mysql中锁的升级策略与性能影响_技术教程

MySQL不会主动进行行锁升级为表锁，但ALTER TABLE、缺失索引的UPDATE/DELETE、LOCK TABLES等操作会间接导致全表加锁；gap锁和next-key锁在RR隔离级别下扩大锁定范围，易引发锁冲突。

MySQL 什么时候会触发行锁升级为表锁

MySQL 的 InnoDB 存储引擎本身不支持传统意义上的“锁升级”（如 SQL Server 那样主动将大量行锁合并为一个表锁），但某些操作会**间接导致全表范围加锁**，效果等同于表级阻塞。关键触发点是：ALTER TABLE、OPTIMIZE TABLE、缺失有效索引的 UPDATE/DELETE，以及显式使用 LOCK TABLES。

执行 ALTER TABLE t ADD COLUMN x INT（非 ALGORITHM=INPLACE）时，InnoDB 会获取 MDL（metadata lock） + 全表 X 锁，阻塞所有 DML
没有 WHERE 条件或 WHERE 列无索引的 UPDATE t SET a=1，会走全表扫描 → 对每个匹配的聚簇索引记录加 X 行锁，同时可能因 gap 锁膨胀引发大量锁冲突
SELECT ... FOR UPDATE 在唯一索引上命中单行时只锁该行；但在普通索引或无索引字段上，可能锁住整个索引范围（包括间隙），看起来像“锁变多”

gap 锁和 next-key 锁如何放大锁影响范围

InnoDB 默认事务隔离级别为 REPEATABLE READ，此时普通 SELECT 不加锁，但 UPDATE/DELETE/SELECT ... FOR UPDATE 会使用 next-key lock（行锁 + 左侧 gap 锁）。这本用于防止幻读，但容易被误认为“锁升级”。

假设 id 是主键，当前有记录 (1),(5),(10)，执行 SELECT * FROM t WHERE id > 3 AND id ，实际会锁住区间 (1,5] 和 (5,10) —— 即 5 这一行 + (5,10) 这个间隙
如果查询条件落在空隙中（如 WHERE id = 7），且 7 不存在，则只加 gap 锁 (5,10)，允许其他事务插入 6 或 8，但会阻塞插入 7
关闭 gap 锁的代价是降级到 READ COMMITTED，此时 UPDATE 只加行锁，不加 gap 锁，但幻读风险回归

如何验证当前事务持有的锁类型与范围

不能靠猜，得查 information_schema 视图或使用 SHOW ENGINE INNODB STATUS。注意：这些信息只反映最近一次死锁或当前活跃锁状态，不是实时全量快照。

查正在等待的锁：
```
SELECT * FROM information_schema.INNODB_TRX\G
```
关注 TRX_STATE 和 TRX_WAITING_LOCK_ID
查锁详情：
```
SELECT * FROM information_schema.INNODB_LOCKS\G
```
（MySQL 5.7+ 已废弃，建议用 performance_schema.data_locks）
更实用的是：
```
SELECT * FROM performance_schema.data_locks\G
```
中 LOCK_TYPE 为 RECORD 表示行锁，LOCK_MODE 含 GAP 或 NEXT-KEY 表示涉及间隙
若看到 LOCK_DATA 显示 supremum pseudo-record，说明锁住了索引末尾间隙，常出现在 ORDER BY ... LIMIT 场景中

避免隐式锁膨胀的实际操作建议

多数“锁升级感”来自设计或执行偏差，而非 InnoDB 主动

升级。核心思路是：让锁尽量窄、尽量短、尽量可预测。

写 UPDATE/DELETE 语句前，务必 EXPLAIN 确认是否走了索引；避免在 TEXT/JSON 字段或函数包裹列（如 WHERE UPPER(name)='A'）上过滤
批量更新分页处理：用 WHERE id > ? ORDER BY id LIMIT 1000 替代 LIMIT 10000,1000，减少扫描和锁数量
高并发更新同一热点行（如计数器）时，考虑改用 INSERT ... ON DUPLICATE KEY UPDATE 或应用层重试，避免长事务持锁
业务允许时，把 REPEATABLE READ 改为 READ COMMITTED，可消除 gap 锁，但需同步评估幻读对业务逻辑的影响