From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dirschel, Steve <steve.dirschel@thomsonreuters.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: Wong, Kam Fook (TR Technology) <kamfook.wong@thomsonreuters.com>
Subject: Re: Postgres Locking
Date: Tue, 31 Oct 2023 17:45:12 -0400
Message-ID: <2641105.1698788712@sss.pgh.pa.us> (raw)
In-Reply-To: <DM6PR03MB433237820C534143711439B2FAA0A@DM6PR03MB4332.namprd03.prod.outlook.com>
References: <5c1179bb-240b-4c1c-b4b3-2a24868e44bc@stiltsoft.com>
<1570249.1697228785@sss.pgh.pa.us>
<dd874d42-ad02-48a6-82db-5666f1ee0ec1@stiltsoft.com>
<3153246.1697661350@sss.pgh.pa.us>
<e9d503cb-efeb-43d3-952e-f517e4d24302@stiltsoft.com>
<1157086.1698329377@sss.pgh.pa.us>
<6c888a16-b206-4817-b5ca-9e09b904edde@stiltsoft.com>
<DM6PR03MB433237820C534143711439B2FAA0A@DM6PR03MB4332.namprd03.prod.outlook.com>
"Dirschel, Steve" <steve.dirschel@thomsonreuters.com> writes:
> Above I can see PID 3740 is blocking PID 3707. The PK on table
> wln_mart.ee_fact is ee_fact_id. I assume PID 3740 has updated a row
> (but not committed it yet) that PID 3707 is also trying to update.
Hmm. We can see that 3707 is waiting for 3740 to commit, because it's
trying to take ShareLock on 3740's transactionid:
> transactionid | | | | | | 251189986 | | | | 54/196626 | 3707 | ShareLock | f | f | 2023-10-31 14:40:21.837507-05
251189986 is indeed 3740's, because it has ExclusiveLock on that:
> transactionid | | | | | | 251189986 | | | | 60/259887 | 3740 | ExclusiveLock | t | f |
There are many reasons why one xact might be waiting on another to commit,
not only that they tried to update the same tuple. However, in this case
I suspect that that is the problem, because we can also see that 3707 has
an exclusive tuple-level lock:
> tuple | 91999 | 93050 | 0 | 1 | | | | | | 54/196626 | 3707 | ExclusiveLock | t | f |
That kind of lock would only be held while queueing to modify a tuple.
(Basically, it establishes that 3707 is next in line, in case some
other transaction comes along and also wants to modify the same tuple.)
It should be released as soon as the tuple update is made, so 3707 is
definitely stuck waiting to modify a tuple, and AFAICS it must be stuck
because of 3740's uncommitted earlier update.
> But I am being told those 2 sessions should not be trying to process the
> same PK rows.
Perhaps "should not" is wrong. Or it could be some indirect update
(caused by a foreign key with CASCADE, or the like).
You have here the relation OID (try "SELECT 93050::regclass" to
decode it) and the tuple ID, so it should work to do
SELECT * FROM that_table WHERE ctid = '(0,1)';
to see the previous state of the problematic tuple. Might
help to decipher the problem.
regards, tom lane
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, steve.dirschel@thomsonreuters.com, kamfook.wong@thomsonreuters.com
Subject: Re: Postgres Locking
In-Reply-To: <2641105.1698788712@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