非唯一索引UPDATE需锁两处：先锁二级索引项，再回表锁对应聚簇索引主键行，该过程非原子，且RR级别下还额外加间隙锁，易因锁序不一致引发死锁。
非唯一索引 Update 为什么锁两处：先二级索引再聚簇索引
MySQL 的行锁本质是锁索引，不是锁记录。InnoDB 执行 UPDATE 时，若 WHERE 条件命中的是非唯一二级索引（比如 KEY idx_status (status)），它必须先通过该索引定位数据，再回表查主键——这个过程会分两步加锁：idx_status 上的记录锁（或 next-key 锁），紧接着是对应主键值在聚簇索引上的排他锁（X 锁）。这两把锁不是原子获取的，中间存在时间窗口。
常见错误现象：Deadlock found when trying to get lock; try restarting transaction 就常出现在两个事务交叉执行这两步时：事务 A 拿了 idx_status 锁、正等主键锁；事务 B 已拿了主键锁、正等 idx_status 锁。
- 只要
WHERE条件用的是非唯一索引，就一定触发回表，就一定涉及至少两个索引层级的加锁 - 唯一索引（含主键）可直接定位到聚簇索引页，通常只锁一处（优化后退化为 record lock）
- 没索引？那就全表扫描，对每条聚簇索引记录逐个加 X 锁，锁范围更大，死锁概率反而可能更高
RR 隔离级别下，非唯一索引还会额外锁间隙
在默认的 REPEATABLE READ 隔离级别中，InnoDB 对非唯一索引的等值查询（如 WHERE status = 1）不仅锁匹配的索引项，还会加 Gap Lock 或 Next-Key Lock，防止幻读。这意味着即使你只更新一行，实际锁住的可能是整个索引区间。
例如：索引 idx_status 中有 status=1 的记录分布在 (id=100) 和 (id=200) 之间，InnoDB 可能锁住 (100, 200) 这个间隙——此时另一个事务想插入 status=1, id=150 就会被阻塞，若双方又在主键上互等，死锁立即发生。
-
READ COMMITTED下间隙锁被禁用，但仅降低幻读风险，不解决回表导致的双索引加锁问题 - 复合索引中只要最左前缀是非唯一的（如
KEY idx_u_i_s (user_id, item_id, status)），且查询只用到user_id，同样会触发间隙锁 + 回表锁 - 用
EXPLAIN FORMAT=tree可确认是否走了索引；用SELECT * FROM performance_schema.data_locks能看到具体锁类型和范围
怎么避免这种“锁两处+锁间隙”的组合拳
核心思路是绕过非唯一索引的不确定性加锁路径，强制走确定、单一、最小化的加锁路线——即直接基于主键或唯一索引操作。
- 先
SELECT id FROM t WHERE status = ? LOCK IN SHARE MODE（带排序，如ORDER BY id），再用得到的id批量UPDATE t SET ... WHERE id IN (...)；确保所有事务按相同顺序（如升序）获取主键锁 - 把
WHERE条件改写成主键等值查询，哪怕要多一次查询；比方说业务层缓存order_id → id映射，后续更新直击WHERE id = ? - 给高频查询字段补唯一约束（如
order_id），让二级索引升级为唯一索引，使 next-key lock 退化为 record lock，消除间隙部分 - 批量更新时绝对不要混用
IN和非主键条件；UPDATE ... WHERE non_pk_col IN (a,b,c)会让优化器对每个值重复执行回表流程，锁放大效应翻倍
容易被忽略的细节：ORDER BY 不影响 UPDATE 加锁顺序
有人以为在 UPDATE 语句里加 ORDER BY id 能统一加锁顺序，这是错的。ORDER BY 在 UPDATE 中是语法允许但被 MySQL 忽略的（除少数版本外），它不会改变索引扫描顺序，也不会影响锁获取顺序。真正起作用的是你从哪张索引开始扫、扫哪些值、以及这些值在 B+ 树中的物理排列。
所以别依赖 SQL 里的 ORDER BY，而要在应用层控制 ID 列表的生成顺序，或用子查询强制驱动顺序，例如：
UPDATE t SET status = 2 WHERE id IN (
SELECT id FROM (
SELECT id FROM t WHERE status = 1 ORDER BY id
) AS tmp
);但注意：这种写法在 MySQL 8.0 前可能被优化为物化临时表，反而引入新锁竞争；更稳妥的方式仍是应用层分批 + 升序拼接 IN 列表。
