PostgreSQL pg_locks 查看数据库锁的情况

PostgreSQL About 2,796 words

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)
Views: 882 · Posted: 2023-10-30

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb/LiteNote

扫描下方二维码关注公众号和小程序↓↓↓

扫描下方二维码关注公众号和小程序↓↓↓


Today On History
Browsing Refresh