|
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 |
