在 PostgreSQL 中判断是否发生锁表或锁行,主要依赖于查询系统目录(pg_catalog
)中的特定视图。最核心的工具是 pg_locks
视图和 pg_stat_activity
视图。
以下是详细的步骤和方法:
1. 核心概念:锁的模式 (Lock Modes)
首先,你需要理解 PostgreSQL 中锁的模式,这能帮助你判断锁的严重程度。
- AccessShareLock (SELECT): 通常由
SELECT
语句获取,是最弱的锁,只与ACCESS EXCLUSIVE
冲突。 - RowExclusiveLock (UPDATE, DELETE, INSERT): 由写数据的 DML 语句获取。
- ShareLock / ShareRowExclusiveLock: 由
CREATE INDEX
等操作获取。 - ExclusiveLock: 比
SHARE
模式更强,会阻塞所有行锁。 - AccessExclusiveLock (DROP, ALTER, TRUNCATE, VACUUM FULL): 最强的锁,与所有其他锁模式冲突。执行
ALTER TABLE
,DROP TABLE
等 DDL 语句时会获取此锁,它会完全锁定表。
1. 查看所有锁定信息
使用 pg_locks
系统视图可以查看数据库中所有的锁定信息:
SELECT locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted FROM pg_locks;
2. 判断是否存在表级锁
表级锁的 locktype
为 relation
,可以通过关联 pg_class
视图查看具体表名:
SELECT c.relname AS table_name, l.locktype, l.mode, l.pid, l.granted FROM pg_locks l JOIN pg_class c ON l.relation = c.oid WHERE l.locktype = 'relation';
3. 判断是否存在行级锁
行级锁的 locktype
为 tuple
(元组锁),可以通过以下查询查看:
SELECT c.relname AS table_name, l.locktype, l.page, l.tuple, -- 行号 l.mode, l.pid, l.granted FROM pg_locks l JOIN pg_class c ON l.relation = c.oid WHERE l.locktype = 'tuple';
4. 查看锁等待情况
如果需要查看哪些会话在等待锁,可以使用:
SELECT waiting.pid AS waiting_pid, waiting.mode AS waiting_mode, granted.relname AS table_name, granted.pid AS granted_pid, granted.mode AS granted_mode FROM pg_locks waiting JOIN pg_locks granted ON ( waiting.relation = granted.relation AND waiting.locktype = granted.locktype AND waiting.page = granted.page AND waiting.tuple = granted.tuple AND waiting.granted = false AND granted.granted = true ) JOIN pg_class c ON waiting.relation = c.oid;
5. 结合进程信息查看
关联 pg_stat_activity
可以查看持有锁的进程具体执行的 SQL:
SELECT l.pid, c.relname AS table_name, l.locktype, l.mode, a.query, a.state FROM pg_locks l JOIN pg_class c ON l.relation = c.oid JOIN pg_stat_activity a ON l.pid = a.pid WHERE c.relkind = 'r'; -- 只查看表
杀死进程ID:
SELECT pg_terminate_backend(1156)