印刻数据库

外观

PostgreSQL 死锁问题 ​

死锁产生原因 ​

资源竞争 ​

当多个事务同时竞争相同的资源（如表、行、索引等）时，如果它们获取资源的顺序不一致，就可能导致死锁。

循环等待 ​

循环等待是死锁产生的必要条件之一，当事务 A 持有资源 1 并等待资源 2，事务 B 持有资源 2 并等待资源 3，事务 C 持有资源 3 并等待资源 1 时，就形成了循环等待。

长时间事务 ​

长时间运行的事务会持有资源很长时间，增加了死锁发生的概率。

不合理的锁粒度 ​

如果事务持有比实际需要更粗粒度的锁（如表锁而不是行锁），会增加资源竞争，从而增加死锁发生的概率。

死锁检测机制 ​

内置死锁检测器 ​

PostgreSQL 内置了死锁检测器，定期检查事务之间是否存在循环等待。

死锁检测参数 ​

bash
# 在 postgresql.conf 中设置
deadlock_timeout = 1s          # 死锁检测超时时间，默认为 1s
log_lock_waits = on           # 记录锁等待超过 deadlock_timeout 的情况

死锁检测流程 ​

  1. 当事务等待锁超过 deadlock_timeout 时间时，启动死锁检测
  2. 死锁检测器构建事务等待图，查找是否存在循环
  3. 如果发现死锁，选择一个事务作为牺牲品，终止该事务并释放其持有的所有锁
  4. 向客户端返回死锁错误信息

死锁日志格式 ​

当发生死锁时，PostgreSQL 会在日志中记录详细的死锁信息，包括：

  • 死锁发生的时间
  • 涉及的事务 ID
  • 事务的等待关系
  • 事务执行的 SQL 语句
  • 死锁牺牲品

死锁问题定位 ​

查看死锁日志 ​

bash
# 查看最近的死锁日志
tail -n 200 /var/log/postgresql/postgresql-14-main.log | grep -i deadlock -A 20

使用 pg_stat_activity 视图 ​

sql
-- 查看当前等待锁的事务
SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock';

-- 查看当前运行的事务及其等待状态
SELECT 
  pid, 
  usename, 
  datname, 
  state, 
  wait_event_type, 
  wait_event, 
  query 
FROM pg_stat_activity 
WHERE state = 'active' OR wait_event_type IS NOT NULL;

使用 pg_locks 视图 ​

sql
-- 查看当前所有锁
SELECT 
  locktype, 
  database, 
  relation::regclass, 
  page, 
  tuple, 
  virtualxid, 
  transactionid, 
  classid, 
  objid, 
  objsubid, 
  pid, 
  mode, 
  granted 
FROM pg_locks;

-- 查看等待锁的事务和持有的锁
SELECT 
  a.pid, 
  a.usename, 
  a.query, 
  l.locktype, 
  l.relation::regclass, 
  l.mode, 
  l.granted 
FROM pg_locks l 
JOIN pg_stat_activity a ON l.pid = a.pid 
ORDER BY a.pid, l.granted DESC;

使用 pg_blocking_pids 函数 ​

sql
-- 查看阻塞其他进程的进程 ID
SELECT pid, pg_blocking_pids(pid) as blocking_pids, query 
FROM pg_stat_activity 
WHERE pg_blocking_pids(pid) != '{}';

死锁问题解决策略 ​

1. 调整事务顺序 ​

确保所有事务按照相同的顺序访问资源，是预防死锁的最有效方法。

示例：正确的事务顺序 ​

sql
-- 事务 A
BEGIN;
UPDATE table1 SET col1 = value1 WHERE id = 1;
UPDATE table2 SET col1 = value2 WHERE id = 1;
COMMIT;

-- 事务 B
BEGIN;
UPDATE table1 SET col1 = value3 WHERE id = 1;
UPDATE table2 SET col1 = value4 WHERE id = 1;
COMMIT;

2. 减小事务粒度 ​

将大事务拆分为多个小事务，减少事务持有资源的时间。

示例：拆分大事务 ​

sql
-- 不推荐：大事务
BEGIN;
-- 执行多个 UPDATE、INSERT、DELETE 操作
COMMIT;

-- 推荐：拆分为多个小事务
BEGIN;
-- 执行部分操作
COMMIT;

BEGIN;
-- 执行另一部分操作
COMMIT;

3. 减少锁持有时间 ​

尽量在事务末尾执行提交或回滚，减少锁持有时间。

示例：优化事务结构 ​

sql
-- 不推荐：事务中包含非数据库操作
BEGIN;
UPDATE table1 SET col1 = value1 WHERE id = 1;
-- 执行外部 API 调用（耗时操作）
COMMIT;

-- 推荐：将耗时操作移到事务外
-- 执行外部 API 调用（耗时操作）
BEGIN;
UPDATE table1 SET col1 = value1 WHERE id = 1;
COMMIT;

4. 使用更细粒度的锁 ​

尽量使用行级锁而不是表级锁，减少资源竞争。

示例：使用行级锁 ​

sql
-- 不推荐：使用表级锁
LOCK TABLE table1 IN EXCLUSIVE MODE;
UPDATE table1 SET col1 = value1 WHERE id = 1;

-- 推荐：使用行级锁（默认）
UPDATE table1 SET col1 = value1 WHERE id = 1;

5. 使用 NOWAIT 或 SKIP LOCKED ​

使用 NOWAIT 选项避免长时间等待锁，或使用 SKIP LOCKED 选项跳过已锁定的行。

示例：使用 NOWAIT ​

sql
-- 使用 NOWAIT 避免等待锁
UPDATE table1 SET col1 = value1 WHERE id = 1 FOR UPDATE NOWAIT;

-- 使用 SKIP LOCKED 跳过已锁定的行
SELECT * FROM table1 WHERE id IN (1, 2, 3) FOR UPDATE SKIP LOCKED;

6. 调整死锁检测参数 ​

根据实际情况调整 deadlock_timeout 参数，平衡死锁检测开销和检测及时性。

bash
# 在 postgresql.conf 中设置
deadlock_timeout = 500ms  # 缩短死锁检测时间

7. 使用乐观并发控制 ​

对于读多写少的场景，可以考虑使用乐观并发控制，减少锁的使用。

示例：使用版本列 ​

sql
-- 创建带版本列的表
CREATE TABLE table1 (
  id SERIAL PRIMARY KEY,
  col1 VARCHAR(100),
  version INT DEFAULT 0
);

-- 更新时检查版本
UPDATE table1 
SET col1 = 'new_value', version = version + 1 
WHERE id = 1 AND version = 0;

不同版本的死锁特性 ​

PostgreSQL 12 及以上版本 ​

  • 改进了死锁检测算法，提高了检测效率
  • 支持 SKIP LOCKED 选项，用于跳过已锁定的行
  • 改进了锁管理，减少了锁冲突

PostgreSQL 13 及以上版本 ​

  • 引入了增量排序功能，减少了某些查询的锁持有时间
  • 改进了并行查询的锁管理
  • 支持 pg_stat_progress_vacuum 视图，提供更详细的 VACUUM 进度信息

PostgreSQL 14 及以上版本 ​

  • 引入了资源组功能，可用于限制特定用户的资源使用
  • 改进了 pg_stat_activity 视图，增加了 wait_event 和 wait_event_type 字段
  • 支持 log_recovery_conflict_waits 参数，用于记录恢复冲突等待

PostgreSQL 15 及以上版本 ​

  • 引入了新的权限模型，影响锁管理
  • 改进了逻辑复制的锁管理
  • 支持 pg_stat_wal 视图，提供更详细的 WAL 统计信息

常见问题（FAQ） ​

Q1: 如何判断是否发生了死锁？ ​

A1: 可以通过以下方式判断是否发生了死锁：

  • 应用程序收到 "deadlock detected" 错误
  • 数据库日志中记录了死锁信息
  • pg_stat_activity 视图中显示多个事务处于 "waiting" 状态
  • 使用 pg_blocking_pids 函数发现循环等待

Q2: 死锁发生后，PostgreSQL 会自动处理吗？ ​

A2: 是的，PostgreSQL 内置了死锁检测器，会定期检查事务之间是否存在循环等待。当检测到死锁时，PostgreSQL 会选择一个事务作为牺牲品，终止该事务并释放其持有的所有锁，让其他事务继续执行。

Q3: 如何避免死锁？ ​

A3: 避免死锁可以采取以下措施：

  • 确保所有事务按照相同的顺序访问资源
  • 减小事务粒度，将大事务拆分为多个小事务
  • 减少锁持有时间，在事务末尾执行提交或回滚
  • 使用更细粒度的锁，尽量使用行级锁而不是表级锁
  • 使用 NOWAIT 或 SKIP LOCKED 选项避免长时间等待锁
  • 调整 deadlock_timeout 参数，平衡死锁检测开销和检测及时性

Q4: 如何处理死锁？ ​

A4: 处理死锁可以采取以下措施：

  • 捕获应用程序中的死锁错误，并重试事务
  • 分析死锁日志，找出死锁原因
  • 优化事务结构和 SQL 语句
  • 调整数据库配置参数
  • 考虑使用乐观并发控制

Q5: 死锁会导致数据不一致吗？ ​

A5: 不会。当 PostgreSQL 检测到死锁时，会选择一个事务作为牺牲品，终止该事务并回滚其所有操作，确保数据一致性。

Q6: 长时间运行的事务会增加死锁概率吗？ ​

A6: 是的。长时间运行的事务会持有资源很长时间，增加了其他事务等待资源的时间，从而增加了死锁发生的概率。

Q7: 如何监控死锁发生的频率？ ​

A7: 可以通过以下方式监控死锁发生的频率：

  • 定期检查数据库日志中的死锁记录
  • 使用监控工具（如 Prometheus + Grafana）收集死锁相关指标
  • 创建自定义指标，记录死锁发生次数
  • 定期分析死锁日志，统计死锁发生的频率和原因

Q8: 如何优化复杂事务的锁使用？ ​

A8: 优化复杂事务的锁使用可以采取以下措施：

  • 分析事务的锁持有顺序，确保一致
  • 减小事务的锁持有范围，只锁定必要的资源
  • 考虑使用部分索引，减少索引锁的范围
  • 优化查询语句，减少锁持有时间
  • 考虑使用异步处理，将复杂事务拆分为多个阶段

死锁问题最佳实践 ​

1. 设计合理的数据库架构 ​

  • 合理设计表结构和索引
  • 避免过度规范化或反规范化
  • 考虑使用分区表减少锁冲突

2. 优化事务设计 ​

  • 确保所有事务按照相同的顺序访问资源
  • 减小事务粒度，将大事务拆分为多个小事务
  • 减少锁持有时间，在事务末尾执行提交或回滚

3. 优化 SQL 语句 ​

  • 避免全表扫描，使用索引减少锁范围
  • 优化查询计划，减少锁持有时间
  • 考虑使用 FOR UPDATE SKIP LOCKED 或 FOR SHARE 锁模式

4. 监控和分析 ​

  • 定期监控死锁发生情况
  • 分析死锁日志，找出死锁原因
  • 针对常见死锁场景进行优化

5. 测试和验证 ​

  • 在测试环境中模拟高并发场景，检测死锁
  • 定期进行性能测试，验证死锁优化效果
  • 记录测试结果，持续改进

配置验证和测试 ​

1. 验证死锁检测配置 ​

sql
-- 检查死锁相关配置
SELECT name, setting FROM pg_settings WHERE name IN (
  'deadlock_timeout', 'log_lock_waits', 'max_locks_per_transaction'
);

2. 测试死锁场景 ​

测试场景 1：两个事务循环等待 ​

sql
-- 会话 1
BEGIN;
UPDATE table1 SET col1 = 'value1' WHERE id = 1;

-- 会话 2
BEGIN;
UPDATE table2 SET col1 = 'value2' WHERE id = 1;

-- 会话 1
UPDATE table2 SET col1 = 'value3' WHERE id = 1;

-- 会话 2
UPDATE table1 SET col1 = 'value4' WHERE id = 1;

测试场景 2：使用 NOWAIT 避免死锁 ​

sql
-- 会话 1
BEGIN;
SELECT * FROM table1 WHERE id = 1 FOR UPDATE;

-- 会话 2
BEGIN;
SELECT * FROM table1 WHERE id = 1 FOR UPDATE NOWAIT;

3. 监控死锁发生 ​

bash
# 查看死锁日志
tail -f /var/log/postgresql/postgresql-14-main.log | grep -i deadlock

# 使用 pg_stat_activity 监控
watch -n 1 "psql -c 'SELECT pid, usename, query, wait_event_type, wait_event FROM pg_stat_activity WHERE state = \'active\' OR wait_event_type IS NOT NULL;'"

通过遵循本指南，可以有效地定位、分析和解决 PostgreSQL 死锁问题，提高数据库的并发处理能力和稳定性。