Re: How to investigate deadlocks - Mailing list pgsql-general

From David G. Johnston
Subject Re: How to investigate deadlocks
Date
Msg-id CAKFQuwar-axQf=ms04+LqWgB+XJL_d9FELzjtYFcWaVj45jUQQ@mail.gmail.com
Whole thread Raw
In response to How to investigate deadlocks  (Matthias Apitz <guru@unixarea.de>)
List pgsql-general
On Monday, October 2, 2023, Matthias Apitz <guru@unixarea.de> wrote:

Hello,

One of our clients running our LMS on top of PostgreSQL 13.1 created a
ticket with these messages:

2023-09-30 16:50:50.951 CEST [18117] ERROR:  deadlock detected
2023-09-30 16:50:50.951 CEST [18117] DETAIL:  Process 18117 waits for ShareLock on transaction 150396154; blocked by process 18187.
        Process 18187 waits for ShareLock on transaction 150396155; blocked by process 18117.
        Process 18117: fetch hc_d03geb
        Process 18187: fetch hc_d02ben
2023-09-30 16:50:50.951 CEST [18117] HINT:  See server log for query details.
2023-09-30 16:50:50.951 CEST [18117] CONTEXT:  while locking tuple (38,57) in relation "d03geb"
2023-09-30 16:50:50.951 CEST [18117] STATEMENT:  fetch hc_d03geb

The shown PIDs for sure are the ones of the Pos backend proc (on Linux).
Is there any chance to investigate it further?

Presuming you control the software performing the fetches, sure.  But the database has pretty much told you all it can from its perspective.  Start from the two fetches, work out the table/row access ordering, and figure out where you have the same pairs of tables being accessed but in different orders.

David J. 

pgsql-general by date:

Previous
From: MATSUO Takatoshi
Date:
Subject: Re: Operating of synchronous master when no standby is available
Next
From: Michael Paquier
Date:
Subject: Re: How to force "re-TOAST" after changing STORAGE or COMPRESSION?