唯一二级索引更新需锁两次:先锁二级索引项,再回表锁主键索引;而主键索引更新只锁一次聚簇索引记录,且不加间隙锁,死锁概率更低。

唯一二级索引更新要锁两次,主键索引只锁一次
主键索引更新时,InnoDB 直接定位到聚簇索引叶子节点,加一把 X 锁就够了。而唯一二级索引(比如 username)更新时,必须分两步:先在二级索引上加 X 锁(锁定 username='alice' 对应的索引项),再根据它存储的主键值回表,在主键索引上再加一把 X 锁。这两把锁不是原子的,中间存在时间窗口。
常见错误现象:UPDATE users SET email='a@b.com' WHERE username='alice' 和 UPDATE users SET status=1 WHERE id=123 同时执行,可能一个事务锁了 username 索引 + 主键行,另一个事务锁了主键行 + 其他二级索引项,形成交叉等待。
- 锁资源叠加:唯一二级索引操作天然引入「二级索引锁 + 主键锁」双资源依赖
- 加锁顺序固定但不可控:总是先二级索引、后主键,但不同 SQL 走的索引路径不同,事务间加锁顺序就可能不一致
- 即使只更新非索引列,只要 WHERE 条件命中唯一二级索引,仍触发双锁
唯一二级索引插入也加 Next-Key Lock,不是纯行锁
很多人误以为「唯一」就等于「只锁那一条」,其实不是。即使是唯一二级索引,INSERT 或 INSERT ... ON DUPLICATE KEY UPDATE 也会走搜索路径校验唯一性,InnoDB 必须对目标键值所在间隙加 Next-Key Lock(记录锁 + 间隙锁)。尤其当索引页分裂频繁、或插入值离散(如 UUID)时,多个事务容易落在同一间隙区间,互相阻塞。
使用场景:两个事务同时执行 INSERT INTO t (uid, name) VALUES (UUID(), 'test') ON DUPLICATE KEY UPDATE name='test2',哪怕插入的是不同 UUID,也可能因搜索路径重叠在同一个 B+ 树间隙上而死锁。
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。
- 间隙范围取决于 B+ 树当前结构,不是由值本身决定;
SHOW ENGINE INNODB STATUS中能看到WAITING FOR THIS LOCK TO BE GRANTED并指向 gap -
ON DUPLICATE KEY UPDATE实际执行逻辑 =SELECT ... FOR UPDATE+ 决定 INSERT 还是 UPDATE,锁早已持有 - 多唯一索引(如同时有
uk_email和uk_phone)会让锁竞争翻倍,每条 INSERT 都要依次检查并加锁所有唯一约束
唯一二级索引 + NULL 值会意外扩大锁范围
MySQL 对唯一二级索引中 NULL 的处理是特殊且容易被忽略的:**多个 NULL 被视为不相等,因此允许重复,但 InnoDB 在加锁时仍会把它们归入同一逻辑间隙**。这就导致看似无关的插入(比如 INSERT ... (customer_id=NULL, client_id=10, app_id=5))可能和已有非 NULL 记录(如 (8000,10,5))落在同一搜索路径上,触发间隙冲突。
常见错误现象:往带联合唯一索引 uk1(customer_id,client_id,app_id) 的表里插入 (NULL, 8001, 10, 5),却卡住等待;而插 (7999,10,5) 却成功——因为 NULL 的定位行为让锁范围“漂移”了。
- NULL 不参与唯一性比较,但参与索引树搜索和加锁判定
- 联合索引中只要左侧列含 NULL,整个键的排序位置不确定,间隙边界模糊
- 用
EXPLAIN FORMAT=tree看执行计划,未必暴露这个问题;必须靠SHOW ENGINE INNODB STATUS查实际锁 heap no 和 gap
主键索引死锁概率低,是因为它没“间隙干扰”
主键是聚簇索引,数据按主键物理排序,InnoDB 对主键等值更新(WHERE id = ?)只加记录锁(Record Lock),不加间隙锁——除非你用范围条件(如 id > 100)。而唯一二级索引无论是否范围查询,在 RR 隔离级别下都可能引入间隙成分,尤其是当索引非空值稀疏、或存在大量删除/插入造成页分裂时,间隙边界变得不可预测。
性能影响:主键自增写入天然顺序,页分裂少、锁集中;唯一二级索引(尤其随机值)导致锁分散在多个页面,缓冲池压力大,innodb_row_lock_waits 显著升高。
- 主键更新不会引发临键锁(Next-Key Lock),除非显式用范围条件
- 唯一二级索引的“唯一性校验”动作本身就有开销,每次都要遍历索引路径,不是 O(1)
- 真正难排查的点在于:SQL 看似简单(单行更新),但背后锁行为由索引结构、数据分布、历史 DML 共同决定,静态分析容易漏

















