京公网安备11010502063487号“ERROR: deadlock detected” 在 PostgreSQL 里很常见,错误信息里只有进程号和事务号。这篇在本机自己起一个 PostgreSQL 16,复现反序更新引起的死锁,看谁被回滚、检测要等多久、服务端日志里多出什么信息,以及统一加锁顺序到底换来了什么。
会话 A(退出码 1),成功的 UPDATE 1 条:
ERROR: deadlock detected
DETAIL: Process <pid> waits for ShareLock on transaction <xid>; blocked by process <pid>.
Process <pid> waits for ShareLock on transaction <xid>; blocked by process <pid>.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,2) in relation "accounts"
会话 B(退出码 0),成功的 UPDATE 2 条:
两个会话一共用了 1.53 s;最后的余额:1|120 ; 2|80DETAIL 里是两个进程互相等对方的事务;CONTEXT 告诉你是在更新哪个表的哪个元组。
6 次的被回滚会话和总耗时(s):[('A', 1.53), ('A', 1.53), ('A', 1.52), ('A', 1.53), ('A', 1.52), ('A', 1.52)]
把启动顺序换过来(B 比 A 早 0.05 s 开始):被回滚的是 ['B']
被回滚的会话 A 整个事务撤销,B 的两条 UPDATE 都生效。最后余额:1|120 ; 2|80(A 的两条 UPDATE 都没有生效)
事务里出错之后,同一个事务里后续语句会怎样(举 1/0 为例,死锁之后同理):
ROLLBACK
ERROR: division by zero
ERROR: current transaction is aborted, commands ignored until end of transaction block
COMMIT 之后 id=1 的余额:100(整个事务回滚了,UPDATE 没有生效)先等满 deadlock_timeout 的会话来检查并中止自己;被回滚的事务整个撤销。
deadlock_timeout = 100ms :两个会话总共用了 0.63 s,被回滚的是 ['A'] deadlock_timeout = 1s :两个会话总共用了 1.53 s,被回滚的是 ['A'] deadlock_timeout = 3s :两个会话总共用了 3.53 s,被回滚的是 ['A']
等待时间约等于 deadlock_timeout。
pg.log: [<pid>] LOG: process <pid> detected deadlock while waiting for ShareLock on transaction <xid> after 1000.730 ms
pg.log: [<pid>] DETAIL: Process holding the lock: <pid>. Wait queue: .
pg.log: [<pid>] CONTEXT: while updating tuple (0,64) in relation "accounts"
pg.log: [<pid>] STATEMENT: update accounts set bal = bal - 10 where id = 1; select pg_sleep(0.5); update accounts set bal = bal + 10 where id = 2;
pg.log: [<pid>] ERROR: deadlock detected
pg.log: [<pid>] DETAIL: Process <pid> waits for ShareLock on transaction <xid>; blocked by process <pid>.
pg.log: Process <pid> waits for ShareLock on transaction <xid>; blocked by process <pid>.
pg.log: Process <pid>: update accounts set bal = bal - 10 where id = 1; select pg_sleep(0.5); update accounts set bal = bal + 10 where id = 2;
pg.log: Process <pid>: update accounts set bal = bal - 20 where id = 2; select pg_sleep(0.5); update accounts set bal = bal + 20 where id = 1;
pg.log: [<pid>] HINT: See server log for query details.
pg.log: [<pid>] CONTEXT: while updating tuple (0,64) in relation "accounts"
pg.log: [<pid>] STATEMENT: update accounts set bal = bal - 10 where id = 1; select pg_sleep(0.5); update accounts set bal = bal + 10 where id = 2;服务端日志里有两个会话各自的语句,客户端看不到。
A 按 1→2、B 按 2→1,20 个事务里 19 个报 deadlock detected(用时 2.3 s)
全部按 1→2,20 个事务里 0 个报 deadlock detected(用时 6.1 s)
应用靠 SQLSTATE 识别并重试死锁(VERBOSITY=verbose 时 psql 显示它):
ERROR: 40P01: deadlock detected统一加锁顺序,代价是并发事务排队;应用要能按 40P01 重试。