Postgresql管理-锁管理与分析

PostgreSQL中有多种锁模式，每种锁模式都有其特定的用途和兼容性规则。以下是几种常见的锁模式及其用途：

• ACCESS SHARE：用于 SELECT 操作。（mvcc 写不阻塞读，读不阻塞写）
• ROW SHARE：用于 SELECT ... FOR UPDATE 和 SELECT ... FOR SHARE 操作。
• ROW EXCLUSIVE：用于 INSERT、UPDATE、DELETE 和 CREATE INDEX CONCURRENTLY 操作。
• SHARE UPDATE EXCLUSIVE：用于 VACUUM、ANALYZE 和 CREATE INDEX 操作。
• SHARE：用于 CREATE TRIGGER 操作。
• SHARE ROW EXCLUSIVE：用于 CREATE INDEX 操作。
• EXCLUSIVE：用于 REFRESH MATERIALIZED VIEW 操作。
• ACCESS EXCLUSIVE：用于 ALTER TABLE、DROP TABLE 等操作。

ACCESS SHARE锁是一种轻量级的锁，主要用于读取操作。它允许高并发读取，并且在读取期间防止表结构被修改。通过这种锁机制，PostgreSQL能够在保证数据一致性的同时提供良好的并发性能

手动枷锁测试：

LOCK [TABLE] table_name IN ACCESS SHARE MODE;

ACCESS SHARE锁与其他类型的锁具有以下兼容性关系：

• 与ACCESS SHARE锁兼容：可以有多个事务同时持有ACCESS SHARE锁。
• 与ROW SHARE锁兼容：可以有多个事务同时持有ACCESS SHARE锁和ROW SHARE锁。
• ROW EXCLUSIVE 锁与 ACCESS SHARE 锁是兼容的（MVCC）

PG_LOCKS视图解锁

PG_LOCKS 视图概述

PG_LOCKS 是 PostgreSQL 提供的系统视图，用于展示当前数据库中的锁信息。通过查询该视图，可以监控锁的持有情况、锁类型、锁对象以及阻塞关系，帮助诊断性能问题和死锁场景。

关键字段说明

• locktype：锁类型（如 relation, tuple, transactionid 等）。
• database：锁关联的数据库 OID。
• relation：锁关联的表 OID（仅适用于表级锁）。
• pid：持有或等待锁的进程 ID。
• mode：锁模式（如 AccessShareLock, ExclusiveLock 等）。
• granted：布尔值，表示锁是否已授予（true）或等待中（false）。
• virtualtransaction：虚拟事务 ID。

常见查询示例

查看当前所有锁

SELECT locktype, relation::regclass, mode, pid, granted 
FROM pg_locks;

查找阻塞会话

SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.DATABASE = blocking_locks.DATABASE
    AND blocked_locks.relation = blocking_locks.relation
    AND blocked_locks.page = blocking_locks.page
    AND blocked_locks.tuple = blocking_locks.tuple
    AND blocked_locks.virtualxid = blocking_locks.virtualxid
    AND blocked_locks.transactionid = blocking_locks.transactionid
    AND blocked_locks.classid = blocking_locks.classid
    AND blocked_locks.objid = blocking_locks.objid
    AND blocked_locks.objsubid = blocking_locks.objsubid
    AND NOT blocked_locks.granted
    AND blocking_locks.granted
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid;

按锁类型统计

SELECT locktype, mode, COUNT(*) AS count 
FROM pg_locks 
GROUP BY locktype, mode 
ORDER BY count DESC;

锁类型与模式说明

• AccessShareLock：SELECT 查询持有的轻量级锁。
• RowExclusiveLock：INSERT/UPDATE/DELETE 持有的行级锁。
• ExclusiveLock：阻塞其他所有锁模式（如 ALTER TABLE）。
• ShareLock：CREATE INDEX 持有的锁，与 AccessShareLock 兼容。

注意事项

• 查询 pg_locks 可能对高负载系统产生性能影响，建议在诊断时使用。
• 锁冲突通常由长时间运行的事务或未提交的事务引起，结合 pg_stat_activity 分析更有效。
• 使用 pg_cancel_backend(pid) 终止阻塞会话需谨慎。