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

Hi please help me with the below email.

---------- Forwarded message ---------
From: Wasim Devale <wasimd60@gmail.com>
Date: Wed, 14 Aug, 2024, 11:09 am
Subject: Dead lock after the migration from CentOS 7 to RHEL 9
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>, pgsql-admin <pgsql-admin@postgresql.org>

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.

So it's not a postgresql issue. It's from the application side correct?

So it's not a postgresql issue. It's from the application side correct?

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

Given errors like the pair you posted, my next step would be to examine the queries being executed by those processes
duringthe deadlock. Now, I think by the time the deadlock error is logged, the queries involved have been cancelled, so
it'spossible the processes have gone on to another query and so pg_stat_activity might not have useful information, or
itmight--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,
sothat you could look through logs to figure out what are the two queries which are deadlocking against each other. 

--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/



> On Aug 14, 2024, at 9:38 PM, Wasim Devale <wasimd60@gmail.com> 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.dconfig file that is unchanged after the migration. This might be the possible reason that it is throughing
deadlock 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, <david.g.johnston@gmail.com> wrote:
> On Wednesday, August 14, 2024, Wasim Devale <wasimd60@gmail.com> 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.

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.

Scott Ribe <scott_ribe@elevated-dev.com> writes:
> Given errors like the pair you posted, my next step would be to examine the queries being executed by those processes
duringthe deadlock. Now, I think by the time the deadlock error is logged, the queries involved have been cancelled, so
it'spossible the processes have gone on to another query and so pg_stat_activity might not have useful information, or
itmight--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

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.