不灭的火

革命尚未成功,同志仍须努力:加密SHA/AES/RSA下载JDK17

作者:AlbertWen  添加时间:2025-09-01 11:43:39  修改时间:2025-09-23 07:39:36  分类:03.MySQL/PgSQL/Redis  编辑

在 PostgreSQL 中判断是否发生锁表或锁行,主要依赖于查询系统目录(pg_catalog)中的特定视图。最核心的工具是 pg_locks 视图和 pg_stat_activity 视图。

以下是详细的步骤和方法:

1. 核心概念:锁的模式 (Lock Modes)

首先,你需要理解 PostgreSQL 中锁的模式,这能帮助你判断锁的严重程度。

  1. AccessShareLock (SELECT) 通常由 SELECT 语句获取,是最弱的锁,只与 ACCESS EXCLUSIVE 冲突。
  2. RowExclusiveLock (UPDATE, DELETE, INSERT): 由写数据的 DML 语句获取。
  3. ShareLock / ShareRowExclusiveLock 由 CREATE INDEX 等操作获取。
  4. ExclusiveLock 比 SHARE 模式更强,会阻塞所有行锁。
  5. AccessExclusiveLock (DROP, ALTER, TRUNCATE, VACUUM FULL): 最强的锁,与所有其他锁模式冲突。执行 ALTER TABLEDROP 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)