PostgreSQL的视图pg_locks

pg_locks 是 PostgreSQL 提供的系统视图，用于显示当前数据库中的锁信息。通过查询这个视图，数据库管理员可以监控锁的使用情况，识别潜在的锁争用和死锁问题，并优化数据库性能。

pg_locks 视图字段说明

以下是 pg_locks 视图中的一些主要字段及其说明：

• locktype：锁的类型，如 relation, extend, page, tuple, transaction, etc。
• database：对象所属数据库的 OID（对象 ID）。
• relation：表或索引的 OID（如果锁对象是一个表或索引）。
• page：页号（如果锁对象是一个页）。
• tuple：行号（如果锁对象是一个行）。
• virtualxid：虚拟事务 ID。
• transactionid：事务 ID（如果锁对象是一个事务）。
• classid：系统的 OID（如果锁对象是一个泛型的数据库对象）。
• objid：对象的 OID（如果锁对象是一个泛型的数据库对象）。
• objsubid：对象的子 ID（如果锁对象是一个泛型的数据库对象）。
• virtualtransaction：虚拟事务 ID，这是一个唯一标识后台进程的标识符。
• pid：持有锁的进程 ID。
• mode：锁的模式，如 AccessShareLock, RowExclusiveLock, RowShareLock, etc。
• granted：锁是否被授予（true 或 false）。
• fastpath：锁是否通过快速路径请求（true 或 false）。

使用示例

查询所有当前锁

通用查询：

SELECT * FROM pg_locks;

根据锁类型查询

例如，查询所有表级锁：

SELECT * FROM pg_locks WHERE locktype = 'relation';

或查询所有行级锁：

SELECT * FROM pg_locks WHERE locktype = 'tuple';

查询特定数据库的锁

可以根据数据库 OID 过滤锁信息：

SELECT * FROM pg_locks WHERE database = (SELECT oid FROM pg_database WHERE datname = 'your_database_name');

查询持有锁的进程

可以根据进程 ID 进行查询：

SELECT * FROM pg_locks WHERE pid = 12345;

查询等待锁的进程

通过过滤 granted 字段为 false 可以找到那些正在等待锁的进程：

SELECT * FROM pg_locks WHERE granted = false;

检测和处理锁争用

在 pg_stat_activity 中结合锁信息

可以将 pg_stat_activity 和 pg_locks 视图结合起来，查询所有正在等待锁的会话以及持有这些锁的会话：

SELECT
    pg_stat_activity.pid,
    pg_stat_activity.query,
    pg_locks.locktype,
    pg_locks.mode,
    pg_locks.relation::regclass,
    pg_locks.transactionid,
    pg_locks.virtualxid,
    pg_locks.virtualtransaction,
    pg_locks.granted
FROM pg_stat_activity
JOIN pg_locks ON pg_stat_activity.pid = pg_locks.pid
WHERE pg_locks.granted = false;

解除锁和终止会话

在某些情况下，可能需要手动解锁，例如当某个会话长时间持有锁导致其他事务无法正常进行。可以使用 pg_terminate_backend 函数来终止持有锁的会话：

获取持有锁的进程：

SELECT * FROM pg_locks WHERE mode = 'ExclusiveLock' AND granted = true;

执行终止进程操作：

假设需要终止 PID 为 12345 的会话：

SELECT pg_terminate_backend(12345);

示例脚本：查看锁争用情况并终止占用锁的会话

以下是一个结合 pg_locks 和 pg_stat_activity 的脚本，显示当前锁争用的情况并终止占用锁的会话：

-- 查看当前锁争用情况
SELECT
waiting_locks.pid AS waiting_pid,
blocking_locks.pid AS blocking_pid,
waiting_activity.query AS waiting_query,
blocking_activity.query AS blocking_query
FROM pg_locks AS waiting_locks
JOIN pg_locks AS blocking_locks
ON waiting_locks.locktype = blocking_locks.locktype
AND waiting_locks.database IS NOT DISTINCT FROM blocking_locks.database
AND waiting_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
AND waiting_locks.page IS NOT DISTINCT FROM blocking_locks.page
AND waiting_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple
AND waiting_locks.virtualxid IS NOT DISTINCT FROM blocking_locks.virtualxid
AND waiting_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid
AND waiting_locks.classid IS NOT DISTINCT FROM blocking_locks.classid
AND waiting_locks.objid IS NOT DISTINCT FROM blocking_locks.objid
AND waiting_locks.objsubid IS NOT DISTINCT FROM blocking_locks.objsubid
AND waiting_locks.pid  blocking_locks.pid
JOIN pg_stat_activity AS waiting_activity
ON waiting_locks.pid = waiting_activity.pid
JOIN pg_stat_activity AS blocking_activity
ON blocking_locks.pid = blocking_activity.pid
WHERE NOT waiting_locks.granted;
-- 终止占用锁的会话（需要确认后再执行）
SELECT pg_terminate_backend(blocking_locks.pid)
FROM pg_locks AS waiting_locks
JOIN pg_locks AS blocking_locks
ON waiting_locks.locktype = blocking_locks.locktype
AND waiting_locks.database IS NOT DISTINCT FROM blocking_locks.database
AND waiting_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
AND waiting_locks.page IS NOT DISTINCT FROM blocking_locks.page
AND waiting_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple
AND waiting_locks.virtualxid IS NOT DISTINCT FROM blocking_locks.virtualxid
AND waiting_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid
AND waiting_locks.classid IS NOT DISTINCT FROM blocking_locks.classid
AND waiting_locks.objid IS NOT DISTINCT FROM blocking_locks.objid
AND waiting_locks.objsubid IS NOT DISTINCT FROM blocking_locks.objsubid
AND waiting_locks.pid  blocking_locks.pid
WHERE NOT waiting_locks.granted;

小结

pg_locks 视图提供了监控和管理 PostgreSQL 中锁的详细信息。通过合理利用 pg_locks，数据库管理员可以实时监控锁的使用情况，及时发现和解决锁争用问题，从而提高系统的并发性能和稳定性。