直接贴SQL

WITH RECURSIVE l AS (SELECT pid, locktype, mode, granted,
ROW(locktype,database,relation,page,tuple,virtualxid,transactionid,classid,objid,objsubid) objFROM pg_locks
), pairs AS (SELECT w.pid waiter, l.pid locker, l.obj, l.modeFROM l wJOIN l ON l.obj IS NOT DISTINCT FROM w.obj AND l.locktype=w.locktype AND NOT l.pid=w.pid AND l.grantedWHERE NOT w.granted
), tree AS (SELECT l.locker pid, l.locker root, NULL::record obj, NULL AS mode, 0 lvl, locker::text path, array_agg(l.locker) OVER () all_pidsFROM ( SELECT DISTINCT locker FROM pairs l WHERE NOT EXISTS (SELECT 1 FROM pairs WHERE waiter=l.locker) ) lUNION ALLSELECT w.waiter pid, tree.root, w.obj, w.mode, tree.lvl+1, tree.path||'.'||w.waiter, all_pids || array_agg(w.waiter) OVER ()FROM tree JOIN pairs w ON tree.pid=w.locker AND NOT w.waiter = ANY ( all_pids )
)
SELECT (clock_timestamp() - a.xact_start)::interval(3) AS ts_age,replace(a.state, 'idle in transaction', 'idletx') state,(clock_timestamp() - state_change)::interval(3) AS change_age,a.datname,tree.pid,a.usename,a.client_addr,lvl,(SELECT count(*) FROM tree p WHERE p.path ~ ('^'||tree.path) AND NOT p.path=tree.path) blocked,repeat(' .', lvl)||' '||left(regexp_replace(query, 's+', ' ', 'g'),100) query
FROM tree
JOIN pg_stat_activity a USING (pid)
ORDER BY path;

测试下，首先开启一个事务，然后更新一条数据

之后再新开窗口，也更新这个listing_id

此时发现一直在执行中

接着使用上述SQL查询锁等待

可以看到相关数据