PostgreSQL作为一款功能强大的关系型数据库，其并发控制机制依赖于精细的锁系统。然而，在高并发场景下，锁等待和死锁问题常常成为性能瓶颈的元凶。本文将深入探讨PostgreSQL锁机制的原理，并通过实战案例，手把手教你如何精准定位和解决锁相关问题。
一、PostgreSQL锁机制基础
在深入排查之前，我们需要先了解PostgreSQL中锁的基本概念和类型。只有理解了锁的工作原理，才能更好地分析和解决问题。
1.1 锁的分类
PostgreSQL中的锁主要分为以下几类：
表级锁（Table-level Locks）
：作用于整个表，常见的有ACCESS SHARE、ROW EXCLUSIVE等。
行级锁（Row-level Locks）
：作用于特定的行，如FOR UPDATE、FOR SHARE等。
咨询锁（Advisory Locks）
：由应用程序显式获取和释放，用于实现业务逻辑的并发控制。
死锁检测（Deadlock Detection）
：PostgreSQL会定期检测死锁并自动回滚其中一个事务。
1.2 锁的兼容性
不同的锁模式之间存在兼容性关系。例如，ACCESS SHARE锁与ACCESS EXCLUSIVE锁是互斥的，而ACCESS SHARE锁之间是兼容的。理解锁的兼容性有助于分析锁等待的原因。
1.3 锁等待与死锁
锁等待
：当一个事务需要获取的锁被其他事务持有时，该事务会进入等待状态，直到锁被释放。
死锁
：两个或多个事务相互等待对方持有的锁，形成循环依赖，导致都无法继续执行。
二、锁等待排查工具与视图
PostgreSQL提供了丰富的系统视图和工具，帮助我们监控和分析锁相关问题。
2.1 关键系统视图
pg_locks
：存储当前数据库中所有锁的信息，包括锁的类型、持有者、等待者等。
pg_stat_activity
：显示当前所有活动连接的状态，包括正在执行的SQL、等待的锁等。
pg_class
：存储表、索引等对象的元数据，结合pg_locks可以定位锁的具体对象。
2.2 常用查询脚本
2.2.1 查看当前锁等待情况
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;
2.2.2 查看死锁详情
当发生死锁时，PostgreSQL会自动检测并记录死锁信息到日志中。可以通过以下方式查看：
# 在postgresql.conf中设置日志级别
log_lock_waits = on
deadlock_timeout = 1s
# 查看日志
tail -f /var/log/postgresql/postgresql-*.log | grep “deadlock”
2.2.3 查看表级锁等待
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,
blocked_activity.wait_event_type AS blocked_wait_event_type,
blocked_activity.wait_event AS blocked_wait_event
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
JOIN
pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE
NOT blocked_locks.granted
AND blocked_locks.locktype = ‘relation’;
三、实战案例分析
接下来，我们通过几个实际案例来演示如何排查和解决锁问题。
3.1 案例一：长时间运行的查询导致锁等待
3.1.1 问题描述
用户反馈某个查询执行时间过长，导致其他事务无法获取锁，系统响应缓慢。
3.1.2 排查过程
查看当前活动连接
SELECT
pid,
usename,
application_name,
client_addr,
state,
query_start,
now() – query_start AS duration,
query
FROM
pg_stat_activity
WHERE
state = ‘active’
ORDER BY
duration DESC;
发现一个长时间运行的UPDATE语句
— 示例输出
pid | usename | application_name | client_addr | state | query_start | duration | query
——+———+——————+————-+——–+—————————-+————–+—————————————–
1234 | app_user| psql | 192.168.1.10| active | 2024-01-15 10:30:00.123456 | 00:15:30.123 | UPDATE large_table SET col1 = ‘value’ WHERE id BETWEEN 1 AND 1000000;
检查该事务持有的锁
SELECT
locktype,
relation::regclass,
mode,
granted
FROM
pg_locks
WHERE
pid = 1234;
发现该事务持有ACCESS EXCLUSIVE锁，阻塞了其他事务
3.1.3 解决方案
终止长时间运行的事务
SELECT pg_terminate_backend(1234);
优化查询
将大事务拆分为小事务
添加合适的索引
使用批处理
— 优化后的批处理示例
DO $$
DECLARE
batch_size INT := 10000;
start_id INT := 1;
end_id INT;
BEGIN
SELECT MAX(id) INTO end_id FROM large_table;
WHILE start_id <= end_id LOOP
UPDATE large_table
SET col1 = 'value'
WHERE id BETWEEN start_id AND start_id + batch_size - 1;
COMMIT; -- 每批提交一次
start_id := start_id + batch_size;
END LOOP;
END $$;
3.2 案例二：死锁问题
3.2.1 问题描述
系统日志中频繁出现死锁错误，导致部分事务被回滚。
3.2.2 排查过程
查看死锁日志
grep "deadlock" /var/log/postgresql/postgresql-2024-01-15.log
分析死锁日志
日志中会显示死锁涉及的事务和SQL语句，例如：
2024-01-15 14:30:00.123 UTC [1234] ERROR: deadlock detected
2024-01-15 14:30:00.123 UTC [1234] DETAIL: Process 1234 waits for RowExclusiveLock on relation 16385 of database 12345; blocked by process 1235.
Process 1235 waits for RowExclusiveLock on relation 16386 of database 12345; blocked by process 1234.
2024-01-15 14:30:00.123 UTC [1234] HINT: See server log for query details.
定位涉及的表
SELECT
relation::regclass
FROM
pg_class
WHERE
oid IN (16385, 16386);
3.2.3 解决方案
统一事务顺序
确保所有事务以相同的顺序访问资源：
-- 事务1
BEGIN;
UPDATE table_a SET ... WHERE id = 1;
UPDATE table_b SET ... WHERE id = 1;
COMMIT;
-- 事务2
BEGIN;
UPDATE table_a SET ... WHERE id = 1;
UPDATE table_b SET ... WHERE id = 1;
COMMIT;
使用SELECT FOR UPDATE NOWAIT
BEGIN;
SELECT * FROM table_a WHERE id = 1 FOR UPDATE NOWAIT;
SELECT * FROM table_b WHERE id = 1 FOR UPDATE NOWAIT;
UPDATE table_a SET ... WHERE id = 1;
UPDATE table_b SET ... WHERE id = 1;
COMMIT;
3.3 案例三：热点行更新导致的性能瓶颈
3.3.1 问题描述
高并发场景下，对同一行数据的频繁更新导致大量锁等待。
3.3.2 排查过程
查看锁等待情况
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
JOIN
pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE
NOT blocked_locks.granted
AND blocked_locks.locktype = 'tuple';
发现大量事务在等待同一行
3.3.3 解决方案
使用乐观锁
-- 添加版本号字段
ALTER TABLE counter ADD COLUMN version INT DEFAULT 0;
-- 更新时检查版本号
UPDATE counter
SET value = value + 1, version = version + 1
WHERE id = 1 AND version = old_version;
-- 检查更新行数
IF NOT FOUND THEN
RAISE EXCEPTION 'Version mismatch, please retry';
END IF;
使用队列机制
# Python示例：使用Redis作为队列
import redis
import psycopg2
r = redis.Redis()
conn = psycopg2.connect()
def update_counter():
# 获取分布式锁
lock_key = "counter_lock_1"
if r.set(lock_key, "locked", nx=True, ex=10):
try:
cur = conn.cursor()
cur.execute("UPDATE counter SET value = value + 1 WHERE id = 1")
conn.commit()
finally:
r.delete(lock_key)
else:
# 稍后重试
time.sleep(0.1)
update_counter()
四、预防与优化策略
4.1 设计阶段
避免长事务
将大事务拆分为小事务
避免在事务中执行耗时操作（如网络请求）
统一资源访问顺序
在所有事务中以相同的顺序访问表和行
合理使用索引
确保WHERE条件和JOIN字段有索引
避免全表扫描
4.2 开发阶段
使用合适的锁模式
只读查询使用ACCESS SHARE
更新操作使用ROW EXCLUSIVE
避免不必要的ACCESS EXCLUSIVE
设置合理的锁超时
-- 设置事务锁超时（秒）
SET lock_timeout = 10;
-- 设置会话锁超时
SET SESSION lock_timeout = 10;
使用NOWAIT选项
SELECT * FROM table WHERE id = 1 FOR UPDATE NOWAIT;
4.3 监控阶段
启用锁监控
-- postgresql.conf
log_lock_waits = on
deadlock_timeout = 1s
log_min_duration_statement = 1000 -- 记录慢查询
定期检查锁统计
-- 查看锁等待统计
SELECT
locktype,
mode,
COUNT(*) AS count,
SUM(granted::int) AS granted_count,
SUM((NOT granted)::int) AS wait_count
FROM
pg_locks
GROUP BY
locktype, mode;
使用监控工具
pg_stat_statements：监控SQL性能
pgBadger：分析日志
Prometheus + Grafana：实时监控
五、高级技巧与最佳实践
5.1 使用咨询锁实现业务逻辑
咨询锁（Advisory Locks）是PostgreSQL提供的一种特殊锁机制，可以用于实现业务逻辑的并发控制。
-- 获取咨询锁（阻塞等待）
SELECT pg_advisory_lock(123);
-- 获取咨询锁（非阻塞）
SELECT pg_try_advisory_lock(123);
-- 释放咨询锁
SELECT pg_advisory_unlock(123);
-- 示例：防止重复处理
BEGIN;
SELECT pg_advisory_lock(10001); -- 锁定任务ID 10001
-- 处理任务
UPDATE tasks SET status = 'processing' WHERE id = 10001;
-- 处理完成后释放锁
SELECT pg_advisory_unlock(10001);
COMMIT;
5.2 使用分区表减少锁竞争
-- 创建分区表
CREATE TABLE sales (
id SERIAL,
sale_date DATE,
amount NUMERIC
) PARTITION BY RANGE (sale_date);
-- 创建分区
CREATE TABLE sales_2024_01 PARTITION OF sales
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
-- 插入数据会自动路由到对应分区
INSERT INTO sales (sale_date, amount) VALUES ('2024-01-15', 100.00);
5.3 使用MVCC避免读写冲突
PostgreSQL的MVCC机制可以避免读写冲突，但需要注意：
避免长时间运行的只读查询
使用合适的隔离级别
及时清理旧版本（autovacuum）
监控事务ID回卷
-- 查看事务ID回卷风险
SELECT
age(relfrozenxid) AS xid_age,
pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM
pg_class c
WHERE
relkind = 'r'
ORDER BY
xid_age DESC;
六、总结
PostgreSQL的锁机制是数据库并发控制的核心，理解锁的工作原理和排查方法对于解决性能问题至关重要。通过本文的实战案例，我们学习了：
锁的基础知识
：锁的类型、兼容性、等待与死锁
排查工具
：pg_locks、pg_stat_activity等系统视图的使用
实战案例
：长事务、死锁、热点行更新的排查与解决
预防策略
：设计、开发、监控阶段的最佳实践
高级技巧
：咨询锁、分区表、MVCC的应用
在实际工作中，建议建立定期的锁监控机制，及时发现和解决潜在问题。同时，良好的数据库设计和编码规范是预防锁问题的根本。
记住：
锁问题往往不是孤立的，而是系统设计、业务逻辑和数据库配置共同作用的结果
。只有综合考虑各个方面，才能从根本上解决锁相关的问题。