PostgreSQL查看当前锁信息

PostgreSQL查看当前锁信息

基础信息
OS版本：Red Hat Enterprise Linux Server release 7.9 (Maipo)
DB版本：16.2
pg软件目录：/home/pg16/soft
pg数据目录：/home/pg16/data
端口：5777

查看当前锁信息的sql

SELECT pg_stat_activity.datname,pg_locks.pid,pg_class.relname,pg_locks.transactionid,pg_locks.granted,pg_locks.mode,pg_stat_activity.query as query_snippet,age(now(), pg_stat_activity.query_start) as age 
FROM pg_stat_activity, pg_locks LEFT OUTER JOIN pg_class 
ON (pg_locks.relation = pg_class.oid) 
WHERE pg_stat_activity.pid = pg_locks.pidAND pg_stat_activity.pid <> pg_backend_pid() 
ORDER BY query_start;

示例1

行级共享锁

white=# BEGIN;
BEGIN
white=*# SELECT * FROM yewu1.t1 WHERE id = 1 FOR SHARE;id 
----1
(1 row)

postgres=# SELECT 
postgres-#     pg_stat_activity.datname,
postgres-#     pg_locks.pid,
postgres-#     pg_class.relname,
postgres-#     pg_locks.transactionid,
postgres-#     pg_locks.granted,
postgres-#     pg_locks.mode,
postgres-#     pg_stat_activity.query as query_snippet,
postgres-#     age(now(), pg_stat_activity.query_start) as age 
postgres-# FROM 
postgres-#     pg_stat_activity, pg_locks LEFT OUTER JOIN pg_class 
postgres-# ON 
postgres-#     (pg_locks.relation = pg_class.oid) 
postgres-# WHERE 
postgres-#     pg_stat_activity.pid = pg_locks.pid
postgres-#     AND pg_stat_activity.pid <> pg_backend_pid() 
postgres-# ORDER BY 
postgres-#     query_start;datname | pid  | relname | transactionid | granted |     mode      |                 query_snippet                  |       age       
---------+------+---------+---------------+---------+---------------+------------------------------------------------+-----------------white   | 6320 |         |               | t       | RowShareLock  | SELECT * FROM yewu1.t1 WHERE id = 1 FOR SHARE; | 00:00:15.839986white   | 6320 |         |               | t       | ExclusiveLock | SELECT * FROM yewu1.t1 WHERE id = 1 FOR SHARE; | 00:00:15.839986white   | 6320 |         |        268808 | t       | ExclusiveLock | SELECT * FROM yewu1.t1 WHERE id = 1 FOR SHARE; | 00:00:15.839986
(3 rows)postgres=# 

示例2

行级排他锁

white=# BEGIN;
BEGIN
white=*# SELECT * FROM yewu1.t1 WHERE id = 1 FOR UPDATE;id 
----1
(1 row)

postgres=# SELECT 
postgres-#     pg_stat_activity.datname,
postgres-#     pg_locks.pid,
postgres-#     pg_class.relname,
postgres-#     pg_locks.transactionid,
postgres-#     pg_locks.granted,
postgres-#     pg_locks.mode,
postgres-#     pg_stat_activity.query as query_snippet,
postgres-#     age(now(), pg_stat_activity.query_start) as age 
postgres-# FROM 
postgres-#     pg_stat_activity, pg_locks LEFT OUTER JOIN pg_class 
postgres-# ON 
postgres-#     (pg_locks.relation = pg_class.oid) 
postgres-# WHERE 
postgres-#     pg_stat_activity.pid = pg_locks.pid
postgres-#     AND pg_stat_activity.pid <> pg_backend_pid() 
postgres-# ORDER BY 
postgres-#     query_start;datname | pid  | relname | transactionid | granted |     mode      |                  query_snippet                  |       age       
---------+------+---------+---------------+---------+---------------+-------------------------------------------------+-----------------white   | 6320 |         |               | t       | RowShareLock  | SELECT * FROM yewu1.t1 WHERE id = 1 FOR UPDATE; | 00:00:19.223913white   | 6320 |         |               | t       | ExclusiveLock | SELECT * FROM yewu1.t1 WHERE id = 1 FOR UPDATE; | 00:00:19.223913white   | 6320 |         |        268809 | t       | ExclusiveLock | SELECT * FROM yewu1.t1 WHERE id = 1 FOR UPDATE; | 00:00:19.223913
(3 rows)postgres=# 

谨记：心存敬畏，行有所止。