PostgreSQL查找行锁等待

第一个session：
postgres=# begin;
BEGIN
postgres=# update t10 set id=1 where id=111;
UPDATE 1
postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
          28120
(1 row)

postgres=# update t100 set id=222 where id=2;
UPDATE 1
postgres=# 



第二个session：
[pg121@iZm5ehqfjhnsbtxrzrnh2zZ ~]$ psql -d postgres
Password for user pg121: 
psql (12.1)
Type "help" for help.

postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
          28210
(1 row)

postgres=# update t10 set id=1 where id=111;

---->>>>此时hang住，也就是说，此时锁等待形成(pid=28210的事务正在等待pid=28120的事务)，请注意，是锁等待形成，不是锁形成。

第三个session：
[pg121@iZm5ehqfjhnsbtxrzrnh2zZ ~]$ psql -d postgres
Password for user pg121: 
psql (12.1)
Type "help" for help.

postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
          29654
(1 row)

postgres=# update t100 set id=222 where id=2;
---->>>>此时hang住，也就是说，此时锁等待形成(pid=29654的事务正在等待pid=28120的事务)，请注意，是锁等待形成，不是锁形成。

查找阻塞者：
postgres=# SELECT
postgres-#     l2.pid as blocker_session_pid,
postgres-#     l2.mode as blocker_session_mode,
postgres-#     l2.granted blocker_session_granter,
postgres-# l1.pid as blocked_session_pid,
postgres-#     l1.mode as blocked_session_mode,
postgres-#     l1.granted blocked_session_granter
postgres-# FROM
postgres-#     pg_locks l1
postgres-# JOIN
postgres-#     pg_locks l2 on (
postgres(#         (
postgres(#             l1.locktype,
postgres(#             l1.database,
postgres(#             l1.relation,
postgres(#             l1.page,
postgres(#             l1.tuple,
postgres(#             l1.virtualxid,
postgres(#             l1.transactionid,
postgres(#             l1.classid,
postgres(#             l1.objid,
postgres(#             l1.objsubid
postgres(#         )
postgres(#     IS NOT DISTINCT FROM
postgres(#         (
postgres(#             l2.locktype,
postgres(#             l2.database,
postgres(#             l2.relation,
postgres(#             l2.page,
postgres(#             l2.tuple,
postgres(#             l2.virtualxid,
postgres(#             l2.transactionid,
postgres(#             l2.classid,
postgres(#             l2.objid,
postgres(#             l2.objsubid
postgres(#         )
postgres(#     )
postgres-# WHERE
postgres-#     NOT l1.granted
postgres-# AND
postgres-#     l2.granted;
 blocker_session_pid | blocker_session_mode | blocker_session_granter | blocked_session_pid | blocked_session_mode | blocked_session_granter 
---------------------+----------------------+-------------------------+---------------------+----------------------+-------------------------
               28120 | ExclusiveLock        | t                       |               29654 | ShareLock            | f
               28120 | ExclusiveLock        | t                       |               28210 | ShareLock            | f
(2 rows)

postgres=# 
请注意：如上查询行锁的脚本参考自https://wiki.postgresql.org/wiki/Find_Locks

或者使用如下脚本，以获得更多的信息：
postgres=# SELECT
postgres-# lock2.pid as blocker_pid,
postgres-# stat2.usename as blocker_user,
postgres-# stat2.query as blocker_statement,
postgres-# stat2.state as blocker_state,
postgres-# lock1.pid as blocked_pid,
postgres-# stat1.usename as blocked_user,
postgres-# stat1.query as blocked_statement,
postgres-# stat1.state as blocked_state,
postgres-# clock_timestamp() - stat1.query_start as blocked_duration
postgres-# FROM pg_catalog.pg_locks lock1
postgres-# JOIN pg_catalog.pg_stat_activity stat1 on lock1.pid = stat1.pid
postgres-# JOIN pg_catalog.pg_locks lock2 on
postgres-# (lock1.locktype,lock1.database,lock1.relation,
postgres(# lock1.page,lock1.tuple,lock1.virtualxid,
postgres(# lock1.transactionid,lock1.classid,lock1.objid,
postgres(# lock1.objsubid) IS NOT DISTINCT FROM
postgres-# (lock2.locktype,lock2.DATABASE,
postgres(# lock2.relation,lock2.page,
postgres(# lock2.tuple,lock2.virtualxid,
postgres(# lock2.transactionid,lock2.classid,
postgres(# lock2.objid,lock2.objsubid)
postgres-# JOIN pg_catalog.pg_stat_activity stat2 on lock2.pid
postgres-# = stat2.pid
postgres-# WHERE NOT lock1.granted AND lock2.granted;
 blocker_pid | blocker_user |         blocker_statement          |    blocker_state    | blocked_pid | blocked_user |         blocked_statement          | blocked_state | blocked_duration 
-------------+--------------+------------------------------------+---------------------+-------------+--------------+------------------------------------+---------------+------------------
       28120 | pg121        | update t100 set id=222 where id=2; | idle in transaction |       29654 | pg121        | update t100 set id=222 where id=2; | active        | 00:31:48.504193
       28120 | pg121        | update t100 set id=222 where id=2; | idle in transaction |       28210 | pg121        | update t10 set id=1 where id=111;  | active        | 01:28:12.479294
(2 rows)

postgres=#