pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres Locking
3+ messages / 3 participants
[nested] [flat]
* Postgres Locking
@ 2023-10-31 21:12 Dirschel, Steve <steve.dirschel@thomsonreuters.com>
0 siblings, 2 replies; 3+ messages in thread
From: Dirschel, Steve @ 2023-10-31 21:12 UTC (permalink / raw)
To: pgsql-performance; +Cc: Wong, Kam Fook (TR Technology) <kamfook.wong@thomsonreuters.com>
Relatively new to Postgres. Running into a locking situation and I need to make sure I understand output. I found this query to show a lock tree:
wldomart01a=> WITH
wldomart01a-> RECURSIVE l AS (
wldomart01a(> SELECT pid, locktype, mode, granted,
wldomart01a(> ROW(locktype,database,relation,page,tuple,virtualxid,transactionid,classid,objid,objsubid) obj
wldomart01a(> FROM pg_locks),
wldomart01a-> pairs AS (
wldomart01a(> SELECT w.pid waiter, l.pid locker, l.obj, l.mode
wldomart01a(> FROM l w
wldomart01a(> JOIN l
wldomart01a(> ON l.obj IS NOT DISTINCT FROM w.obj
wldomart01a(> AND l.locktype=w.locktype
wldomart01a(> AND NOT l.pid=w.pid
wldomart01a(> AND l.granted
wldomart01a(> WHERE NOT w.granted),
wldomart01a-> tree AS (
wldomart01a(> SELECT l.locker pid, l.locker root, NULL::record obj, NULL AS mode, 0 lvl, locker::text path, array_agg(l.locker) OVER () all_pids
wldomart01a(> FROM ( SELECT DISTINCT locker FROM pairs l WHERE NOT EXISTS (SELECT 1 FROM pairs WHERE waiter=l.locker) ) l
wldomart01a(> UNION ALL
wldomart01a(> SELECT w.waiter pid, tree.root, w.obj, w.mode, tree.lvl+1, tree.path||'.'||w.waiter, all_pids || array_agg(w.waiter) OVER ()
wldomart01a(> FROM tree
wldomart01a(> JOIN pairs w
wldomart01a(> ON tree.pid=w.locker
wldomart01a(> AND NOT w.waiter = ANY ( all_pids ))
wldomart01a-> SELECT
wldomart01a-> path, repeat(' .', lvl)||' '|| tree.pid as pid_tree, tree.pid,
wldomart01a-> (clock_timestamp() - a.xact_start)::interval(3) AS ts_age,
wldomart01a-> replace(a.state, 'idle in transaction', 'idletx') state,
wldomart01a-> wait_event_type wait_type,
wldomart01a-> wait_event,
wldomart01a-> (clock_timestamp() - state_change)::interval(3) AS change_age,
wldomart01a-> lvl,
wldomart01a-> (SELECT count(*) FROM tree p WHERE p.path ~ ('^'||tree.path) AND NOT p.path=tree.path) blocked,
wldomart01a-> repeat(' .', lvl)||' '||left(query,100) query
wldomart01a-> FROM tree
wldomart01a-> JOIN pg_stat_activity a
wldomart01a-> USING (pid)
wldomart01a-> ORDER BY path;
path | pid_tree | pid | ts_age | state | wait_type | wait_event | change_age | lvl | blocked | query
-----------+----------+------+--------------+--------+-----------+---------------+--------------+-----+---------+------------------------------------
3740 | 3740 | 3740 | 01:23:03.294 | idletx | Client | ClientRead | 00:00:00.004 | 0 | 1 | update "wln_mart"."ee_fact" set +
| | | | | | | | | | "changed_on" = $1 +
| | | | | | | | | | where "ee_fact_id" = $2
3740.3707 | . 3707 | 3707 | 01:23:03.294 | active | Lock | transactionid | 01:23:03.29 | 1 | 0 | . update "wln_mart"."ee_fact" set+
| | | | | | | | | | "changed_on" = $1 +
| | | | | | | | | | where "ee_fact_id" = $2
(2 rows)
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. But I am being told those 2 sessions should not be trying to process the same PK rows.
Here is output from pg_locks for those 2 sessions:
wldomart01a=> select * from pg_locks where pid in (3740,3707) order by pid;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+------------------+---------+----------+-------------------------------
transactionid | | | | | | 251189989 | | | | 54/196626 | 3707 | ExclusiveLock | t | f |
relation | 91999 | 94619 | | | | | | | | 54/196626 | 3707 | RowExclusiveLock | t | t |
relation | 91999 | 94615 | | | | | | | | 54/196626 | 3707 | RowExclusiveLock | t | t |
relation | 91999 | 94611 | | | | | | | | 54/196626 | 3707 | RowExclusiveLock | t | t |
relation | 91999 | 94610 | | | | | | | | 54/196626 | 3707 | RowExclusiveLock | t | t |
relation | 91999 | 94609 | | | | | | | | 54/196626 | 3707 | RowExclusiveLock | t | t |
relation | 91999 | 94569 | | | | | | | | 54/196626 | 3707 | RowExclusiveLock | t | t |
relation | 91999 | 93050 | | | | | | | | 54/196626 | 3707 | RowExclusiveLock | t | t |
virtualxid | | | | | 54/196626 | | | | | 54/196626 | 3707 | ExclusiveLock | t | t |
transactionid | | | | | | 251189988 | | | | 54/196626 | 3707 | ExclusiveLock | t | f |
transactionid | | | | | | 251189986 | | | | 54/196626 | 3707 | ShareLock | f | f | 2023-10-31 14:40:21.837507-05
tuple | 91999 | 93050 | 0 | 1 | | | | | | 54/196626 | 3707 | ExclusiveLock | t | f |
relation | 91999 | 308853 | | | | | | | | 54/196626 | 3707 | RowExclusiveLock | t | t |
relation | 91999 | 94693 | | | | | | | | 54/196626 | 3707 | RowExclusiveLock | t | t |
relation | 91999 | 94693 | | | | | | | | 60/259887 | 3740 | RowExclusiveLock | t | t |
relation | 91999 | 94619 | | | | | | | | 60/259887 | 3740 | RowExclusiveLock | t | t |
relation | 91999 | 94615 | | | | | | | | 60/259887 | 3740 | RowExclusiveLock | t | t |
relation | 91999 | 94611 | | | | | | | | 60/259887 | 3740 | RowExclusiveLock | t | t |
relation | 91999 | 94610 | | | | | | | | 60/259887 | 3740 | RowExclusiveLock | t | t |
relation | 91999 | 94609 | | | | | | | | 60/259887 | 3740 | RowExclusiveLock | t | t |
relation | 91999 | 94569 | | | | | | | | 60/259887 | 3740 | RowExclusiveLock | t | t |
relation | 91999 | 93050 | | | | | | | | 60/259887 | 3740 | RowExclusiveLock | t | t |
virtualxid | | | | | 60/259887 | | | | | 60/259887 | 3740 | ExclusiveLock | t | t |
transactionid | | | | | | 251189986 | | | | 60/259887 | 3740 | ExclusiveLock | t | f |
relation | 91999 | 308853 | | | | | | | | 60/259887 | 3740 | RowExclusiveLock | t | t |
(25 rows)
I believe the locktype relation is pointing to the table and the indexes on the table. Which data point(s) above point to this being row-level locking and not some other level of locking? I am very familiar with Oracle locking and different levels and am trying to quickly get up-to-speed on Postgres locking. I am continuing to google for this but figured I could post this to see if someone can provide a quick response.
Thanks
Steve
This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: Postgres Locking
@ 2023-10-31 21:43 Smith, Travis <Travis.Smith@circana.com>
parent: Dirschel, Steve <steve.dirschel@thomsonreuters.com>
1 sibling, 0 replies; 3+ messages in thread
From: Smith, Travis @ 2023-10-31 21:43 UTC (permalink / raw)
To: Dirschel, Steve <steve.dirschel@thomsonreuters.com>; pgsql-performance; +Cc: Wong, Kam Fook (TR Technology) <kamfook.wong@thomsonreuters.com>
Hi Steve,
Its literally the same query. I would try to extract the actual named object that is in the lock to verify. Is there any partitioning? An explain plan may be helpful.
Thank you
Travis
From: Dirschel, Steve <steve.dirschel@thomsonreuters.com>
Sent: Tuesday, October 31, 2023 4:12 PM
To: pgsql-performance@postgresql.org
Cc: Wong, Kam Fook (TR Technology) <kamfook.wong@thomsonreuters.com>
Subject: Postgres Locking
***ATTENTION!! This message originated from outside of Circana. Treat hyperlinks and attachments in this email with caution.***
Relatively new to Postgres. Running into a locking situation and I need to make sure I understand output. I found this query to show a lock tree:
wldomart01a=> WITH
wldomart01a-> RECURSIVE l AS (
wldomart01a(> SELECT pid, locktype, mode, granted,
wldomart01a(> ROW(locktype,database,relation,page,tuple,virtualxid,transactionid,classid,objid,objsubid) obj
wldomart01a(> FROM pg_locks),
wldomart01a-> pairs AS (
wldomart01a(> SELECT w.pid waiter, l.pid locker, l.obj, l.mode
wldomart01a(> FROM l w
wldomart01a(> JOIN l
wldomart01a(> ON l.obj IS NOT DISTINCT FROM w.obj
wldomart01a(> AND l.locktype=w.locktype
wldomart01a(> AND NOT l.pid=w.pid
wldomart01a(> AND l.granted
wldomart01a(> WHERE NOT w.granted),
wldomart01a-> tree AS (
wldomart01a(> SELECT l.locker pid, l.locker root, NULL::record obj, NULL AS mode, 0 lvl, locker::text path, array_agg(l.locker) OVER () all_pids
wldomart01a(> FROM ( SELECT DISTINCT locker FROM pairs l WHERE NOT EXISTS (SELECT 1 FROM pairs WHERE waiter=l.locker) ) l
wldomart01a(> UNION ALL
wldomart01a(> SELECT w.waiter pid, tree.root, w.obj, w.mode, tree.lvl+1, tree.path||'.'||w.waiter, all_pids || array_agg(w.waiter) OVER ()
wldomart01a(> FROM tree
wldomart01a(> JOIN pairs w
wldomart01a(> ON tree.pid=w.locker
wldomart01a(> AND NOT w.waiter = ANY ( all_pids ))
wldomart01a-> SELECT
wldomart01a-> path, repeat(' .', lvl)||' '|| tree.pid as pid_tree, tree.pid,
wldomart01a-> (clock_timestamp() - a.xact_start)::interval(3) AS ts_age,
wldomart01a-> replace(a.state, 'idle in transaction', 'idletx') state,
wldomart01a-> wait_event_type wait_type,
wldomart01a-> wait_event,
wldomart01a-> (clock_timestamp() - state_change)::interval(3) AS change_age,
wldomart01a-> lvl,
wldomart01a-> (SELECT count(*) FROM tree p WHERE p.path ~ ('^'||tree.path) AND NOT p.path=tree.path) blocked,
wldomart01a-> repeat(' .', lvl)||' '||left(query,100) query
wldomart01a-> FROM tree
wldomart01a-> JOIN pg_stat_activity a
wldomart01a-> USING (pid)
wldomart01a-> ORDER BY path;
path | pid_tree | pid | ts_age | state | wait_type | wait_event | change_age | lvl | blocked | query
-----------+----------+------+--------------+--------+-----------+---------------+--------------+-----+---------+------------------------------------
3740 | 3740 | 3740 | 01:23:03.294 | idletx | Client | ClientRead | 00:00:00.004 | 0 | 1 | update "wln_mart"."ee_fact" set +
| | | | | | | | | | "changed_on" = $1 +
| | | | | | | | | | where "ee_fact_id" = $2
3740.3707 | . 3707 | 3707 | 01:23:03.294 | active | Lock | transactionid | 01:23:03.29 | 1 | 0 | . update "wln_mart"."ee_fact" set+
| | | | | | | | | | "changed_on" = $1 +
| | | | | | | | | | where "ee_fact_id" = $2
(2 rows)
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. But I am being told those 2 sessions should not be trying to process the same PK rows.
Here is output from pg_locks for those 2 sessions:
wldomart01a=> select * from pg_locks where pid in (3740,3707) order by pid;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+------------------+---------+----------+-------------------------------
transactionid | | | | | | 251189989 | | | | 54/196626 | 3707 | ExclusiveLock | t | f |
relation | 91999 | 94619 | | | | | | | | 54/196626 | 3707 | RowExclusiveLock | t | t |
relation | 91999 | 94615 | | | | | | | | 54/196626 | 3707 | RowExclusiveLock | t | t |
relation | 91999 | 94611 | | | | | | | | 54/196626 | 3707 | RowExclusiveLock | t | t |
relation | 91999 | 94610 | | | | | | | | 54/196626 | 3707 | RowExclusiveLock | t | t |
relation | 91999 | 94609 | | | | | | | | 54/196626 | 3707 | RowExclusiveLock | t | t |
relation | 91999 | 94569 | | | | | | | | 54/196626 | 3707 | RowExclusiveLock | t | t |
relation | 91999 | 93050 | | | | | | | | 54/196626 | 3707 | RowExclusiveLock | t | t |
virtualxid | | | | | 54/196626 | | | | | 54/196626 | 3707 | ExclusiveLock | t | t |
transactionid | | | | | | 251189988 | | | | 54/196626 | 3707 | ExclusiveLock | t | f |
transactionid | | | | | | 251189986 | | | | 54/196626 | 3707 | ShareLock | f | f | 2023-10-31 14:40:21.837507-05
tuple | 91999 | 93050 | 0 | 1 | | | | | | 54/196626 | 3707 | ExclusiveLock | t | f |
relation | 91999 | 308853 | | | | | | | | 54/196626 | 3707 | RowExclusiveLock | t | t |
relation | 91999 | 94693 | | | | | | | | 54/196626 | 3707 | RowExclusiveLock | t | t |
relation | 91999 | 94693 | | | | | | | | 60/259887 | 3740 | RowExclusiveLock | t | t |
relation | 91999 | 94619 | | | | | | | | 60/259887 | 3740 | RowExclusiveLock | t | t |
relation | 91999 | 94615 | | | | | | | | 60/259887 | 3740 | RowExclusiveLock | t | t |
relation | 91999 | 94611 | | | | | | | | 60/259887 | 3740 | RowExclusiveLock | t | t |
relation | 91999 | 94610 | | | | | | | | 60/259887 | 3740 | RowExclusiveLock | t | t |
relation | 91999 | 94609 | | | | | | | | 60/259887 | 3740 | RowExclusiveLock | t | t |
relation | 91999 | 94569 | | | | | | | | 60/259887 | 3740 | RowExclusiveLock | t | t |
relation | 91999 | 93050 | | | | | | | | 60/259887 | 3740 | RowExclusiveLock | t | t |
virtualxid | | | | | 60/259887 | | | | | 60/259887 | 3740 | ExclusiveLock | t | t |
transactionid | | | | | | 251189986 | | | | 60/259887 | 3740 | ExclusiveLock | t | f |
relation | 91999 | 308853 | | | | | | | | 60/259887 | 3740 | RowExclusiveLock | t | t |
(25 rows)
I believe the locktype relation is pointing to the table and the indexes on the table. Which data point(s) above point to this being row-level locking and not some other level of locking? I am very familiar with Oracle locking and different levels and am trying to quickly get up-to-speed on Postgres locking. I am continuing to google for this but figured I could post this to see if someone can provide a quick response.
Thanks
Steve
This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Postgres Locking
@ 2023-10-31 21:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dirschel, Steve <steve.dirschel@thomsonreuters.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2023-10-31 21:45 UTC (permalink / raw)
To: Dirschel, Steve <steve.dirschel@thomsonreuters.com>; +Cc: pgsql-performance; Wong, Kam Fook (TR Technology) <kamfook.wong@thomsonreuters.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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-10-31 21:45 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-31 21:12 Postgres Locking Dirschel, Steve <steve.dirschel@thomsonreuters.com>
2023-10-31 21:43 ` Smith, Travis <Travis.Smith@circana.com>
2023-10-31 21:45 ` Tom Lane <tgl@sss.pgh.pa.us>
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