PostgreSQL死锁排查与解决：定位、清理与预防指南

什么是数据库死锁

在操作系统中，死锁指多个进程因争夺共享资源而相互等待导致无法继续执行的现象。在 PostgreSQL 数据库中，这同样适用于事务层面。例如：事务 A 持有行 1 的共享锁，请求行 2 的排他锁；同时事务 B 持有行 2 的共享锁，请求行 1 的排他锁。双方互相等待对方释放资源，形成闭环依赖，最终陷入死锁状态。

如何确定死锁位置

当发现数据库异常或特定 SQL 运行缓慢时，首先需要确认是否存在阻塞或死锁活动。PostgreSQL 提供了 `pg_stat_activity` 和 `pg_locks` 两个核心视图用于诊断。

第1步：检查当前活动会话

目的：查看正在运行的 SQL 及其状态，识别处于阻塞状态的连接。

操作方法：查询目标数据库中的活跃进程。

select pid, wait_event_type, state, query from pg_stat_activity 
where datname = 'product_db' and wait_event_type is not null;

完成状态：若发现 `wait_event_type` 为 'Lock'，说明该 PID 对应的进程正在等待锁资源。

第2步：关联锁对象信息

目的：确定具体是哪个表或对象被锁住。

操作方法：先获取对象 OID，再查询锁详情。

-- 获取表的 OID
select oid from pg_class where relname = 'your_table_name';

-- 根据 OID 查询锁信息
select locktype, pid, mode, granted from pg_locks where relation = <oid>;

第3步：终止异常进程

目的：解除阻塞或死锁，恢复业务正常访问。

操作参数：需要明确区分取消查询与终止后端进程的函数差异。

重要提醒：`pg_cancel_backend(pid)` 仅能取消正在执行的 SELECT 类查询，对于已占用锁的事务或非 SELECT 语句无效。处理死锁或顽固锁时必须使用 `pg_terminate_backend(pid)`。

-- 推荐使用此函数强制断开连接并回滚事务
select pg_terminate_backend(<pid>);

常见死锁原因及应对策略

1. 缺少合理索引导致全表扫描

当查询条件未命中索引时，PostgreSQL 会执行全表扫描。这会涉及大量数据行的加锁操作，大大增加了与其他并发事务发生冲突的概率，进而引发死锁。

• 避免方法：确保高频查询的 WHERE 子句字段建立合适索引；避免过于复杂的关联查询；对大表进行分区或归档以降低扫描范围。

2. 多表操作顺序不一致

事务 A 先锁定表 X 再尝试锁定表 Y，而事务 B 先锁定表 Y 再尝试锁定表 X。这种资源申请顺序的差异是导致死锁的经典场景。

• 避免方法：在代码层统一多个表的操作顺序；尽量缩小事务范围，减少持锁时间。

避免死锁的实战建议

1. 拆分大事务：将批量更新或删除操作拆分为多次小事务提交，避免长时间占用资源。
2. 优化隔离级别：如果业务允许，可适当降低隔离级别。例如从可重复读 (RR) 调整为读已提交 (RC)，可以减少间隙锁 (Gap Lock) 带来的死锁风险。
3. 监控活跃锁：定期巡检 `pg_stat_activity` 中 `state` 为 'active' 且等待超过阈值的进程，及时介入干预。

补充：针对 TRUNCATE/DROP 卡死的排查

在执行 `DROP TABLE` 或 `TRUNCATE TABLE` 时，系统需要申请 `ACCESS EXCLUSIVE` 独占锁。如果此时有普通查询正在运行，DDL 命令会挂起等待。解决方法同上，查找并终止那些正在查询该表的残留进程即可。

常用锁模式速查

理解以下锁模式有助于更精准地分析阻塞源头：

• AccessShareLock: SELECT 查询持有的最弱锁。
• RowExclusiveLock: INSERT/UPDATE/DELETE 操作持有。
• ShareLock: CREATE INDEX 等操作持有。
• ExclusiveLock: 阻止 Row Share 和 SELECT...FOR UPDATE。
• AccessExclusiveLock: DDL 操作（如 DROP TABLE），排斥几乎所有其他锁。

以上就是 PostgreSQL 死锁排查与处理的详细内容，更多关于数据库性能优化的资料请关注本站其它相关文章！