为什么MySQL使用SELECT ... FOR UPDATE加锁时会触发死锁？

1. 基础前提 #

• 事务隔离级别为READ COMMITTED，InnoDB 行锁加锁规则在 RC 级别下，会对扫描到的符合条件的行加排他锁；对于二级索引查询，会先锁二级索引条目，再回表锁聚簇索引（即主键索引）条目。
• 索引结构：存在二级联合索引evento(evento, stato)，聚簇索引为主键id。
• 两个事务均关闭自动提交，所有加锁操作会持有到事务提交/回滚才释放。

2. 执行时序与加锁过程 #

按日志执行顺序逐段分析：

1. 连接43事务第一步：执行SELECT stato FROM ingressi WHERE id=1 FOR UPDATE
   直接通过聚簇索引定位id=1的行，成功对id=1的聚簇索引加排他锁，锁持有不释放。
2. 连接39事务第一步：执行SELECT count(*) FROM ingressi WHERE evento=66 FOR UPDATE
   通过二级索引evento查询所有evento=66的行（此处id=1的行必然满足evento=66的条件）：
   • 先对二级索引上所有evento=66的条目加排他锁
   • 回表到聚簇索引取对应行时，发现id=1的聚簇索引已经被连接43加锁，连接39进入锁等待状态，等待连接43释放聚簇索引锁
3. 连接43事务第二步：执行UPDATE ingressi SET stato=0 WHERE id=1
   该操作修改了stato字段，而stato是二级联合索引evento的组成字段，InnoDB 需要更新对应的二级索引条目：
   • 更新二级索引条目之前，需要对该条目加排他锁
   • 但这个evento=66对应的二级索引条目已经被连接39加了排他锁，连接43转而等待连接39释放二级索引锁

此时循环等待形成：连接43持有id=1聚簇索引锁，等待连接39的二级索引锁；连接39持有evento=66的二级索引锁，等待连接43的聚簇索引锁，死锁成立。

3. 为什么直接抛死锁而非继续等待 #

InnoDB 内置了死锁检测机制，会实时监控事务间的锁等待依赖关系，一旦检测到循环等待（死锁），会立即选择更新行数更少、持有锁更少的事务进行回滚，避免无限等待。本次场景中连接39的事务代价更小，因此被选为回滚对象，抛出死锁错误。

4. 优化建议 #

• 调整加锁顺序：两个事务统一先加二级索引锁、再加主键锁，避免交叉等待
• 缩小加锁范围：连接39的查询如果不需要锁所有evento=66的行，可以拆分查询范围，或者用乐观锁替代悲观的FOR UPDATE加锁

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