一、为什么需要关注PostgreSQL锁等待问题
在日常的数据库运维中，我们经常会遇到一些奇怪的性能问题。比如某个查询突然变慢了，或者整个系统莫名其妙地卡住了。这时候，十有八九是遇到了锁等待的问题。想象一下，就像在超市排队结账，如果前面有个人磨磨蹭蹭的，后面的人就只能干等着。
PostgreSQL作为一个功能强大的关系型数据库，提供了多种锁机制来保证数据的一致性。但是如果不小心，这些锁就可能成为性能瓶颈。特别是在高并发的生产环境中，锁等待问题可能会像多米诺骨牌一样引发连锁反应，导致整个系统响应变慢。
二、认识PostgreSQL中的锁
PostgreSQL中的锁大致可以分为两类：表级锁和行级锁。表级锁会影响整个表，而行级锁只影响特定的行。就像你去图书馆，可以锁住整个书架（表锁），也可以只锁住你要看的那本书（行锁）。
常见的锁模式包括：
- ACCESS SHARE：最弱的锁，SELECT操作会获取这种锁
- ROW SHARE：SELECT FOR UPDATE/SHARE会获取这种锁
- ROW EXCLUSIVE：UPDATE、DELETE、INSERT会获取这种锁
- SHARE UPDATE EXCLUSIVE：VACUUM、ANALYZE等操作会获取这种锁
- SHARE：CREATE INDEX会获取这种锁
- SHARE ROW EXCLUSIVE：很少使用
- EXCLUSIVE：阻塞所有操作，除了ACCESS SHARE
- ACCESS EXCLUSIVE：最强的锁，ALTER TABLE、DROP TABLE等DDL操作会获取这种锁
三、使用pg_stat_activity监控锁等待
pg_stat_activity是PostgreSQL提供的一个非常有用的视图，它可以显示当前所有会话的活动信息。通过它，我们可以找出哪些会话正在等待锁，以及是谁持有这些锁。
下面是一个实用的查询，可以帮助我们找出锁等待的情况：
-- 查询当前锁等待情况
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;
这个查询会返回所有被阻塞的会话信息，包括：
- 被阻塞的会话ID和用户名
- 阻塞者的会话ID和用户名
- 被阻塞的SQL语句
- 阻塞者的SQL语句
四、实际案例分析
让我们通过一个实际的例子来看看如何分析和解决锁等待问题。
假设我们有一个电商系统，用户在下单时需要扣减库存。我们可能会遇到这样的情况：
-- 会话1：管理员正在批量更新商品信息
BEGIN;
UPDATE products SET price = price * 1.1 WHERE category_id = 5;
-- 注意这里没有提交事务
-- 会话2：用户尝试下单购买商品1001
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 1001;
-- 这个语句会被阻塞，等待会话1释放锁
这时候，如果我们运行前面的锁等待查询，会看到类似这样的结果：
blocked_pid | blocked_user | blocking_pid | blocking_user | blocked_statement | blocking_statement
------------+--------------+--------------+---------------+----------------------------------------+------------------------------------
12345 | customer | 67890 | admin | UPDATE products SET stock = stock - 1 | UPDATE products SET price = price * 1.1 WHERE category_id = 5
从这个结果我们可以清楚地看到：
- 用户customer的会话(12345)被阻塞了
- 阻塞者是admin的会话(67890)
- 被阻塞的操作是库存扣减
- 阻塞操作是批量价格更新
五、解决锁等待问题的策略
找到问题后，我们可以采取以下几种解决方案：
优化事务设计：
- 让长时间运行的事务拆分成多个小事务
- 避免在事务中执行不必要的操作
调整隔离级别：
- 考虑使用READ COMMITTED而不是SERIALIZABLE
- 使用SELECT FOR UPDATE SKIP LOCKED跳过被锁定的行
应用层优化：
- 实现重试机制
- 设置合理的超时时间
数据库参数调整：
- 调整lock_timeout参数
- 优化max_connections参数
六、预防锁等待的最佳实践
与其等问题发生后再解决，不如提前预防。以下是一些最佳实践：
监控常态化：
-- 创建定期执行的监控脚本 SELECT now(), * FROM pg_stat_activity WHERE wait_event_type = 'Lock';设置合理的超时：
-- 设置语句超时为5秒 SET statement_timeout = '5s';使用锁超时：
-- 设置锁等待超时为3秒 SET lock_timeout = '3s';避免热点更新：
- 对高频更新的表考虑使用分区
- 对大表更新考虑分批处理
七、高级技巧：使用pg_blocking_pids函数
PostgreSQL 9.6+提供了一个更简便的函数pg_blocking_pids，可以快速找出阻塞当前会话的进程：
-- 找出阻塞当前会话的进程
SELECT pg_blocking_pids(pg_backend_pid());
-- 更详细的查询
SELECT pid, usename, pg_blocking_pids(pid) AS blocked_by, query, query_start, state
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
八、总结与建议
锁等待问题是PostgreSQL运维中常见但又容易被忽视的问题。通过pg_stat_activity视图和相关查询，我们可以有效地定位和解决这些问题。记住以下几点：
- 监控先行：建立常态化的锁监控机制
- 快速响应：发现问题后要立即处理
- 预防为主：遵循最佳实践减少锁冲突
- 合理设计：优化应用和数据库设计
最后，建议将锁等待监控纳入日常数据库健康检查的一部分，这样可以防患于未然，确保数据库的稳定运行。
评论