mysql中锁的升级与降级：行锁与表锁转换

mysql不会主动锁升级，但ALTER table等DDL操作、无索引的UPDATE/delete、LOCK TABLES会绕过行锁直接加表锁；select … for UPDATE锁整表因全表扫描导致大量行锁。

MySQL 什么时候会从行锁升级为表锁？

MySQL 的 InnoDB 引擎本身不主动“升级”锁（不像 SQL Server 那样有明确的 lock escalation 机制），但某些操作会**绕过行锁、直接申请表级锁**，造成事实上的“锁升级”效果。这不是优化器自动升级，而是语义决定的强制行为。

常见触发场景：

• ALTER TABLE、DROP TABLE、TRUNCATE TABLE 等 DDL 操作：InnoDB 会先获取 SIX（Shared Intent eXclusive）表锁，再对每个行加 X 锁；若表很大，期间其他事务无法修改该表任何行
• 未走索引的 UPDATE 或 DELETE：例如 UPDATE t SET a=1 WHERE b=100，而列 b 没有索引 → 全表扫描 → 对所有聚簇索引记录加 X 行锁 → 效果等同于锁住整张表（尤其在 RC 隔离级别下，间隙锁不生效，但大量行锁仍阻塞并发）
• 显式使用 LOCK TABLES t WRITE：这是纯表锁，与事务隔离级别无关，且会隐式提交当前事务

为什么 SELECT ... FOR UPDATE 有时锁整张表？

根本原因不是“升级”，而是**锁范围判断失败**。InnoDB 只能在索引上加锁；如果 WHERE 条件无法命中任何索引（包括联合索引最左前缀不匹配），优化器只能走聚簇索引全扫描，于是给每条记录加 X 行锁 —— 表越大，锁越多，等待越明显。

示例：

CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(20), age INT); -- 没有为 age 建索引 SELECT * FROM t WHERE age = 25 FOR UPDATE;

此时 InnoDB 会扫描全部 id 主键记录，并对每一行加 X 锁。并发执行同样语句的事务会被全部阻塞，看起来像锁了整张表。

验证方式：执行后查 SELECT * FROM performance_schema.data_locks，能看到数百/数千个 RECORD 类型锁，而非单个 TABLE 锁。

有没有真正的锁降级？比如表锁变行锁

没有。InnoDB 不支持运行时锁降级。一旦持有表级锁（如 LOCK TABLES ... WRITE），它独立于事务，不能被缩小范围；而事务内的行锁生命周期只到事务结束，也无法“降”成更细粒度的锁（本来就是最细粒度了）。

但有一种常见误解场景：

• 用户执行 LOCK TABLES t WRITE 后，又执行 START TRANSACTION → 此时新事务内对 t 的 DML 仍受表锁限制，不会自动切换为行锁
• 想释放表锁，必须显式执行 UNLOCK TABLES，之后事务才能用正常行锁机制

注意：UNLOCK TABLES 会隐式提交当前事务（如果存在），所以不能把它当成“降级”手段来用。

如何避免意外的“伪表锁”效应？

核心是让行锁真正“按需加”，而不是被迫扫全表。关键检查点：

• 确认所有 WHERE、JOIN、ORDER BY 字段都有合适索引（用 EXPLaiN 验证 type 不是 ALL 或 index）
• 避免在索引字段上做函数操作，例如 WHERE YEAR(create_time) = 2024 会导致索引失效
• 批量更新时控制 IN 列表长度，过长可能触发优化器放弃索引选择全表扫描
• DDL 操作尽量避开业务高峰；如需在线变更，优先考虑 pt-online-schema-change 或 MySQL 8.0+ 的 ALgoRITHM=INSTANT/ALGORITHM=INPLACE

最易忽略的一点：唯一索引失效也可能导致锁扩大。例如 WHERE phone = '138...' AND deleted = 0，若 (phone, deleted) 没建联合索引，而只有 phone 单列唯一索引，InnoDB 仍可能扫出多行再过滤，从而加多行锁。