PostgreSQL pg_locks 查看数据库锁的情况

SQL

select class.relname as table_name, stat.query as sql, stat.query_start, current_timestamp-stat.query_start time_cost, lock.pid, stat.usename, lock.mode
from pg_locks lock
inner join pg_stat_activity stat on lock.pid = stat.pid
inner join pg_class class on lock.relation = class.oid
where lock.pid != pg_backend_pid();

输出

可以查看到：

• 哪张表有上锁
• 具体哪条SQL上锁了
• SQL开始执行的时间
• SQL耗时
• SQL执行的进程ID
• SQL执行的用户名
• 锁的类型

z-blog=# select class.relname as table_name, stat.query as sql, stat.query_start, current_timestamp-stat.query_start time_cost, lock.pid, stat.usename, lock.mode
z-blog-# from pg_locks lock
z-blog-# inner join pg_stat_activity stat on lock.pid = stat.pid
z-blog-# inner join pg_class class on lock.relation = class.oid
z-blog-# where lock.pid != pg_backend_pid();
 table_name |                                      sql                                       |          query_start          |    time_cost    |  pid  | usename  |       mode
------------+--------------------------------------------------------------------------------+-------------------------------+-----------------+-------+----------+------------------
 queue      | with temp as (                                                                +| 2023-07-03 11:54:11.007252+08 | 02:09:01.621343 | 74310 | abcdefgh | RowShareLock
            |     select id from queue where status='pending' limit 1 for update skip locked+|                               |                 |       |          |
            | )                                                                             +|                               |                 |       |          |
            | update queue set status='succeeded' where queue.id = (select id from temp)    +|                               |                 |       |          |
            | returning *;                                                                   |                               |                 |       |          |
 queue      | with temp as (                                                                +| 2023-07-03 11:54:11.007252+08 | 02:09:01.621343 | 74310 | abcdefgh | RowExclusiveLock
            |     select id from queue where status='pending' limit 1 for update skip locked+|                               |                 |       |          |
            | )                                                                             +|                               |                 |       |          |
            | update queue set status='succeeded' where queue.id = (select id from temp)    +|                               |                 |       |          |
            | returning *;                                                                   |                               |                 |       |          |
(2 rows)