public inbox for [email protected]help / color / mirror / Atom feed
Dead lock after the migration from CentOS 7 to RHEL 9 13+ messages / 6 participants [nested] [flat]
* Dead lock after the migration from CentOS 7 to RHEL 9 @ 2024-08-14 05:39 Wasim Devale <[email protected]> 0 siblings, 2 replies; 13+ messages in thread From: Wasim Devale @ 2024-08-14 05:39 UTC (permalink / raw) To: Pgsql-admin <[email protected]>; pgsql-admin Hi All I am facing this issue while reindexing after migration. I analysed the application holding the particular table. I stopped the application and reindexed it. It went well then again I am facing this issue below. ERROR: deadlock detected DETAIL: Process 2197043 waits for AccessExclusiveLock on relation 10384355 of database 10163711; blocked by process 1889236. Process 1889236 waits for RowExclusiveLock on relation 10168609 of database 10163711; blocked by process 2197043. HINT: See server log for query details. Thanks, Wasim ^ permalink raw reply [nested|flat] 13+ messages in thread
* Fwd: Dead lock after the migration from CentOS 7 to RHEL 9 @ 2024-08-15 03:21 Wasim Devale <[email protected]> parent: Wasim Devale <[email protected]> 1 sibling, 1 reply; 13+ messages in thread From: Wasim Devale @ 2024-08-15 03:21 UTC (permalink / raw) To: Muhammad Waqas <[email protected]>; Pgsql-admin <[email protected]>; pgsql-admin Hi please help me with the below email. ---------- Forwarded message --------- From: Wasim Devale <[email protected]> Date: Wed, 14 Aug, 2024, 11:09 am Subject: Dead lock after the migration from CentOS 7 to RHEL 9 To: Pgsql-admin <[email protected]>, pgsql-admin < [email protected]> Hi All I am facing this issue while reindexing after migration. I analysed the application holding the particular table. I stopped the application and reindexed it. It went well then again I am facing this issue below. ERROR: deadlock detected DETAIL: Process 2197043 waits for AccessExclusiveLock on relation 10384355 of database 10163711; blocked by process 1889236. Process 1889236 waits for RowExclusiveLock on relation 10168609 of database 10163711; blocked by process 2197043. HINT: See server log for query details. Thanks, Wasim ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Dead lock after the migration from CentOS 7 to RHEL 9 @ 2024-08-15 03:26 David G. Johnston <[email protected]> parent: Wasim Devale <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: David G. Johnston @ 2024-08-15 03:26 UTC (permalink / raw) To: Wasim Devale <[email protected]>; +Cc: Muhammad Waqas <[email protected]>; Pgsql-admin <[email protected]>; pgsql-admin On Wednesday, August 14, 2024, Wasim Devale <[email protected]> wrote: > Hi please help me with the below email. > > ---------- Forwarded message --------- > From: Wasim Devale <[email protected]> > Date: Wed, 14 Aug, 2024, 11:09 am > Subject: Dead lock after the migration from CentOS 7 to RHEL 9 > To: Pgsql-admin <[email protected]>, pgsql-admin < > [email protected]> > > > Hi All > > I am facing this issue while reindexing after migration. I analysed the > application holding the particular table. I stopped the application and > reindexed it. It went well then again I am facing this issue below. > > ERROR: deadlock detected > > DETAIL: Process 2197043 waits for AccessExclusiveLock on relation 10384355 > of database 10163711; blocked by process 1889236. > > Process 1889236 waits for RowExclusiveLock on relation 10168609 of > database 10163711; blocked by process 2197043. > > HINT: See server log for query details. > How are we supposed to help; you have the indicated log files, and application code, not us. It’s a bug in your code. And you haven’t asked any question that could be answered. David J. ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Dead lock after the migration from CentOS 7 to RHEL 9 @ 2024-08-15 03:28 Wasim Devale <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Wasim Devale @ 2024-08-15 03:28 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Muhammad Waqas <[email protected]>; Pgsql-admin <[email protected]>; pgsql-admin So it's not a postgresql issue. It's from the application side correct? On Thu, 15 Aug, 2024, 8:56 am David G. Johnston, <[email protected]> wrote: > On Wednesday, August 14, 2024, Wasim Devale <[email protected]> wrote: > >> Hi please help me with the below email. >> >> ---------- Forwarded message --------- >> From: Wasim Devale <[email protected]> >> Date: Wed, 14 Aug, 2024, 11:09 am >> Subject: Dead lock after the migration from CentOS 7 to RHEL 9 >> To: Pgsql-admin <[email protected]>, pgsql-admin < >> [email protected]> >> >> >> Hi All >> >> I am facing this issue while reindexing after migration. I analysed the >> application holding the particular table. I stopped the application and >> reindexed it. It went well then again I am facing this issue below. >> >> ERROR: deadlock detected >> >> DETAIL: Process 2197043 waits for AccessExclusiveLock on relation >> 10384355 of database 10163711; blocked by process 1889236. >> >> Process 1889236 waits for RowExclusiveLock on relation 10168609 of >> database 10163711; blocked by process 2197043. >> >> HINT: See server log for query details. >> > > How are we supposed to help; you have the indicated log files, and > application code, not us. It’s a bug in your code. And you haven’t asked > any question that could be answered. > > David J. > > ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Dead lock after the migration from CentOS 7 to RHEL 9 @ 2024-08-15 03:31 David G. Johnston <[email protected]> parent: Wasim Devale <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: David G. Johnston @ 2024-08-15 03:31 UTC (permalink / raw) To: Wasim Devale <[email protected]>; +Cc: Muhammad Waqas <[email protected]>; Pgsql-admin <[email protected]>; pgsql-admin On Wednesday, August 14, 2024, Wasim Devale <[email protected]> wrote: > So it's not a postgresql issue. It's from the application side correct? > That’s the assumption. The vast majority of locks happen because the “application” executed SQL commands. David J. ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Dead lock after the migration from CentOS 7 to RHEL 9 @ 2024-08-15 03:38 Wasim Devale <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 2 replies; 13+ messages in thread From: Wasim Devale @ 2024-08-15 03:38 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Muhammad Waqas <[email protected]>; Pgsql-admin <[email protected]>; pgsql-admin Ok thanks for the insights. Do we need to do any config changes in the config file? We have also configured Datadog agent at operating system level that checks postgresql activity it's has the pgpouncer.d config file that is unchanged after the migration. This might be the possible reason that it is throughing dead lock error. Please through some insights on the above. Thanks for the immediate help. Regards, Wasim On Thu, 15 Aug, 2024, 9:01 am David G. Johnston, <[email protected]> wrote: > On Wednesday, August 14, 2024, Wasim Devale <[email protected]> wrote: > >> So it's not a postgresql issue. It's from the application side correct? >> > > That’s the assumption. The vast majority of locks happen because the > “application” executed SQL commands. > > David J. > ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Dead lock after the migration from CentOS 7 to RHEL 9 @ 2024-08-15 03:45 Scott Ribe <[email protected]> parent: Wasim Devale <[email protected]> 1 sibling, 2 replies; 13+ messages in thread From: Scott Ribe @ 2024-08-15 03:45 UTC (permalink / raw) To: Wasim Devale <[email protected]>; +Cc: pgsql-admin Given errors like the pair you posted, my next step would be to examine the queries being executed by those processes during the deadlock. Now, I think by the time the deadlock error is logged, the queries involved have been cancelled, so it's possible the processes have gone on to another query and so pg_stat_activity might not have useful information, or it might--depending on what your app would do after a deadlock error. If it does not, then the next thing would be to set log_min_duration_statement to less than deadlock/statement timeout, so that you could look through logs to figure out what are the two queries which are deadlocking against each other. -- Scott Ribe [email protected] https://www.linkedin.com/in/scottribe/ > On Aug 14, 2024, at 9:38 PM, Wasim Devale <[email protected]> wrote: > > Ok thanks for the insights. > Do we need to do any config changes in the config file? > We have also configured Datadog agent at operating system level that checks postgresql activity it's has the pgpouncer.d config file that is unchanged after the migration. This might be the possible reason that it is throughing dead lock error. > Please through some insights on the above. > Thanks for the immediate help. > Regards, > Wasim > > On Thu, 15 Aug, 2024, 9:01 am David G. Johnston, <[email protected]> wrote: > On Wednesday, August 14, 2024, Wasim Devale <[email protected]> wrote: > So it's not a postgresql issue. It's from the application side correct? > > That’s the assumption. The vast majority of locks happen because the “application” executed SQL commands. > > David J. ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Dead lock after the migration from CentOS 7 to RHEL 9 @ 2024-08-15 03:50 David G. Johnston <[email protected]> parent: Wasim Devale <[email protected]> 1 sibling, 1 reply; 13+ messages in thread From: David G. Johnston @ 2024-08-15 03:50 UTC (permalink / raw) To: Wasim Devale <[email protected]>; +Cc: Muhammad Waqas <[email protected]>; Pgsql-admin <[email protected]>; pgsql-admin On Wednesday, August 14, 2024, Wasim Devale <[email protected]> wrote: > Ok thanks for the insights. > > Do we need to do any config changes in the config file? > > We have also configured Datadog agent at operating system level that > checks postgresql activity it's has the pgpouncer.d config file that is > unchanged after the migration. This might be the possible reason that it is > throughing dead lock error. > > Please through some insights on the above. > Sounds like you need to read up on what a deadlock is, and then go figure out what combination of SQL commands in your environment is causing it. Only then does it make sense to reason through possible solutions. Until you can show some SQL commands I’m not of much help. I’m not inclined to teach what a deadlock is - such knowledge is available on the internet. David J. ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Dead lock after the migration from CentOS 7 to RHEL 9 @ 2024-08-15 03:54 Wasim Devale <[email protected]> parent: Scott Ribe <[email protected]> 1 sibling, 0 replies; 13+ messages in thread From: Wasim Devale @ 2024-08-15 03:54 UTC (permalink / raw) To: Scott Ribe <[email protected]>; +Cc: pgsql-admin Thanks Scott I do reindexing on database when production is completely down. it's only the application UI queries that holds the tables. I will dig down more if require your help I will post it. Thanks David, Thanks Scott. On Thu, 15 Aug, 2024, 9:15 am Scott Ribe, <[email protected]> wrote: > Given errors like the pair you posted, my next step would be to examine > the queries being executed by those processes during the deadlock. Now, I > think by the time the deadlock error is logged, the queries involved have > been cancelled, so it's possible the processes have gone on to another > query and so pg_stat_activity might not have useful information, or it > might--depending on what your app would do after a deadlock error. > > If it does not, then the next thing would be to set > log_min_duration_statement to less than deadlock/statement timeout, so that > you could look through logs to figure out what are the two queries which > are deadlocking against each other. > > -- > Scott Ribe > [email protected] > https://www.linkedin.com/in/scottribe/ > > > > > On Aug 14, 2024, at 9:38 PM, Wasim Devale <[email protected]> wrote: > > > > Ok thanks for the insights. > > Do we need to do any config changes in the config file? > > We have also configured Datadog agent at operating system level that > checks postgresql activity it's has the pgpouncer.d config file that is > unchanged after the migration. This might be the possible reason that it is > throughing dead lock error. > > Please through some insights on the above. > > Thanks for the immediate help. > > Regards, > > Wasim > > > > On Thu, 15 Aug, 2024, 9:01 am David G. Johnston, < > [email protected]> wrote: > > On Wednesday, August 14, 2024, Wasim Devale <[email protected]> wrote: > > So it's not a postgresql issue. It's from the application side correct? > > > > That’s the assumption. The vast majority of locks happen because the > “application” executed SQL commands. > > > > David J. > > ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Dead lock after the migration from CentOS 7 to RHEL 9 @ 2024-08-15 04:08 Tom Lane <[email protected]> parent: Scott Ribe <[email protected]> 1 sibling, 0 replies; 13+ messages in thread From: Tom Lane @ 2024-08-15 04:08 UTC (permalink / raw) To: Scott Ribe <[email protected]>; +Cc: Wasim Devale <[email protected]>; pgsql-admin Scott Ribe <[email protected]> writes: > Given errors like the pair you posted, my next step would be to examine the queries being executed by those processes during the deadlock. Now, I think by the time the deadlock error is logged, the queries involved have been cancelled, so it's possible the processes have gone on to another query and so pg_stat_activity might not have useful information, or it might--depending on what your app would do after a deadlock error. > If it does not, then the next thing would be to set log_min_duration_statement to less than deadlock/statement timeout, so that you could look through logs to figure out what are the two queries which are deadlocking against each other. The postmaster log should already contain the statements that deadlocked against each other --- that's what the "HINT: See server log for query details" is telling you. regards, tom lane ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Dead lock after the migration from CentOS 7 to RHEL 9 @ 2024-08-15 06:43 Asad Ali <[email protected]> parent: Wasim Devale <[email protected]> 1 sibling, 0 replies; 13+ messages in thread From: Asad Ali @ 2024-08-15 06:43 UTC (permalink / raw) To: Wasim Devale <[email protected]>; +Cc: Pgsql-admin <[email protected]>; pgsql-admin The error message indicates a deadlock situation between two processes in your PostgreSQL database. A deadlock occurs when two or more processes are blocked indefinitely, each waiting for the other to release a lock. Here's a breakdown of the error message: - Process 2197043 is waiting for an AccessExclusiveLock on relation 10384355 (a table or index). - Process 1889236 is holding the lock that process 2197043 is waiting for and is waiting for a RowExclusiveLock on relation 10168609 (another table or index). - Process 2197043 is holding the lock that process 1889236 is waiting for, creating a deadlock. *To resolve this issue:* 1. Check the server log for query details, as hinted in the error message. This will help you identify the queries causing the deadlock. 2. Analyze the queries and identify any potential causes, such as: - Long-running transactions - Lock contention between multiple processes - Poorly indexed tables *3. Consider the following solutions:* - Optimize queries to reduce lock contention - Use more granular locking (e.g., RowShareLock instead of RowExclusiveLock) - Split long-running transactions into smaller ones - Reindex tables during a maintenance window to minimize lock contention 4. If the issue persists, consider increasing the deadlock_timeout parameter to allow PostgreSQL to detect and resolve deadlocks more efficiently. Remember to exercise caution when making changes to your database configuration or queries, as they may impact performance or data integrity. On Wed, Aug 14, 2024 at 10:39 AM Wasim Devale <[email protected]> wrote: > Hi All > > I am facing this issue while reindexing after migration. I analysed the > application holding the particular table. I stopped the application and > reindexed it. It went well then again I am facing this issue below. > > ERROR: deadlock detected > > DETAIL: Process 2197043 waits for AccessExclusiveLock on relation 10384355 > of database 10163711; blocked by process 1889236. > > Process 1889236 waits for RowExclusiveLock on relation 10168609 of > database 10163711; blocked by process 2197043. > > HINT: See server log for query details. > > > Thanks, > Wasim > ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Dead lock after the migration from CentOS 7 to RHEL 9 @ 2024-08-15 14:10 Ron Johnson <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 1 reply; 13+ messages in thread From: Ron Johnson @ 2024-08-15 14:10 UTC (permalink / raw) To: Pgsql-admin <[email protected]> On Wed, Aug 14, 2024 at 11:50 PM David G. Johnston < [email protected]> wrote: > On Wednesday, August 14, 2024, Wasim Devale <[email protected]> wrote: > >> Ok thanks for the insights. >> >> Do we need to do any config changes in the config file? >> >> We have also configured Datadog agent at operating system level that >> checks postgresql activity it's has the pgpouncer.d config file that is >> unchanged after the migration. This might be the possible reason that it is >> throughing dead lock error. >> >> Please through some insights on the above. >> > > Sounds like you need to read up on what a deadlock is, and then go figure > out what combination of SQL commands in your environment is causing it. > Only then does it make sense to reason through possible solutions. Until > you can show some SQL commands I’m not of much help. I’m not inclined to > teach what a deadlock is - such knowledge is available on the internet. > TBF, this is apparently the same application that was running "fine" before upgrading. OP has still done a non-existant job at telling us what version he upgraded from and to, how he upgraded and what he did afterwards. -- Death to America, and butter sauce. Iraq lobster! ^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Dead lock after the migration from CentOS 7 to RHEL 9 @ 2024-08-15 14:40 Wasim Devale <[email protected]> parent: Ron Johnson <[email protected]> 0 siblings, 0 replies; 13+ messages in thread From: Wasim Devale @ 2024-08-15 14:40 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]> I am not much aware that the application code is embedded in Java and the groovy language. The issue is resolved I did indexing concurrently on each primary key index individually via a Linux command written in a file and executed it and other indexes that has the btree indexing. This resolved it. Thanks everyone. On Thu, 15 Aug, 2024, 7:41 pm Ron Johnson, <[email protected]> wrote: > On Wed, Aug 14, 2024 at 11:50 PM David G. Johnston < > [email protected]> wrote: > >> On Wednesday, August 14, 2024, Wasim Devale <[email protected]> wrote: >> >>> Ok thanks for the insights. >>> >>> Do we need to do any config changes in the config file? >>> >>> We have also configured Datadog agent at operating system level that >>> checks postgresql activity it's has the pgpouncer.d config file that is >>> unchanged after the migration. This might be the possible reason that it is >>> throughing dead lock error. >>> >>> Please through some insights on the above. >>> >> >> Sounds like you need to read up on what a deadlock is, and then go figure >> out what combination of SQL commands in your environment is causing it. >> Only then does it make sense to reason through possible solutions. Until >> you can show some SQL commands I’m not of much help. I’m not inclined to >> teach what a deadlock is - such knowledge is available on the internet. >> > > TBF, this is apparently the same application that was running "fine" > before upgrading. > > OP has still done a non-existant job at telling us what version he > upgraded from and to, how he upgraded and what he did afterwards. > > -- > Death to America, and butter sauce. > Iraq lobster! > ^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2024-08-15 14:40 UTC | newest] Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-08-14 05:39 Dead lock after the migration from CentOS 7 to RHEL 9 Wasim Devale <[email protected]> 2024-08-15 03:21 ` Wasim Devale <[email protected]> 2024-08-15 03:26 ` David G. Johnston <[email protected]> 2024-08-15 03:28 ` Wasim Devale <[email protected]> 2024-08-15 03:31 ` David G. Johnston <[email protected]> 2024-08-15 03:38 ` Wasim Devale <[email protected]> 2024-08-15 03:45 ` Scott Ribe <[email protected]> 2024-08-15 03:54 ` Wasim Devale <[email protected]> 2024-08-15 04:08 ` Tom Lane <[email protected]> 2024-08-15 03:50 ` David G. Johnston <[email protected]> 2024-08-15 14:10 ` Ron Johnson <[email protected]> 2024-08-15 14:40 ` Wasim Devale <[email protected]> 2024-08-15 06:43 ` Asad Ali <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox