MySQL InnoDB锁实验出现锁超时与死锁问题咨询

一、无br_id索引时锁超时的根本原因 #

你观察到的"锁表"不是真的InnoDB表级锁，是无索引时update语句触发全表扫描加的全通行锁+间隙锁集合，核心逻辑如下：

• InnoDB的行锁是加在索引上的，当update语句的过滤条件字段br_id没有索引时，数据库只能走全表扫描逐行判断是否符合条件。
• 默认可重复读（RR）隔离级别下，InnoDB会给所有扫描过的行加排他（X）行锁，同时给扫描过的所有间隙加间隙（GAP）锁防止幻读，这些锁会一直持有到事务提交/回滚才释放。
• 你的saveMany方法被@Transactional修饰，整个200次循环都在同一个大事务中，锁持有时间长达400秒（200次*2秒sleep）。
• 当saveMany先执行第一次update时，此时表是空的，全表扫描会直接给主键索引的整个间隙（从负无穷到上确界伪记录）加GAP锁，后续插入的br_id=1的行也会被加X行锁，全部持有到事务结束。
• 后续启动的saveOne执行update br_id=2时，同样要走全表扫描，申请行锁和间隙锁时会被saveMany持有的锁挡住，等待50秒后触发超时。

对应你的实验场景：

• 场景4仅注释行1时，saveMany的事务内只有insert操作，插入的所有行都持有X行锁，saveOne的update全表扫描时申请这些行锁被阻塞，触发超时。
• 场景5仅注释行2时，saveMany的事务内只有update操作，持有全表的GAP锁，saveOne的update因为GAP锁兼容可以正常执行，但insert的插入意向锁和GAP锁冲突，触发超时。

二、并行insert或并行update无异常的原因 #

仅并行insert的情况 #

InnoDB的插入意向锁是间隙锁的一种，多个事务的插入意向锁之间互相兼容，只要插入的主键/唯一键不冲突，并行插入不会互相阻塞，所以两个事务分别插br_id=1和br_id=2的行可以正常执行。

仅并行update的情况 #

间隙锁的核心特性是间隙锁之间互相兼容，不管是X间隙锁还是S间隙锁，多个事务可以同时持有同一个间隙的GAP锁，只有插入操作的插入意向锁会和GAP锁冲突。如果只有update没有insert，两个事务的update都能拿到全表的GAP锁，执行完直接提交，不会出现等待。

三、添加br_id索引后立刻触发死锁的原因 #

给br_id加普通二级索引后，update语句会走br_id索引，不再全表扫描，此时触发死锁是因为满足了循环等待条件，时序如下：

1. saveMany先执行update br_id=1，此时表是空的，br_id索引只有一个全局间隙（负无穷~supremum），给该间隙加X GAP锁，成功。
2. saveOne执行update br_id=2，同样给br_id索引的全局间隙加X GAP锁，因为GAP锁兼容，成功。
3. saveMany执行insert插入br_id=1的行，需要申请该间隙的插入意向锁，插入意向锁和saveOne持有的GAP锁冲突，进入等待。
4. saveOne执行insert插入br_id=2的行，同样需要申请该间隙的插入意向锁，和saveMany持有的GAP锁冲突，进入等待。

此时两个事务互相持有对方需要的锁，形成循环等待，直接触发死锁检测，抛出死锁异常。

内容的提问来源于stack exchange，提问作者Jignesh M. Khatri