京公网安备11010502063487号1213 是 MySQL 里最常被说成“重试就好”的错误。这篇用 MySQL 26.10.0 在本机复现:经典的反向加锁、谁会成为牺牲品、不锁同一行也会发生的间隙锁死锁、怎么从日志里看到两条语句、统一加锁顺序对重试的取代,以及关掉死锁检测以后到底发生什么。
事务 A: update t set v = v + 1 where id = 1 -> ok (0 ms) 事务 A: update t set v = v + 10 where id = 2 -> ok (409 ms) 事务 B: update t set v = v + 100 where id = 2 -> ok (0 ms) 事务 B: update t set v = v + 1000 where id = 1 -> OperationalError: (1213, 'Deadlock found when trying to get lock; try restarting transaction') (4 ms) 最终表内容:((1, 1), (2, 10), (10, 0)) (被回滚的事务,它之前的那条更新也没有生效:整个事务回滚,而 1205 只回滚语句)
InnoDB 立刻检测到环,整个事务回滚。
事务 A: update big set v = 1 where id <= 400 -> ok (1 ms) 事务 A: update t set v = 1 where id = 1 -> ok (0 ms) 事务 A: update t set v = 1 where id = 2 -> ok (406 ms) 事务 B: update t set v = 2 where id = 2 -> ok (0 ms) 事务 B: update t set v = 2 where id = 1 -> OperationalError: (1213, 'Deadlock found when trying to get lock; try restarting transaction') (2 ms)
回滚代价小的事务被选中。
事务 A: select * from t where id = 5 for update -> ok (0 ms) 事务 A: insert into t values (5, 1) -> ok (406 ms) 事务 B: select * from t where id = 5 for update -> ok (0 ms) 事务 B: insert into t values (5, 2) -> OperationalError: (1213, 'Deadlock found when trying to get lock; try restarting transaction') (1 ms) 表里 id=5 的行:((5, 1),) 对照:隔离级别改成 READ COMMITTED(没有间隙锁)。同样的语句: 事务 A: select * from t where id = 5 for update -> ok (0 ms) 事务 A: insert into t values (5, 1) -> ok (0 ms) 事务 B: select * from t where id = 5 for update -> ok (0 ms) 事务 B: insert into t values (5, 2) -> IntegrityError: (1062, "Duplicate entry '5' for key 't.PRIMARY'") (0 ms) 表里 id=5 的行:((5, 1),)(不是死锁,而是后插入的撞唯一键 1062)
RR 下先 SELECT FOR UPDATE 再 INSERT 会死锁,RC 下变成 1062。
SHOW ENGINE INNODB STATUS 里的关键行:
*** (1) TRANSACTION:
TRANSACTION 1590, ACTIVE 1 sec starting index read
update t set v = v + 10 where id = 2
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `d`.`t` trx id 1590 lock_mode X locks rec but not gap
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `d`.`t` trx id 1590 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 1591, ACTIVE 1 sec starting index read
update t set v = v + 1000 where id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `d`.`t` trx id 1591 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `d`.`t` trx id 1591 lock_mode X locks rec but not gap waiting
错误日志里(innodb_print_all_deadlocks=ON)与死锁相关的行 24 行,例如:
[Note] [MY-012468] [InnoDB] Transactions deadlock detected, dumping detailed information.
[Note] [MY-012469] [InnoDB] *** (1) HOLDS THE LOCK(S):
[Note] [MY-012469] [InnoDB] *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
[Note] [MY-012469] [InnoDB] *** (2) HOLDS THE LOCK(S):
对照:另一个实例同样开着 innodb_print_all_deadlocks=ON,但 log_error_verbosity 保持默认(@@log_error_verbosity = 2),制造同样的死锁后,错误日志里与死锁相关的行有 0 行INNODB STATUS 只留最近一次;完整记录需要 innodb_print_all_deadlocks 加 log_error_verbosity=3。
随机顺序,不重试:共 1600 个事务,成功提交 566,1213 出现 1034 次,重试 0 次,sum(v) = 2830(每个成功事务加 5),用时 1.54 s 随机顺序,遇到 1213 重试整个事务:共 1600 个事务,成功提交 1600,1213 出现 2898 次,重试 2898 次,sum(v) = 8000(每个成功事务加 5),用时 3.80 s 按 id 升序更新,不重试:共 1600 个事务,成功提交 1600,1213 出现 0 次,重试 0 次,sum(v) = 8000(每个成功事务加 5),用时 0.38 s
统一加锁顺序比重试更有效,再为偶发的 1213 写重试。
事务 A: update t set v = v + 1 where id = 1 -> ok (0 ms) 事务 A: update t set v = v + 10 where id = 2 -> OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction') (3418 ms) 事务 B: update t set v = v + 100 where id = 2 -> ok (0 ms) 事务 B: update t set v = v + 1000 where id = 1 -> OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction') (3014 ms)
innodb_deadlock_detect=OFF 后要等 lock_wait_timeout,两边都失败。