Postgres Locking - Mailing list pgsql-performance
| From | Dirschel, Steve |
|---|---|
| Subject | Postgres Locking |
| Date | |
| Msg-id | DM6PR03MB433237820C534143711439B2FAA0A@DM6PR03MB4332.namprd03.prod.outlook.com Whole thread Raw |
| In response to | Re: Postgres 15 SELECT query doesn't use index under RLS (Alexander Okulovich <aokulovich@stiltsoft.com>) |
| Responses |
Re: Postgres Locking
RE: Postgres Locking |
| List | pgsql-performance |
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
pgsql-performance by date: