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
