浅析一个postgresql的死锁问题

之前写过一篇文章（浅析一个sql server数据库事务死锁问题），简单分析了一个sql server数据库的死锁问题及索引对执行计划的影响；这里继续分享一个postgresql的死锁问题。

一般来说，数据库死锁问题都是由于两个或多个复杂事务产生了对锁的循环依赖造成的。最简单的例子就是，事务1执行两个update语句：

UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;

事务2以相反的顺序执行同样的update语句：

UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;

在高并发的情况下，就容易产生死锁。

其实不光是update+update语句会产生死锁；正如之前文章中提到的例子，insert+select语句也会产生死锁，原因是由于索引的创建方式不一样，导致锁的粒度不一样。而这里要分享的死锁问题是两个insert语句产生的。

问题原型

同样的，抽象问题的原型如下：

有一张学生表，表结构如下（仅有四个字段加id主键约束）：

CREATE TABLE public.student (
id int4 NOT NULL,
address varchar(255) NULL,
email varchar(255) NULL,
"name" varchar(255) NULL,
CONSTRAINT student_pkey PRIMARY KEY (id)
);

接下来有两个事务执行批量插入操作，事务1先插入student3，再插入student4，如下：

begin transaction
INSERT INTO public.student(ID, NAME, ADDRESS, EMAIL) VALUES('3','Jack','Dallas, TX','jack@gmail.com');
INSERT INTO public.student(ID, NAME, ADDRESS, EMAIL) VALUES('4','Jack','Dallas, TX','jack@gmail.com');
commit

而事务2先插入student4，再插入student3，如下：

begin transaction
INSERT INTO public.student(ID, NAME, ADDRESS, EMAIL) VALUES('4','Jack','Dallas, TX','jack@gmail.com');
INSERT INTO public.student(ID, NAME, ADDRESS, EMAIL) VALUES('3','Jack','Dallas, TX','jack@gmail.com');
commit

在两个事务并行执行的场景下，有时候会产生死锁问题，具体的错误如下：

SQL Error [40P01]: ERROR: deadlock detected
Detail: Process 55 waits for ShareLock on transaction 569; blocked by process 57.
Process 57 waits for ShareLock on transaction 568; blocked by process 55.
Hint: See server log for query details.
Where: while inserting index tuple (0,14) in relation "student_pkey"

问题原因

后面发现，当上面两个事务（事务1和事务2）交叉并行执行时，就会发生死锁。具体流程是：

事务1先执行完第一条insert语句；接着事务2开始执行，事务2第一条insert语句执行没有问题，执行第二条insert语句时会pending住；这时，事务1继续执行第二条inset语句，死锁就发生了。

那么，为什么事务2执行第二条insert语句会pending住呢？

原因是，事务1先插入了student3，事务2先插入了student4，它们都没有commit，但是由于id上有一个index，index存储会把没有commit的数据也存起来；当事务2执行第二条insert语句时，发现student3在index里面已经有了，于是就等待事务1结束；这时事务1继续执行第二条insert语句，同样的，发现student4在index里面也有了，于是就等待事务2结束；在这种情况下，死锁就产生了。具体可参考下面解释：

A Postgres index stores not only committed data, but also stores data written by ongoing transactions. Postgres will look for the tuple we’re attempting to insert in both the committed and “dirty” (not-yet-committed) sections of the index. https://rcoh.svbtle.com/postgres-unique-constraints-can-cause-deadlock

通过查看数据库lock情况也可以看出，当事务2执行第二条insert语句时pending在获取一个sharelock上。

（事务1和事务2都执行完第一条语句时的lock情况）

（事务2执行完第二条语句时的lock情况）

延申

1. 由于JDBC事务是基于连接实现，如果用DBeaver测试上面死锁问题，你可能需要设置成打开一个editor tab就打开一个新的连接，如下：

1. postgresql里面死锁的发生可能跟下面几个timeout参数的设置都有关系，请注意： deadlock_timeout lock_timeout statement_timeout