PostgreSQL死锁案例分析(二）
原作者：陈雁飞 创作时间：2019-09-26 15:28:14+08 采编：wangliyun
发布时间：2019-09-27 08:08:14
欢迎大家踊跃投稿，投稿信箱：[email protected]
评论：1 浏览：4684
作者介绍
陈雁飞，开源PostgreSQL爱好者，一直从事PostgreSQL数据库运维工作
问题现象
接前一篇文章，这里继续介绍在工作中遇到的一个死锁案例。经过对业务模型的抽取分析（后面会介绍表结构和数据，业务模型来源于开源组件的实际业务），模拟得到的死锁日志信息如下：
2019-09-01 21:01:08.359 CST [1482] ERROR: deadlock detected
2019-09-01 21:01:08.359 CST [1482] DETAIL: Process 1482 waits for ShareLock on transaction 523; blocked by process 1610.
Process 1610 waits for ShareLock on transaction 524; blocked by process 1482.
Process 1482: select test2.a,test2.b,test2.c from test2 join test1 on test2.a = test1.a where test2.b = 2 and test1.c = 3 for update;
Process 1610: delete from test1 where a = 1;
2019-09-01 21:01:08.359 CST [1482] HINT: See server log for query details.
2019-09-01 21:01:08.359 CST [1482] CONTEXT: while locking tuple (0,1) in relation "test1"
2019-09-01 21:01:08.359 CST [1482] STATEMENT: select test2.a,test2.b,test2.c from test2 join test1 on test2.a = test1.a where test2.b = 2 and test1.c = 3 for update;
从数据库日志上看，记录的SQL语句涉及两张表TEST1和TEST2，其中一个事务执行的SQL是SEELCT … FOR UPDATE用于获取行级锁操作。
流程梳理
经分析，事务操作涉及两张表，简化后的表结构以及操作逻辑如下：
create table test1(a int primary key, b int, c int);
create table test2(a int references test1 on delete cascade,b int, c int);
insert into test1 values(1,2,3),(2,3,4),(3,4,5);
insert into test2 values(1,2,3),(2,3,4),(3,4,5);
表TEST1
表TEST2
从表结构上可以看到，表test2和test1构成外键约束关系，并且是级联删除的关系，导致在删除TEST1表中的时候，数据库会自动请求对TEST2表中对应行的删除操作。根据业务操作模型，整理得到的执行SQL逻辑如下（这里仅仅列举出事务中涉及锁相关的操作，其他查询操作未列举出）：
从整理的SQL操作上看，事务一仅仅涉及到对TEST1表的操作，但是由于存在外键级联删除的关系，在delete语句的执行的时候，会请求TEST2表相应的行进行删除。事务二主要是一个SELECT .. FOR UPDATE操作，但是查询语句中涉及两表join关联，且最后锁定的行和事务一中请求的TEST2表相同。因此，可以推测事务二执行的时候，依次涉及对TEST2表和TEST1表的加锁操作。
由于是涉及到行级锁的操作，需要借助gdb工具进行调试，控制事务2获取锁的逻辑顺序。首先，根据执行计划信息，找到加载的行级锁的函数。
在数据库中，SQL的执行按照生成的执行计划完成的，该执行计划中最顶层算子是LockRows，对应到执行器中的函数是ExecLockRows，结合代码，对行级元素加锁的操作如下：
test = table_tuple_lock(erm->relation, &tid, estate->es_snapshot,
markSlot, estate->es_output_cid,
lockmode, erm->waitPolicy,
lockflags,
&tmfd);
因此，使用gdb调试工具在该处函数加上断点，erm记录请求行级锁对应的表信息。操作结果如下：
可以看到对应表OID为16389，查询数据库该对应的表为TEST2，表明此时事务二已经获取到TEST2对应结果的行级锁信息，此时继续执行事务一中的删除操作，该事务将被阻塞。
调试工具中继续执行事务二操作之后，出现前文中描述的死锁信息，如下：
表明事务二中执行的语句先获取TEST2的行锁，然后获取TEST1的行锁，与事务一种的操作获取锁顺序正好相反，由于操作的是相同行，从而导致了死锁发生。
进一步分析
这里查询TEST2对应的语句返回结果只有一条记录，且不需要获取TEST1表中的结果，在手册上对FOR UPDATE锁的解释是“FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. ”，表示对查询语句检索的行请求锁，这里没有请求TEST1表的数据，为什么也请求该表上的行级锁呢？从直观感觉上看是没有必要的。
其实这个和数据库的执行器获取数据逻辑有关，处理函数为ExecutePlan，在该函数中循环处理每颗Plan并获取对应的所有元组，然后根据前面保存的junk filter信息（ExecFilterJunk函数），获取需要的目标元组。这样，在执行的时候，需要检索Plan中所有表中满足条件的行。
