PostgreSQL 死锁与锁等待

死锁与锁等待是 PostgreSQL 并发控制中的常见问题，它们会导致系统性能下降，甚至服务不可用。本文档详细介绍了 PostgreSQL 的锁机制、死锁与锁等待的识别方法、解决方案和预防措施。

锁机制概述

1. 锁的定义与作用

锁是数据库并发控制的核心机制，用于确保多个事务可以安全地并发执行，防止数据不一致和冲突。锁的主要作用包括：

保护数据一致性：防止多个事务同时修改同一数据导致数据不一致
实现事务隔离：确保事务在不同隔离级别下的正确性
协调并发访问：合理分配资源，避免冲突

2. PostgreSQL 锁类型

PostgreSQL 支持多种锁类型，按粒度可分为表级锁、行级锁和页级锁，按模式可分为以下几类：

锁模式	简写	描述	示例操作
ACCESS SHARE	AS	读取表数据	SELECT
ROW SHARE	RS	行共享锁	SELECT FOR UPDATE/SHARE
ROW EXCLUSIVE	RX	行排他锁	INSERT/UPDATE/DELETE
SHARE UPDATE EXCLUSIVE	SUX	共享更新排他锁	VACUUM (without FULL), ANALYZE
SHARE	S	共享锁	CREATE INDEX
SHARE ROW EXCLUSIVE	SRX	共享行排他锁	CREATE INDEX CONCURRENTLY
EXCLUSIVE	X	排他锁	ALTER TABLE, DROP TABLE
ACCESS EXCLUSIVE	AX	访问排他锁	DROP DATABASE, TRUNCATE TABLE

3. 锁冲突矩阵

不同锁模式之间存在冲突关系，下表显示了哪些锁模式之间会发生冲突：

请求锁模式	已持有锁模式
	AS
------------	----
AS	✅
RS	✅
RX	✅
SUX	✅
S	✅
SRX	✅
X	✅
AX	❌

识别死锁与锁等待

1. 查看当前锁等待

sql

-- 查看当前锁等待情况
SELECT 
    bl.pid AS blocked_pid,
    a.usename AS blocked_user,
    kl.pid AS blocking_pid,
    ka.usename AS blocking_user,
    a.query AS blocked_query,
    ka.query AS blocking_query,
    a.query_start AS blocked_query_start,
    now() - a.query_start AS blocked_duration,
    kl.mode AS blocking_mode,
    bl.mode AS blocked_mode,
    kl.relation::regclass AS locked_object
FROM pg_locks bl
JOIN pg_stat_activity a ON bl.pid = a.pid
JOIN pg_locks kl ON bl.locktype = kl.locktype
                AND bl.database IS NOT DISTINCT FROM kl.database
                AND bl.relation IS NOT DISTINCT FROM kl.relation
                AND bl.page IS NOT DISTINCT FROM kl.page
                AND bl.tuple IS NOT DISTINCT FROM kl.tuple
                AND bl.virtualxid IS NOT DISTINCT FROM kl.virtualxid
                AND bl.transactionid IS NOT DISTINCT FROM kl.transactionid
                AND bl.classid IS NOT DISTINCT FROM kl.classid
                AND bl.objid IS NOT DISTINCT FROM kl.objid
                AND bl.objsubid IS NOT DISTINCT FROM kl.objsubid
                AND bl.pid != kl.pid
JOIN pg_stat_activity ka ON kl.pid = ka.pid
WHERE NOT bl.granted;

2. 查看死锁日志

PostgreSQL 默认会在日志中记录死锁信息，格式如下：

2025-12-22 10:30:45 UTC [1234]: [1-1] ERROR:  deadlock detected
2025-12-22 10:30:45 UTC [1234]: [2-1] DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 9012.
        Process 9012 waits for ShareLock on transaction 5678; blocked by process 1234.
        Process 1234: UPDATE table1 SET col1 = 1 WHERE id = 1;
        Process 9012: UPDATE table1 SET col1 = 2 WHERE id = 2;
2025-12-22 10:30:45 UTC [1234]: [3-1] HINT:  See server log for query details.
2025-12-22 10:30:45 UTC [1234]: [4-1] CONTEXT:  while updating tuple (0,1) in relation "table1"

3. 使用 pg_stat_activity 查看长查询

sql

-- 查看运行时间超过5分钟的查询
SELECT 
    pid,
    usename,
    datname,
    query,
    query_start,
    now() - query_start AS duration,
    state,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE state <> 'idle'
    AND now() - query_start > interval '5 minutes'
ORDER BY duration DESC;

4. 使用 pg_stat_statements 分析锁相关查询

sql

-- 查看锁相关查询的统计信息
SELECT 
    queryid,
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements 
WHERE query LIKE '%SELECT FOR UPDATE%' 
    OR query LIKE '%UPDATE%' 
    OR query LIKE '%DELETE%'
ORDER BY total_time DESC 
LIMIT 10;

解决死锁与锁等待

1. 紧急处理措施

1.1 终止阻塞进程

sql

-- 终止特定阻塞进程
SELECT pg_terminate_backend(<blocking_pid>);

-- 终止所有阻塞其他进程的进程
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE pid IN (
    SELECT DISTINCT kl.pid 
    FROM pg_locks bl 
    JOIN pg_locks kl ON bl.locktype = kl.locktype 
                    AND bl.database IS NOT DISTINCT FROM kl.database 
                    AND bl.relation IS NOT DISTINCT FROM kl.relation 
                    AND bl.page IS NOT DISTINCT FROM kl.page 
                    AND bl.tuple IS NOT DISTINCT FROM kl.tuple 
                    AND bl.virtualxid IS NOT DISTINCT FROM kl.virtualxid 
                    AND bl.transactionid IS NOT DISTINCT FROM kl.transactionid 
                    AND bl.classid IS NOT DISTINCT FROM kl.classid 
                    AND bl.objid IS NOT DISTINCT FROM kl.objid 
                    AND bl.objsubid IS NOT DISTINCT FROM kl.objsubid 
                    AND bl.pid != kl.pid 
    WHERE NOT bl.granted
);

1.2 调整锁超时

sql

-- 临时调整锁超时（单位：毫秒）
SET lock_timeout = '5000'; -- 5秒

-- 永久调整
ALTER SYSTEM SET lock_timeout = '10000';
SELECT pg_reload_conf();

1.3 手动提交或回滚事务

如果能够连接到阻塞进程，可以尝试手动提交或回滚事务：

sql

-- 如果阻塞进程是交互式会话，可以尝试回滚
ROLLBACK;

-- 或提交
COMMIT;

2. 根本原因分析与解决

2.1 优化查询和事务

减少事务持有锁的时间：
- 尽量缩短事务长度
- 避免在事务中进行长时间的计算或外部操作
- 及时提交或回滚事务
统一访问顺序：
- 对多个表的访问使用相同的顺序
- 对同一表的多行访问使用相同的顺序（例如，按主键升序访问）
使用合适的锁级别：
- 只获取必要的锁级别
- 避免使用 SELECT FOR UPDATE/SHARE，除非确实需要
- 使用行级锁而不是表级锁

2.2 优化数据库设计

合理设计索引：
- 为频繁访问的列创建索引
- 避免过多的索引，因为索引会增加锁竞争
使用分区表：
- 对于大表，考虑使用分区表减少锁竞争
- 分区表可以将锁范围限制在特定分区
使用合适的数据类型：
- 选择合适的数据类型，减少锁持有时间
- 避免使用过大的数据类型

2.3 配置优化

调整事务隔离级别：

sql

-- 查看当前事务隔离级别
SHOW default_transaction_isolation;

-- 临时调整事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 永久调整
ALTER SYSTEM SET default_transaction_isolation = 'read committed';
SELECT pg_reload_conf();

调整死锁检测频率：

sql

-- 查看当前死锁检测频率
SHOW deadlock_timeout;

-- 调整死锁检测频率（单位：毫秒）
ALTER SYSTEM SET deadlock_timeout = '1000'; -- 1秒
SELECT pg_reload_conf();

预防死锁与锁等待

1. 应用程序层面

优化代码逻辑：
- 避免在循环中执行事务
- 减少事务嵌套
- 避免在事务中调用外部服务
使用乐观锁：
- 对于读多写少的场景，考虑使用乐观锁
- 使用版本号或时间戳字段实现乐观锁

使用 SKIP LOCKED 或 NOWAIT：

sql

-- 使用 SKIP LOCKED 跳过已锁定的行
SELECT * FROM table1 FOR UPDATE SKIP LOCKED LIMIT 10;

-- 使用 NOWAIT 立即返回，不等待锁释放
SELECT * FROM table1 FOR UPDATE NOWAIT;

2. 数据库层面

定期维护：
- 定期执行 VACUUM 和 ANALYZE 操作
- 清理无效数据，减少锁竞争
监控锁使用：
- 实时监控锁使用情况
- 及时发现锁竞争热点
配置合适的参数：
- 根据业务需求配置合适的锁超时
- 调整死锁检测频率
- 配置合适的事务隔离级别

3. 架构层面

读写分离：
- 将读请求分发到从库，减少主库锁竞争
- 从库可以使用不同的隔离级别
分片架构：
- 使用 Citus 等扩展实现分片
- 将数据分散到多个节点，减少单节点锁竞争
微服务架构：
- 将业务拆分为多个微服务
- 每个微服务使用独立的数据库或数据库实例

死锁与锁等待案例分析

案例一：交叉更新导致死锁

问题现象

数据库日志中频繁出现死锁错误
应用程序偶尔出现事务回滚
死锁日志显示两个事务互相等待对方释放锁

诊断过程

查看死锁日志，发现两个事务的更新顺序相反
事务1：UPDATE table1 SET col1 = 1 WHERE id = 1; UPDATE table1 SET col1 = 2 WHERE id = 2;
事务2：UPDATE table1 SET col1 = 3 WHERE id = 2; UPDATE table1 SET col1 = 4 WHERE id = 1;

解决方案

统一更新顺序，两个事务都按 id 升序更新
优化事务逻辑，将大事务拆分为小事务
调整应用程序代码，确保所有事务对表的访问顺序一致

案例二：长时间事务导致锁等待

问题现象

应用程序响应时间延长
数据库连接数增加
大量查询处于等待状态

诊断过程

查看 pg_stat_activity，发现一个长时间运行的事务
该事务持有大量行锁，导致其他事务无法执行
事务中包含外部 API 调用，导致事务持有锁时间过长

解决方案

优化事务逻辑，将外部 API 调用移到事务外
缩短事务持有锁的时间
使用异步处理方式处理外部请求
添加锁超时配置，防止事务无限等待

案例三：缺少索引导致表级锁

问题现象

UPDATE 操作导致表级锁
大量查询等待表级锁释放
系统性能急剧下降

诊断过程

查看查询计划，发现 UPDATE 操作缺少索引，导致全表扫描
全表扫描导致表级锁，而非行级锁
大量并发 UPDATE 操作导致锁竞争

解决方案

添加缺失的索引
优化 UPDATE 查询，使用索引减少扫描范围
考虑使用部分索引或覆盖索引
监控索引使用情况，确保索引有效

监控与告警

1. 内置监控视图

pg_locks：查看当前锁情况
pg_stat_activity：查看当前活动会话
pg_stat_statements：查看查询统计信息
pg_stat_database：查看数据库级别的死锁统计

2. Prometheus 告警规则

yaml

# 锁等待告警规则
- alert: PostgreSqlLockWait
  expr: rate(pg_locks_count{mode="waiting"}[5m]) > 10
  for: 1m
  labels:
    severity: warning
  annotations:
    summary: "PostgreSQL 锁等待频繁"
    description: "实例 {{ $labels.instance }} 在过去5分钟内有超过10次锁等待"

- alert: PostgreSqlLongLockWait
  expr: pg_stat_activity_max_duration{state="active"} > 300
  for: 1m
  labels:
    severity: critical
  annotations:
    summary: "PostgreSQL 长时间锁等待"
    description: "实例 {{ $labels.instance }} 存在超过5分钟的锁等待"

- alert: PostgreSqlDeadlock
  expr: pg_stat_database_deadlocks > 0
  for: 1m
  labels:
    severity: critical
  annotations:
    summary: "PostgreSQL 死锁发生"
    description: "实例 {{ $labels.instance }} 检测到死锁"

3. Grafana 监控面板

可以使用 Grafana 创建锁监控面板，显示：

当前锁数量和锁等待数量
锁等待时长分布
死锁数量趋势
锁相关查询的执行情况
阻塞进程数量

版本差异注意事项

版本	差异说明
PostgreSQL 9.x	锁机制相对简单，缺少一些高级特性
PostgreSQL 10+	增强了锁监控功能，引入了 wait_event 字段
PostgreSQL 11+	引入了 SKIP LOCKED 选项，允许跳过已锁定的行
PostgreSQL 12+	改进了死锁检测算法，提高了检测效率
PostgreSQL 13+	增强了 pg_stat_activity 视图，提供了更多锁相关信息
PostgreSQL 14+	改进了锁管理，减少了锁竞争
PostgreSQL 15+	增强了锁监控功能，提供了更多的统计信息

死锁与锁等待最佳实践

1. 开发阶段

使用一致的访问顺序：确保所有事务对表和行的访问顺序一致
尽量缩短事务长度：减少锁持有时间
避免在事务中进行外部操作：防止事务无限期持有锁
使用合适的锁级别和隔离级别：只获取必要的锁
为频繁访问的列创建索引：减少锁竞争

2. 运维阶段

定期监控锁使用情况：实时监控锁等待和死锁情况
设置合理的锁超时和死锁检测频率：根据业务需求调整
优化长查询和慢查询：减少锁持有时间
定期进行数据库维护：VACUUM, ANALYZE 等操作
考虑使用分区表：减少锁竞争范围

3. 应急处理

快速识别阻塞进程：使用 pg_stat_activity 和 pg_locks 视图
合理选择终止进程或等待：根据业务重要性决策
分析死锁日志：找出根本原因
调整应用程序或数据库配置：防止再次发生
记录和复盘：总结经验教训，优化系统

总结

死锁与锁等待是 PostgreSQL 并发控制中的常见问题，需要从多个方面进行处理。通过优化查询和事务、调整数据库设计、配置合适的参数、实现监控和告警，可以有效地减少死锁和锁等待的发生，提高系统的性能和可用性。

在实际运维工作中，应根据具体情况选择合适的解决方案，并不断总结经验，优化系统设计和配置，确保数据库系统的稳定运行。

PostgreSQL 死锁与锁等待 ​

锁机制概述 ​

1. 锁的定义与作用 ​

2. PostgreSQL 锁类型 ​

3. 锁冲突矩阵 ​

识别死锁与锁等待 ​

1. 查看当前锁等待 ​

2. 查看死锁日志 ​

3. 使用 pg_stat_activity 查看长查询 ​

4. 使用 pg_stat_statements 分析锁相关查询 ​

解决死锁与锁等待 ​

1. 紧急处理措施 ​

1.1 终止阻塞进程 ​

1.2 调整锁超时 ​

1.3 手动提交或回滚事务 ​

2. 根本原因分析与解决 ​

2.1 优化查询和事务 ​

2.2 优化数据库设计 ​

2.3 配置优化 ​

预防死锁与锁等待 ​

1. 应用程序层面 ​

2. 数据库层面 ​

3. 架构层面 ​

死锁与锁等待案例分析 ​

案例一：交叉更新导致死锁 ​

问题现象 ​

诊断过程 ​

解决方案 ​

案例二：长时间事务导致锁等待 ​

问题现象 ​

诊断过程 ​

解决方案 ​

案例三：缺少索引导致表级锁 ​

问题现象 ​

诊断过程 ​

解决方案 ​

监控与告警 ​

1. 内置监控视图 ​

2. Prometheus 告警规则 ​

3. Grafana 监控面板 ​

版本差异注意事项 ​

死锁与锁等待最佳实践 ​

1. 开发阶段 ​

2. 运维阶段 ​

3. 应急处理 ​

总结 ​

PostgreSQL 死锁与锁等待

锁机制概述

1. 锁的定义与作用

2. PostgreSQL 锁类型

3. 锁冲突矩阵

识别死锁与锁等待

1. 查看当前锁等待

2. 查看死锁日志

3. 使用 pg_stat_activity 查看长查询

4. 使用 pg_stat_statements 分析锁相关查询

解决死锁与锁等待

1. 紧急处理措施

1.1 终止阻塞进程

1.2 调整锁超时

1.3 手动提交或回滚事务

2. 根本原因分析与解决

2.1 优化查询和事务

2.2 优化数据库设计

2.3 配置优化

预防死锁与锁等待

1. 应用程序层面

2. 数据库层面

3. 架构层面

死锁与锁等待案例分析

案例一：交叉更新导致死锁

问题现象

诊断过程

解决方案

案例二：长时间事务导致锁等待

问题现象

诊断过程

解决方案

案例三：缺少索引导致表级锁

问题现象

诊断过程

解决方案

监控与告警

1. 内置监控视图

2. Prometheus 告警规则

3. Grafana 监控面板

版本差异注意事项

死锁与锁等待最佳实践

1. 开发阶段

2. 运维阶段

3. 应急处理

总结