pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Matthias Apitz <guru@unixarea.de>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: How to investigate deadlocks
Date: Mon, 02 Oct 2023 10:16:18 -0400
Message-ID: <2018034.1696256178@sss.pgh.pa.us> (raw)
In-Reply-To: <ZRqpLukFBYbDBnrG@pureos>
References: <ZRqpLukFBYbDBnrG@pureos>

Matthias Apitz <guru@unixarea.de> writes:
> 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?

Well, not with this amount of info.  But it seems like a fairly
safe bet that the locks being contended for arise from trying to
SELECT-FOR-UPDATE the same two rows in different orders.
You'd need to look at the cursors being fetched from and see
what you can do to make their row locking orders be reliably
consistent.  Or see if you can avoid needing exclusive row
locks in the first place.

			regards, tom lane





view thread (6+ messages)  latest in thread

Message-ID: <2018034.1696256178@sss.pgh.pa.us>
Permalink:  ../2018034.1696256178@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/2018034.1696256178@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: tgl@sss.pgh.pa.us, guru@unixarea.de
  Subject: Re: How to investigate deadlocks
  In-Reply-To: <2018034.1696256178@sss.pgh.pa.us>
  References: <ZRqpLukFBYbDBnrG@pureos> <2018034.1696256178@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox