mysql中InnoDB的锁竞争与高并发优化

UPdate语句卡住select主因是未走索引导致全表扫描并大量加next-key锁，引发锁冲突或死锁；应确保WHERE条件走索引、避免函数操作、合理设计复合索引，并优化唯一键插入策略。

为什么 UPDATE 语句会卡住其他 SELECT？

InnoDB 默认使用 REPEAtable READ 隔离级别，且对 UPDATE、delete 等写操作加的是行级锁（实际是索引记录上的 next-key lock），但很多人误以为“没走索引才锁表”——其实更常见的是：没走索引 → 退化为全表扫描 → 每条记录都尝试加锁 → 大量锁冲突，甚至触发锁升级失败或死锁检测开销激增。

典型表现：SELECT ... for UPDATE 或普通 UPDATE 执行缓慢，SHOW ENGINE INNODB STATUS 显示大量 waiting for table metadata lock 或 lock wait timeout exceeded 错误。

• 检查执行计划是否走了索引：EXPLAIN SELECT ... 和 EXPLAIN UPDATE ... 必须看到 type 是 ref、range 或更好，不能是 ALL
• 复合索引要满足最左前缀：比如 WHERE status = ? AND user_id = ?，索引应建为 (status, user_id)，而非反过来
• 避免在 WHERE 条件中对字段做函数/计算：WHERE DATE(create_time) = '2024-01-01' 会跳过索引，改用 WHERE create_time >= '2024-01-01' AND create_time

如何减少 next-key lock 的范围？

next-key lock 是 InnoDB 为防止幻读而设计的“记录锁 + 间隙锁”组合。它不仅锁住匹配的记录，还锁住该记录前后的索引间隙。高并发下，间隙重叠越多，竞争越激烈。

关键控制点在于：让查询条件尽可能精确命中唯一索引，缩小间隙范围。

• 主键或唯一索引上的等值查询（WHERE id = 123）只加记录锁（record lock），不加间隙锁
• 非唯一索引等值查询（WHERE idx_col = 'x'）会锁住所有匹配记录 + 它们之间的间隙，若匹配多行，间隙范围可能很大
• 范围查询（WHERE age > 25）必然触发 next-key lock，且间隙可能覆盖整个后续区间；如需降低竞争，可考虑分页改用游标式（WHERE id > last_seen_id ORDER BY id LIMIT N）

SELECT * FROM orders WHERE order_no = 'ORD-2024-001' FOR UPDATE; -- ✅ 走唯一索引，只锁单行记录 SELECT * FROM orders WHERE user_id = 888 FOR UPDATE; -- ⚠️ 若 user_id 非唯一，可能锁住多行+间隙，尤其当 user_id 重复率高时

INSERT … ON DUPLICATE KEY UPDATE 为什么也会锁表？

这个语句看似“原子”，但 InnoDB 实际执行分三步：先按唯一键查找 → 若存在则加锁并更新 → 若不存在则插入。问题出在“查找阶段”：即使最终是插入，InnoDB 仍会对**唯一键对应的间隙**加插入意向锁（insert intention lock），与其他事务的间隙锁冲突。

常见陷阱：高频插入带时间戳的订单号（如 20240401-00001），因前缀相同，所有新记录都试图插入同一索引间隙，形成串行化瓶颈。

• 避免用时间前缀生成业务主键；改用 UUID_SHORT()、雪花 ID 或独立序列服务
• 如果必须用时间+序号，确保序号部分足够分散（例如哈希后取模分片），打破局部聚集性
• 确认 ON DUPLICATE KEY UPDATE 的唯一约束是真正需要的；有时用 INSERT IGNORE + 应用层重试更轻量

高并发下要不要关掉 gap lock？

可以设 innodb_locks_unsafe_for_binlog = ON（已弃用）或降级隔离级别到 READ COMMITTED，从而禁用间隙锁。但这不是银弹：

• READ COMMITTED 下虽然无间隙锁，但 MVCC 版本链变长，大事务下 UNDO LOG 增长快，可能触发 purge 延迟，反而拖慢整体性能
• 丢失可重复读语义：同一事务内多次 SELECT 可能读到新插入的行（幻读），业务逻辑需自行规避
• 主从复制要求：若开启 binlog_format = STATEMENT，READ COMMITTED 不被允许；必须用 ROW 格式

真正有效的优化，从来不是关锁，而是让锁更短、更细、更少重叠——比如拆分热点行（用户余额拆成多个子账户）、异步化非关键更新、用乐观锁替代悲观锁重试。