PostgreSQL 死锁问题

本文分析在 PostgreSQL 发生的一例死锁问题

执行SQL 序列

1. 表结构和数据

    create table a(id int, value int);
    insert into a values(1,1);
    insert into a values(2,2);

1. 事物隔离级别：读已提交
2. 死锁发生的序列

#会话 1 开启一个事物
begin;

#会话 1 修改一行数据。修改成功,当前事物并未提交
update a set value = 3 where id = 1;

#会话 2 开启一个事物
begin;

#会话 2 修改一行数据。修改成功,当前事物并未提交
update a set value = 4 where id = 2;

# 会话 2 修改第二行数据
update a set value = 5 where id = 1;
# 由于这一行数据被会话 1 修改，且会话 1 所在事物状态未知
# 会话 2 挂起，等待会话 1 所在事物提交或回滚
# PostgreSQL 基于 MVCC 机制
# 如果会话 1 回滚，则该语句会在老数据（1）上修改；
# 如果会话 1 提交，则该语句会在新数据（3）上修改。

# 会话 1 修改第二行数据
update a set value = 6 where id = 2;
# 由于这一行数据被会话 2 修改，且会话 2 所在事物没有提交
# 会话 2 所在事物在等会话 1 的状态的确定，发生死锁
# PostgreSQL 死锁检测检测到死锁，介入处理，会话 1 所在事物被强制回滚
ERROR:  deadlock detected
DETAIL:  Process 24284 waits for ShareLock on transaction 2001; blocked by process 22401.
Process 22401 waits for ShareLock on transaction 2000; blocked by process 24284.
HINT:  See server log for query details.

# 此时会话 2 检测到会话 1 所在事物已回滚，则数据修改找到老版本的数据修改，update 语句执行完成。
postgres=# update a set value = 5 where id = 1;
UPDATE 1

问题分析

该问题是典型的 PostgreSQL 死锁问题

1. 问题的原因是数据库会话间对数据库对象的循环上锁在成的
2. PostgreSQL 的自动死锁检测机制能发现这类循环锁定，解决方法是一旦发生可能的死锁，就强制回滚可能造成死锁的会话的事物
3. 用户在开发应用的过程中需要在特别注意上锁的顺序，否则在并发修改数据时容易造成死锁，导致性能低下
4. 上述用例，如果所有回话加锁的顺序都是从小的 ID 到大的 ID，则不会发生死锁
5. 同时，也可以使用意向锁来提前锁定需要修改的数据

相关资料

1. Deadlocks

1. select for update