PostgreSQL pg_locks 查看数据库锁的情况
PostgreSQL 锁 About 2,796 wordsSQL
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扫描下方二维码关注公众号和小程序↓↓↓
Loading...