PostgreSQL pgrowlocks 扩展

pgrowlocks 是 PostgreSQL 的一个系统扩展，用于显示表中行级锁定信息。这个扩展特别适合诊断锁争用问题和性能调优。

一、扩展安装与启用

1. 安装扩展

-- 使用超级用户安装
CREATE EXTENSION pgrowlocks;

2. 验证安装

-- 查看扩展是否安装成功
SELECT * FROM pg_available_extensions WHERE name = 'pgrowlocks';-- 查看扩展提供的函数
\df+ pgrowlocks

二、基本功能与使用

1. 核心功能

pgrowlocks 提供的主要功能：

显示表中当前被锁定的行
显示锁定类型（共享锁、排他锁等）
显示锁定的事务ID
显示锁定模式

2. 基本用法

-- 查看表中被锁定的行
SELECT * FROM pgrowlocks('表名');-- 示例：查看employees表中的行锁
SELECT * FROM pgrowlocks('employees');

三、返回字段详解

pgrowlocks 函数返回以下字段：

字段名	数据类型	描述
locked_row	tid	被锁定行的元组ID（页面号和行号）
locker	xid	持有锁的事务ID
multi	boolean	是否是多事务锁定
xids	xid[]	事务ID数组（当multi为true时）
modes	text[]	锁定模式数组
pids	integer[]	后端进程ID数组

四、锁定模式说明

PostgreSQL 中的行级锁模式：

锁定模式	描述
FOR UPDATE	行更新排他锁
FOR NO KEY UPDATE	非键更新排他锁
FOR SHARE	共享锁
FOR KEY SHARE	键共享锁

五、实际应用场景

1. 诊断锁等待问题

-- 找出锁定的行和等待的进程
SELECT l.*, a.query, a.wait_event_type, a.wait_event
FROM pgrowlocks('accounts') l
JOIN pg_stat_activity a ON a.pid = ANY(l.pids);

2. 监控长时间运行的事务

-- 结合pg_stat_activity查看锁定行的详细信息
SELECT l.locked_row, l.modes, l.pids, a.usename, a.query, a.query_start, age(now(), a.query_start) as running_time
FROM pgrowlocks('orders') l
JOIN pg_stat_activity a ON a.pid = ANY(l.pids)
ORDER BY running_time DESC;

3. 分析死锁情况

-- 检查可能导致死锁的行锁
SELECT l.locked_row, l.modes, l.pids, a.query
FROM pgrowlocks('inventory') l
JOIN pg_stat_activity a ON a.pid = ANY(l.pids)
WHERE array_length(l.pids, 1) > 1;

六、高级用法

1. 查看特定行的锁定状态

-- 先获取行的ctid（行标识符）
SELECT ctid, * FROM accounts WHERE account_id = 12345;-- 然后检查锁定状态（假设ctid是'(5,2)'）
SELECT * FROM pgrowlocks('accounts') 
WHERE locked_row = '(5,2)'::tid;

2. 批量检查多个表的锁

-- 创建一个函数来检查所有用户表的行锁
CREATE OR REPLACE FUNCTION check_all_table_locks()
RETURNS TABLE (table_name text, locked_row tid, modes text[], pids integer[]) AS $$
DECLAREtbl text;
BEGINFOR tbl IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'LOOPRETURN QUERY EXECUTE format('SELECT %L as table_name, locked_row, modes, pids FROM pgrowlocks(%L)',tbl, tbl);END LOOP;RETURN;
END;
$$ LANGUAGE plpgsql;-- 使用函数检查所有表
SELECT * FROM check_all_table_locks() WHERE pids IS NOT NULL;

七、性能考虑

性能影响：pgrowlocks 需要扫描表的锁定信息，对大表可能有性能影响
生产环境使用：建议在低峰期使用，或限制查询范围
替代方案：对于大型数据库，考虑使用 pg_lock_status() 等系统函数

八、与其他工具的对比

特性	pgrowlocks	pg_locks	pg_stat_activity
粒度	行级	对象级	进程级
易用性	高	中	中
详细信息	锁模式、事务ID	锁类型、关系	查询、等待事件
最佳适用场景	行锁分析	对象锁分析	会话分析

九、最佳实践

定期监控：设置定时任务检查关键表的行锁
结合其他视图：与 pg_stat_activity 和 pg_locks 一起使用
文档记录：记录常见的锁争用模式和解决方案
自动化警报：对长时间行锁设置监控警报

十、限制与注意事项

需要表上的 SELECT 权限
不显示已授予但未等待的锁
对于分区表，需要分别检查每个分区
在大表上运行可能影响性能

十一、故障排除示例

场景：订单处理系统出现超时

-- 1. 检查orders表的行锁
SELECT * FROM pgrowlocks('orders') WHERE array_length(pids, 1) > 0;-- 2. 查看持有锁的查询
SELECT l.locked_row, l.modes, a.pid, a.usename, a.query, a.query_start, age(now(), a.query_start)
FROM pgrowlocks('orders') l
JOIN pg_stat_activity a ON a.pid = ANY(l.pids);-- 3. 如果确定是问题锁，可以终止进程
-- SELECT pg_terminate_backend(pid) FROM pg_stat_activity 
-- WHERE pid = 发现的阻塞进程ID;