京公网安备11010502063487号看到 “Lock wait timeout exceeded; try restarting transaction” 时,调大 innodb_lock_wait_timeout 往往没用:真正的问题是谁在堵、堵了多久、超时之后到底回滚了什么。这篇用本机的 MySQL 把 1205 复现出来,逐个看这几个问题,再对比它和死锁 1213 的区别。
默认值 @@innodb_lock_wait_timeout = 50 秒;下面把 B 会话的值改成 2 秒,免得等 50 秒 B: update t set v = v + 10 where id = 1 -> OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction'),等了 2.01 s 同一个 B 会话改第 3 行 -> ok,用了 0.9 ms(A 没碰这一行)
A 改一行不提交,B 改同一行,等满 innodb_lock_wait_timeout 报 1205。同一个会话改别的行立即成功,说明堵的只是争同一把行锁的语句。
默认(innodb_rollback_on_timeout=OFF):B 的第二条超时 -> OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')(等了 2.00 s)
B 超时之后在 B 自己会话里查第 2 行(@@innodb_rollback_on_timeout, v): (0, 7)
B 提交之后,别的会话看第 2 行的 v = 7
--innodb-rollback-on-timeout=ON:B 的第二条超时 -> OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')(等了 2.01 s)
B 超时之后在 B 自己会话里查第 2 行(@@innodb_rollback_on_timeout, v): (1, 0)
B 提交之后,别的会话看第 2 行的 v = 0默认只回滚超时的那一条语句,事务保持打开,前面成功的语句还在;打开 innodb_rollback_on_timeout 后整个事务回滚。本篇是用启动选项打开它的。默认设置下应用必须在捕获 1205 后自己决定是否回滚整个事务。
waiting_pid 21
waiting_query update t set v = v + 10 where id = 1
blocking_pid 20
blocking_query None
locked_table `d`.`t`
locked_index PRIMARY
waiting_lock_mode X,REC_NOT_GAP
blocking_lock_mode X,REC_NOT_GAP
wait_age_secs 2
堵人的会话(processlist: id, command, 空闲秒, state, info): (20, 'Sleep', 2, '', None)
堵人的事务(innodb_trx: trx_state, 已开始, 锁住的行数, trx_query): ('RUNNING', 1, 1, None)
KILL 20 之后,等的人的结果:ok,等了 1.52 s(超时设的是 20 s,中途就被放行了)
被 KILL 的 A 再发语句: OperationalError: (2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))")
第 1 行的 v = 10 (A 的更新被回滚了,只有 B 的 +10)sys.innodb_lock_waits 给出等的人和堵的人。堵的人经常是一个 Sleep 状态、没提交的事务,它的 trx_query 是空的——语句早执行完了,只是事务没结束。KILL 它之后等的人立刻继续。
t2(name 没有索引):A 改的是 name = a 那一行,performance_schema.data_locks 里 t2 的记录锁 6 个;B 改 id = 5 -> OperationalError(用了 2.01 s)
t2 的锁:index=PRIMARY mode=X data=1
t2 的锁:index=PRIMARY mode=X data=2
t2 的锁:index=PRIMARY mode=X data=3
t2 的锁:index=PRIMARY mode=X data=4
t2 的锁:index=PRIMARY mode=X data=5
t2 的锁:index=PRIMARY mode=X data=supremum pseudo-record
t3(name 有索引 idx_name):A 改的是 name = a 那一行,performance_schema.data_locks 里 t3 的记录锁 3 个;B 改 id = 5 -> ok(用了 0.01 s)
t3 的锁:index=idx_name mode=X data='a', 1
t3 的锁:index=idx_name mode=X,GAP data='b', 2
t3 的锁:index=PRIMARY mode=X,REC_NOT_GAP data=1
(默认隔离级别 REPEATABLE READ。没有索引时,扫描过的每一行都加了锁;有索引只锁命中的那一行和附近的间隙)REPEATABLE READ 下,没有索引可用时 UPDATE 扫过的每一行都被加锁。给过滤列建索引,持有的锁个数从 6 个降到 3 个,别的行不再被堵。
这一节两个会话的 innodb_lock_wait_timeout 都是默认的 50 秒 A 先改 1 再改 2,B 先改 2 再改 1:A 第二条 -> ok;B 第二条 -> OperationalError: (1213, 'Deadlock found when trying to get lock; try restarting transaction') B 的第二条语句从发出到收到 1213 用了 1.1 ms(远小于 50 s) 对照:第一节同样的两个会话争一行,没有环,只有等到 innodb_lock_wait_timeout 才报 1205(那一节把它设成了 2 秒,等了约 2 s)
死锁是一个环,InnoDB 立刻发现并回滚一方(这次 1.1 ms);没有环的单纯等待,要熬到超时。两者的提示都是重试事务,原因和处理方式不同。