postgresql表中间加列_PostgreSQL死锁案例分析__在最近的生产环境巡检中，发现一个死锁错误。从日志中看，触发死锁的是对表的相同行操作，最终分析和业务操作有关，不过其中涉及到Postg...

### **作者介绍**

#### 陈雁飞，开源PostgreSQL爱好者，一直从事PostgreSQL数据库运维工作

### **问题现象**

#### 在最近的生产环境巡检中，发现一个死锁错误。从日志中看，触发死锁的是对表的相同行操作，最终分析和业务操作有关，不过其中涉及到Postgres数据库的外键更新加锁处理逻辑，下面对这个问题展开详细分析。

#### 数据库日志中记录的死锁日志信息如下

```

2019-08-24 21:18:35.153 HKT [11832] ERROR: deadlock detected

2019-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逻辑如下：


#### 这里整理的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操作提到事务开始地方，这样事务中间就变成串行操作，不会触发死锁，本案例中修改对性能影响也不大。