Subject: Dead lock after the migration from CentOS 7 to RHEL 9

From: Wasim Devale <[email-masked]>
Date: Wed, 14 Aug 2024 11:09:22 +0530

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

---

From: Wasim Devale <[email-masked]>
Date: Thu, 15 Aug 2024 08:51:10 +0530

Hi please help me with the below email.

---------- Forwarded message ---------
From: Wasim Devale <[email-masked]>
Date: Wed, 14 Aug, 2024, 11:09 am
Subject: Dead lock after the migration from CentOS 7 to RHEL 9
To: Pgsql-admin <[email-masked]>, pgsql-admin <
[email-masked]>

Hi All

ERROR: deadlock detected

Thanks,
Wasim

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 14 Aug 2024 20:26:48 -0700

On Wednesday, August 14, 2024, Wasim Devale <[email-masked]> wrote:

> Process 1889236 waits for RowExclusiveLock on relation 10168609 of
> database 10163711; blocked by process 2197043.
>

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.

---

From: Wasim Devale <[email-masked]>
Date: Thu, 15 Aug 2024 08:58:11 +0530

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-masked]>
wrote:

>> DETAIL: Process 2197043 waits for AccessExclusiveLock on relation
>> 10384355 of database 10163711; blocked by process 1889236.
>>

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 14 Aug 2024 20:31:16 -0700

> 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.

---

From: Wasim Devale <[email-masked]>
Date: Thu, 15 Aug 2024 09:08:31 +0530

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

---

From: Scott Ribe <[email-masked]>
Date: Wed, 14 Aug 2024 21:45:29 -0600

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-masked]
https://www.linkedin.com/in/scottribe/

> On Aug 14, 2024, at 9:38 PM, Wasim Devale <[email-masked]> wrote:
> 
> 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. 
> On Thu, 15 Aug, 2024, 9:01 am David G. Johnston, <[email-masked]> wrote:
> That’s the assumption.  The vast majority of locks happen because the “application” executed SQL commands.
> 
> David J.

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 14 Aug 2024 20:50:44 -0700

On Wednesday, August 14, 2024, Wasim Devale <[email-masked]> wrote:

> 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

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.

---

From: Wasim Devale <[email-masked]>
Date: Thu, 15 Aug 2024 09:24:01 +0530

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-masked]>
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

---

From: Tom Lane <[email-masked]>
Date: Thu, 15 Aug 2024 00:08:28 -0400

> 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 wh…

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

---

From: Asad Ali <[email-masked]>
Date: Thu, 15 Aug 2024 11:43:22 +0500

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.

---

From: Ron Johnson <[email-masked]>
Date: Thu, 15 Aug 2024 10:10:48 -0400

> 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!

---

From: Wasim Devale <[email-masked]>
Date: Thu, 15 Aug 2024 20:10:01 +0530

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-masked]> wrote:

> 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.
>
> --