PostgreSQL执行UPDATE报锁错误导致更新未完成的原因排查

核心结论 #

PostgreSQL确实会因为锁的死锁问题，导致更新操作完全无法执行完成、所有修改全部不生效。
你之前了解的"锁冲突时数据库会等待锁释放后继续执行"，仅适用于单向锁等待场景：即事务A等待事务B持有的锁，事务B本身没有等待A持有的任何锁，这种场景下等B事务提交/回滚释放锁后，A就能继续执行。
如果出现循环锁等待也就是死锁，两个及以上事务会永久互相阻塞，PostgreSQL内置的死锁检测机制触发后，会自动选择其中一个事务终止、抛出死锁报错，被终止事务的所有修改会被完整回滚，不会留下任何部分生效的修改，这就是你查询数据发现大量jobNumber > 9的记录未被更新的直接原因。

场景触发逻辑 #

你遇到的是最典型的跨事务行级死锁，从报错日志可以明确看到循环等待链：

1. 进程7444已经持有部分行的排他行锁，正在等待进程10304持有的事务ShareLock
2. 进程10304已经持有7444需要的那部分锁，反过来正在等待7444持有的事务ShareLock
   两个进程互不释放自己持有的锁，又都要等对方的锁才能继续，形成完全卡死的循环。
   这个死锁的直接诱因是你在两个独立的数据库连接（对应日志里的7444、10304两个进程）中并发执行了两个范围有衔接的批量UPDATE：

• 第一条语句更新jobNumber在9到12之间的记录
• 第二条语句更新jobNumber大于等于12的记录
  两个语句没有强制统一的加锁顺序，执行时一个连接先锁定了低区间的行、再去升高区间的行锁，另一个连接刚好先锁定了高区间的行、再去低区间加行锁，刚好撞上形成循环等待。报错上下文里两个进程分别在修改不同ctid的元组，也完全符合这个加锁顺序颠倒的特征。
  你调整过的数据库参数只要没有关闭默认开启的死锁检测、修改默认的事务隔离级别，就不会改变这个锁处理逻辑，死锁和参数调整没有关联。

解决方法 #

• 批量更新操作尽量放在单事务中串行执行，不要在多个并发连接中同时跑范围重叠的批量更新，从根源上避免跨事务锁竞争
• 如果必须并发执行批量更新，所有更新语句要严格遵循相同的加锁顺序，比如更新时强制按主键排序后逐行加锁，保证所有事务永远先锁主键值小的行、再锁主键值大的行，不会形成循环等待
• 死锁报错属于事务级的可重试错误，PostgreSQL的事务原子性保证只要抛出错误，事务内所有修改都会被完整回滚，不会出现部分数据更新、部分没更新的中间状态，直接重试被终止的更新语句即可

你执行的SQL和对应报错信息如下：

UPDATE xml_files SET "jobNumber" = 1 WHERE "jobNumber" > 9 AND "jobNumber" < 12;
psql:D:/dump/job_numbers.sql:1: ERROR: interlock detected
ERROR: Process 7444 is waiting in ShareLock mode for "transaction 17499718"; locked by process 10304.
Process 10304 is waiting in ShareLock for lock "transaction 16365708"; locked by process 7444.
TIP: See server protocol for request details.
CONTEXT: When changing tuple (147470,18) with respect to "xml_files"

UPDATE xml_files SET "jobNumber" = 8 WHERE "jobNumber" >= 12;
psql:D:/dump/job_numbers.sql:3: ERROR: interlock detected
ERROR: Process 7444 is waiting in ShareLock mode for "transaction 17697349"; blocked by process 10304.
Process 10304 is waiting in ShareLock for lock "transaction 17535413"; locked by process 7444.
TIP: See server protocol for request details.
CONTEXT: When the tuple (40940,13) is changed with respect to "xml_files"

内容的提问来源于stack exchange，提问作者Dmitry Bubnenkov