PostgreSQL死锁案例分析
- 2019 年 11 月 21 日
- 筆記
作者介绍
陈雁飞，开源PostgreSQL爱好者，一直从事PostgreSQL数据库运维工作
问题现象
在最近的生产环境巡检中，发现一个死锁错误。从日志中看，触发死锁的是对表的相同行操作，最终分析和业务操作有关，不过其中涉及到Postgres数据库的外键更新加锁处理逻辑，下面对这个问题展开详细分析。
数据库日志中记录的死锁日志信息如下
|
2019-08-24 21:18:35.153 HKT [11832] ERROR: deadlock detected2019-08-24 21:18:35.153 HKT [11832] DETAIL: Process 11832 waits for ShareLock on transaction 588; blocked by process 1672.Process 1672 waits for ShareLock on transaction 589; blocked by process 11832.Process 11832: select * from test2 where a = 1 for update;Process 1672: update test2 set d = 10 where a = 1;2019-08-24 21:18:35.153 HKT [11832] HINT: See server log for query details.2019-08-24 21:18:35.153 HKT [11832] CONTEXT: while locking tuple (0,1) in relation "test2"2019-08-24 21:18:35.153 HKT [11832] STATEMENT: select * from test2 where a = 1 for update; |
|---|
检查表结构，a列是test2表的主键，，因此理论上对该行的UPDATE和SELECT .. FOR UDPATE操作不会出现死锁，出现死锁必然和业务场景有关。
流程梳理
分析死锁问题必然要和实际业务操作结合起来，这个问题分析也不例外。
首先从数据库日志中可以看到，死锁的时候等待的是事务中的ShareLock锁，因此和事务操作相关，索引需要获取业务的完整事务操作，可以通过设置数据库的配置参数log_min_duration_statement=0，采集业务所有操作语句，然后分析相关事务操作中的涉及锁相关的语句和表。
经分析，事务操作涉及两张表，简化后的表结构以及操作逻辑如下：
|
create table test1(a int primary key, b int);create table test2(a int primary key,b int references test1(a), c int references test1(a), d int);insert into test1 values(1,1);insert into test1 values(2,1);insert into test2 values(1,1,2,5); |
|---|
表TEST1
|
|
|---|
表TEST2
|
|
|---|
从表结构上可以看到，表test2和test1构成外键约束关系，并且是表test2中一行中两列分别对应表test1中两行。分析业务操作发现，业务每次操作的时候需要操作test2中的同一行的两列，整理得到的执行SQL逻辑如下：
|
时间 |
事务1 |
事务2 |
|---|---|---|
|
T1 |
Begin; |
Begin; |
|
T2 |
select * from test1 where a = 1 for update; |
select * from test1 where a = 2 for update; |
|
T3 |
select * from test2 where a = 1 for update; |
|
|
T4 |
update test2 set d = 10 where a = 1; |
|
|
T5 |
update test2 set d = 20 where a = 1; |
|
|
T6 |
|
select * from test2 where a = 1 for update; |
这里整理的SQL是分析后的模拟流程，实际流程中每个事务还有对其他表的查询、插入等实际业务操作，这里为了分析问题不再详细整理出来。
手工按照上面的流程执行操作，发现事2执行到T6时刻，必然会触发死锁，并且如果事务1不执行T5语句（不对同一行更新两次），那么就不会触发死锁。
原因分析
死锁必然是两个或者多个事务之间相互持有锁导致的，而此时事务2仅仅持有TEST1表中A=2的行锁，然后请求TEST2表中A=1的行锁，而事务1持有TEST2表中A=1的行锁，因此事务1请求TEST1表中A=2的行锁。根据主外键的知识，更新TEST2的时候会请求TEST1中对应行的锁信息，从而导致死锁的发生。
细心地读者会发现， T4和T5的更新并没有修改列B或列C的值，且T4时刻的更新可以正常执行，难道是T4时更新不会请求T1上的锁，到T5时更新就请求锁了？
在PostgreSQL中确实是这样，我们知道Postgres的外键是通过触发器实现一致性参考的，更新时候代码逻辑如下：
函数AfterTriggerSaveEvent中
|
|
|---|
加入触发列表之前调用RI_FKey_fk_upd_check_required函数检查，该函数部分实现如下
|
|
|---|
首先获取元组对应的xmin，判断是否是当前事务产生的，如果是属于当前事务新插入的元组，表示需要触发检查，如果不是当前事务插入的数据，且关联键值没有发生变化，就不需要触发检查。T4时刻的更新属于后面一种情况，不会触发触发检查，但是T5时刻更新属于前一种情况，需要请求表TEST1中的两行锁信息，其中一行正好被事务2锁持有，从而导致死锁的发生。
总结
死锁问题的解决需要结合业务实际操作，针对该问题，建议业务将T3/T6操作提到事务开始地方，这样事务中间就变成串行操作，不会触发死锁，本案例中修改对性能影响也不大。
