外观
PostgreSQL 锁和等待事件
锁类型
PostgreSQL支持多种锁类型，用于控制对数据库对象的并发访问。
1. 表级锁
| 锁类型 | 描述 | 冲突锁类型 |
|---|---|---|
| ACCESS SHARE | 读取表数据（SELECT） | ACCESS EXCLUSIVE |
| ROW SHARE | 读取表数据，可能修改（SELECT FOR UPDATE） | EXCLUSIVE, ACCESS EXCLUSIVE |
| ROW EXCLUSIVE | 修改表数据（INSERT, UPDATE, DELETE） | SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE |
| SHARE UPDATE EXCLUSIVE | 并行扫描表 | SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE |
| SHARE | 创建索引（CREATE INDEX） | ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE |
| SHARE ROW EXCLUSIVE | 行级共享锁 | ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE |
| EXCLUSIVE | 限制表访问（ALTER TABLE ... ADD COLUMN） | 除ACCESS SHARE外的所有锁 |
| ACCESS EXCLUSIVE | 独占表访问（DROP TABLE, TRUNCATE, VACUUM FULL） | 所有锁 |
2. 行级锁
- FOR UPDATE：获取行的独占锁，防止其他事务修改或删除
- FOR SHARE：获取行的共享锁，防止其他事务删除或修改
- FOR NO KEY UPDATE：获取行的非键更新锁，防止其他事务获取FOR UPDATE锁
- FOR KEY SHARE：获取行的键共享锁，防止其他事务获取EXCLUSIVE锁
3. 其他锁类型
- 页级锁：保护数据页的完整性
- 索引锁：保护索引结构的完整性
- 扩展锁：保护表空间扩展操作
- 事务ID锁：处理事务ID的分配和回收
等待事件
等待事件是PostgreSQL 9.6引入的功能，用于诊断事务等待的原因。
1. 锁等待事件
| 等待事件 | 描述 | 示例场景 |
|---|---|---|
| Lock:relation | 等待表级锁 | 一个事务在ALTER TABLE，另一个事务在SELECT |
| Lock:transactionid | 等待事务ID锁 | 长事务持有锁，其他事务等待 |
| Lock:tuple | 等待行级锁 | 两个事务同时更新同一行 |
| Lock:page | 等待页级锁 | 大量并发更新同一数据页 |
| Lock:virtualxid | 等待虚拟事务ID锁 | 事务等待自身子事务完成 |
2. I/O等待事件
| 等待事件 | 描述 | 示例场景 |
|---|---|---|
| IO:XactSLRULock | 等待SLRU页锁定 | 大量并发事务导致SLRU页竞争 |
| IO:BufFileRead | 等待临时文件读取 | 大型排序或哈希操作 |
| IO:BufFileWrite | 等待临时文件写入 | 大型排序或哈希操作 |
3. 其他等待事件
| 等待事件 | 描述 | 示例场景 |
|---|---|---|
| LWLock:buffer_content | 等待缓冲区内容锁 | 大量并发访问同一缓冲区 |
| LWLock:lock_manager | 等待锁管理器锁 | 大量并发锁请求 |
| LWLock:transactionid | 等待事务ID锁 | 长事务导致事务ID回卷 |
| Client:ClientRead | 等待客户端输入 | 客户端发送请求后未响应 |
监控锁和等待事件
1. 查看当前锁状态
sql
-- 查看所有锁
SELECT
locktype,
database,
relation,
page,
tuple,
virtualxid,
transactionid,
pid,
mode,
granted
FROM pg_locks;
-- 查看锁与事务的关联
SELECT
l.locktype,
l.database,
l.relation,
l.mode,
l.granted,
a.pid,
a.usename,
a.application_name,
a.client_addr,
a.query,
a.query_start
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid;2. 监控等待事件
sql
-- 查看当前等待事件
SELECT
pid,
usename,
application_name,
client_addr,
wait_event_type,
wait_event,
state,
query,
query_start,
now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active' AND wait_event IS NOT NULL;
-- 统计等待事件
SELECT
wait_event_type,
wait_event,
count(*)
FROM pg_stat_activity
WHERE wait_event IS NOT NULL
GROUP BY wait_event_type, wait_event
ORDER BY count(*) DESC;3. 检测死锁
sql
-- 查看死锁日志（需要配置log_deadlocks = on）
SELECT * FROM pg_log WHERE message LIKE '%deadlock%';
-- 使用pg_stat_database_conflicts查看冲突统计
SELECT * FROM pg_stat_database_conflicts;4. 锁等待查询
sql
-- 查找阻塞其他事务的查询
SELECT
blocking.pid AS blocking_pid,
blocking.usename AS blocking_user,
blocking.query AS blocking_query,
blocking.query_start AS blocking_start,
blocked.pid AS blocked_pid,
blocked.usename AS blocked_user,
blocked.query AS blocked_query,
blocked.query_start AS blocked_start,
now() - blocked.query_start AS wait_duration
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocked.waiting_pid = blocking.pid
WHERE blocked.waiting = true;锁问题处理策略
1. 查找并终止阻塞事务
sql
-- 查找阻塞事务
SELECT
a.pid AS blocking_pid,
a.query AS blocking_query,
b.pid AS blocked_pid,
b.query AS blocked_query
FROM pg_locks l1
JOIN pg_stat_activity a ON l1.pid = a.pid
JOIN pg_locks l2 ON l1.locktype = l2.locktype AND l1.relation = l2.relation AND l1.page = l2.page AND l1.tuple = l2.tuple AND l1.virtualxid = l2.virtualxid AND l1.transactionid = l2.transactionid AND l1.classid = l2.classid AND l1.objid = l2.objid AND l1.objsubid = l2.objsubid
JOIN pg_stat_activity b ON l2.pid = b.pid
WHERE l1.granted AND NOT l2.granted;
-- 终止阻塞事务
SELECT pg_terminate_backend(blocking_pid);2. 优化长事务
sql
-- 查找长事务
SELECT
pid,
usename,
application_name,
client_addr,
query,
state,
now() - query_start AS duration
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
AND now() - query_start > interval '5 minutes'
ORDER BY duration DESC;
-- 终止长事务
SELECT pg_terminate_backend(pid);3. 优化锁竞争
sql
-- 减少锁持有时间
-- 1. 优化查询性能，减少事务执行时间
-- 2. 使用更小粒度的锁（行级锁而非表级锁）
-- 3. 避免长事务
-- 示例：优化批量更新，减少锁持有时间
-- 不推荐：一次性更新所有行
UPDATE users SET status = 'active';
-- 推荐：分批更新，减少锁持有时间
DO $$
DECLARE
batch_size INT := 1000;
total_rows INT;
processed_rows INT := 0;
BEGIN
SELECT COUNT(*) INTO total_rows FROM users;
WHILE processed_rows < total_rows LOOP
UPDATE users
SET status = 'active'
WHERE id IN (SELECT id FROM users ORDER BY id LIMIT batch_size OFFSET processed_rows);
COMMIT;
processed_rows := processed_rows + batch_size;
-- 可选：添加延迟，减少系统负载
-- PERFORM pg_sleep(0.1);
END LOOP;
END $$;配置优化
1. 锁相关配置
bash
# 配置锁等待超时（PostgreSQL 9.6+）
echo "lock_timeout = 30s" >> postgresql.conf
# 配置死锁检测时间
echo "deadlock_timeout = 1s" >> postgresql.conf
# 启用死锁日志
echo "log_deadlocks = on" >> postgresql.conf
# 记录锁等待事件
echo "log_lock_waits = on" >> postgresql.conf
# 设置日志级别
echo "log_min_messages = notice" >> postgresql.conf
# 重新加载配置
pg_ctl reload -D /var/lib/postgresql/14/main2. 事务相关配置
bash
# 配置空闲事务超时
echo "idle_in_transaction_session_timeout = 10min" >> postgresql.conf
# 配置语句超时
echo "statement_timeout = 5min" >> postgresql.conf
# 重新加载配置
pg_ctl reload -D /var/lib/postgresql/14/main最佳实践
1. 开发最佳实践
- 避免长事务，尽量保持事务简短
- 使用合适的隔离级别，避免不必要的锁
- 使用行级锁（FOR UPDATE）代替表级锁
- 优化查询性能，减少锁持有时间
- 避免在事务中执行外部操作（如HTTP请求）
2. 运维最佳实践
- 配置合理的锁等待超时和死锁检测时间
- 启用锁等待和死锁日志
- 监控锁等待事件，设置告警阈值
- 定期检查长事务，及时终止异常事务
- 使用连接池管理数据库连接，避免连接泄漏
3. 监控最佳实践
- 使用Prometheus+Grafana监控锁和等待事件
- 设置锁等待时间告警（如超过30秒）
- 监控死锁发生率
- 分析锁等待事件趋势，提前发现潜在问题
- 定期生成锁使用报告
常见问题（FAQ）
Q1: 如何查看哪些事务正在阻塞其他事务？
A1: 使用以下SQL查询：
sql
SELECT
blocking.pid AS blocking_pid,
blocking.usename AS blocking_user,
blocking.query AS blocking_query,
blocked.pid AS blocked_pid,
blocked.usename AS blocked_user,
blocked.query AS blocked_query,
now() - blocked.query_start AS wait_duration
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocked.waiting_pid = blocking.pid
WHERE blocked.waiting = true;Q2: 如何避免死锁？
A2: 可以通过以下方法避免死锁：
- 保持事务简短，减少锁持有时间
- 统一访问资源的顺序（如总是按主键顺序更新行）
- 使用更低的隔离级别（如READ COMMITTED）
- 避免在同一事务中修改多个表
- 配置合理的锁等待超时
Q3: 如何处理锁等待超时？
A3: 处理锁等待超时的方法：
- 分析阻塞事务，终止长时间运行的事务
- 优化查询性能，减少锁持有时间
- 调整锁等待超时参数（lock_timeout）
- 考虑使用更细粒度的锁
- 重新设计应用逻辑，避免并发冲突
Q4: 如何优化大量并发插入导致的锁竞争？
A4: 优化方法：
- 使用分区表分散插入负载
- 调整fillfactor参数，减少页面分裂
- 使用批量插入代替单条插入
- 考虑使用UNLOGGED表（如果可以接受数据丢失风险）
- 调整wal_buffers和checkpoint参数
Q5: 如何监控锁的使用情况？
A5: 可以通过以下方式监控：
- 查询pg_locks和pg_stat_activity视图
- 启用log_lock_waits参数记录锁等待
- 使用pg_stat_database_conflicts查看冲突统计
- 结合Prometheus+Grafana设置锁等待告警
- 使用pgBadger分析锁相关日志
Q6: 锁等待事件和死锁有什么区别？
A6:
- 锁等待事件：一个事务等待另一个事务释放锁，可能会在超时后失败
- 死锁：两个或多个事务互相等待对方释放锁，导致所有事务都无法继续执行
- PostgreSQL会自动检测和解决死锁，终止其中一个事务
Q7: 如何配置锁等待日志？
A7: 需要在postgresql.conf中配置以下参数：
bash
log_lock_waits = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_min_messages = noticeQ8: 如何优化VACUUM操作导致的锁竞争？
A8: 优化方法：
- 使用autovacuum，避免手动VACUUM FULL
- 调整autovacuum配置参数（如autovacuum_naptime、autovacuum_vacuum_threshold）
- 在低峰期执行VACUUM操作
- 使用VACUUM (FREEZE)代替VACUUM FULL
- 考虑使用pg_repack代替VACUUM FULL