MySQL学习专题

MySQL 无索引更新:为什么阻塞很多行却不是锁升级

用 MySQL 8.4 的双连接实验观察非索引条件更新后的 RECORD 锁与 TABLE IX,纠正把大范围记录锁称为行锁升级表锁的说法。

简明结论

InnoDB 无合适索引时可能扫描并锁住很多记录;看到其他行更新被阻塞,不能直接认定发生了行锁升级为表锁。

原资料标题中的“无索引行锁升级为表锁”把阻塞现象和锁类型混为一谈。这里改用可观察的记录锁解释:在本例 REPEATABLE READ 下,全表扫描的 UPDATE 可能使多个记录被锁住,但表上的 IX 意向锁并不是表级 X 锁。

准备三行数据

在独立的 MySQL 8.4 / InnoDB 实验实例运行:

CREATE DATABASE phase2_knowledge;
USE phase2_knowledge;
CREATE TABLE lock_demo (
  id INT PRIMARY KEY, flag INT NOT NULL, value INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO lock_demo VALUES (1,0,0),(2,1,0),(3,0,0);

若已创建同名实验库,仅在其中创建这张测试表。flag 上没有索引。

用两个连接复现

连接 A:

USE phase2_knowledge;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
UPDATE lock_demo SET value=value+1 WHERE flag=1;
-- 暂不提交;只有 id=2 的数据被修改,但扫描涉及其他记录。

观察连接需要读取 performance_schema 的权限:

SELECT DISTINCT LOCK_TYPE,LOCK_MODE
FROM performance_schema.data_locks
WHERE OBJECT_SCHEMA='phase2_knowledge' AND OBJECT_NAME='lock_demo'
ORDER BY LOCK_TYPE,LOCK_MODE;

本次验证结果包含 RECORD 锁和 TABLE / IX,没有 TABLE / X。不同记录的具体锁模式可不同,不能只搜索输出中是否有字母 X。

连接 B:

USE phase2_knowledge;
SET SESSION innodb_lock_wait_timeout=1;
UPDATE lock_demo SET value=99 WHERE id=3;

B 在本例中超时并报告错误 1205。随后在 A 执行 ROLLBACK;,B 再执行同一更新能够完成。本例的超时是人为设定的一秒,不是生产默认值。

怎样改善

缩短事务、避免无必要的大范围修改,并评估让 WHERE 条件具备合适索引。索引会改变扫描和锁定范围,但要同时考虑选择性与写入成本;READ COMMITTED、唯一点查等条件下的锁行为应另行验证。死锁错误 1213 也不能与本例等待超时 1205 混用处理逻辑。

本站新增了锁类型检查与双连接实验,保留旧原文出处;没有验证所有隔离级别和所有更新计划。

继续阅读事务快照与隔离和死锁复现。