外观
19 - 一条 SELECT 为什么会锁住别人
基线:MySQL 8.0.16+ / 8.4、InnoDB,锁定读置于显式事务。前置:11 索引、17 隔离、18 MVCC、A 实验方法。
摘要
一条 SELECT ... FOR UPDATE 已经返回,另一个连接的更新却还在等待:前者的事务没有结束,锁也还未释放。锁保护哪些对象,取决于查询如何访问索引、事务隔离级别、索引是否唯一,以及目标记录是否存在,不能只看返回行数。本文先用两连接观察一行上的冲突,再解释缺失记录、索引范围和表结构为什么也会产生等待,最后介绍诊断方法与并发任务领取。
阅读入口:先看一行被占用,再看范围为什么被保护
初读先完成第 3 节准备和第 4.1 节主键命中:A 读一行并保留锁,B 修改同一行等待,A 结束事务后 B 继续。只需两个连接,不要求先会联接诊断视图。然后学习第 4.2、5 节的缺失记录与范围,最后看第 6 节表结构等待。第 3.1 节诊断和第 7 节并发领取可第二遍再做。
本文用 RR 表示 REPEATABLE READ(可重复读),用 RC 表示 READ COMMITTED(读已提交)。在这两个隔离级别下,普通一致性读通常通过 MVCC(多版本并发控制)选择可见的数据版本;锁定读则读取记录并加锁,为后续操作协调冲突。本文使用的锁定子句是 FOR UPDATE。
先理解一行上的过程:A 在显式事务中锁定读取一行;B 要修改同一行,需要取得与 A 冲突的锁,因此等待;A 提交或回滚后释放锁,B 才能继续。查询返回了结果,不等于事务已经结束。 锁定读取得的是事务期间的保护,不是永久所有权。普通一致性读、锁定读和修改语句遵循不同规则,不能凭 SQL 以 SELECT 开头就判断它是否会等待。
1. 现象:只返回两行,区间内插入却无法完成
第 5 节的数据中,score 分别为 10、20、30。A 在 RR 事务中沿 score 索引执行范围锁定读,条件为 BETWEEN 10 AND 20,返回两行;B 插入 score=15 的新行,预期仍会等待。
原因是范围保护还涉及索引记录之间的空位,称为索引间隙。虽然 15 对应的记录尚不存在,它的插入位置已经落在被保护的间隙里。只锁住已有记录不足以阻止范围内新增,因此查询返回两行,不代表只保护这两行。
普通一致性读一般不用记录锁阻止写入,但仍需 MDL(Metadata Lock,元数据锁)保护正在使用的表定义。MDL 协调的是表结构的使用与修改,和数据记录锁不同。排障时先区分记录锁等待、间隙上的插入等待、MDL 等待和 I/O(磁盘读写)等待,再选择观察入口。
2. 原理:锁对象与兼容性
理解锁时要分开看两个问题:保护什么对象,以及哪些请求可以同时持有。 后者称为兼容性。记录锁作用于索引记录;S(共享)和 X(排他)是锁模式。表级 IS(意向共享)和 IX(意向排他)表达事务在下层记录上加锁的意图,不等于把整张表锁成互斥资源。
| 类型 | 保护对象 | 关键边界 |
|---|---|---|
| S / X 记录锁 | 索引记录 | 冲突锁定读/写需协调,普通 MVCC 读不因此自动等待 |
| IS / IX 意向锁 | 表级意向 | 多个 IX 可兼容,不表示整个表被排他锁住 |
| Gap Lock | 索引间隙 | 主要阻止插入;不同事务的间隙锁可以共存 |
| Next-Key Lock | 索引记录及其前间隙 | RR 范围访问防止相关范围插入 |
| Insert Intention | 插入位置的意向 | 不同位置未必互阻,会与该间隙的保护锁冲突 |
| Metadata Lock | 表等对象定义 | SELECT、DML、DDL 都参与,不同于 InnoDB 表意向锁 |
Gap Lock 是间隙锁,主要约束向受保护位置插入。Next-Key Lock 则把一条索引记录和它之前的间隙一起保护。Insert Intention(插入意向锁)是插入时针对目标位置的请求,与表级 IX 是不同概念;不同事务的插入是否互相阻塞,还要看位置和已有的间隙保护。
唯一索引的完整等值查找若命中已有记录,就已经定位到唯一目标,通常只需记录锁。若目标不存在,RR 下可能保护它本应插入的间隙。复合唯一键由多列共同保证唯一性,只匹配部分列仍可能对应多个目标,不满足完整唯一查找条件。
InnoDB 的聚簇索引保存完整行数据,本例使用主键组织聚簇索引。二级索引是聚簇索引以外的索引;通过它定位后再访问聚簇记录,称为回表。二级索引锁定读还可能锁对应的聚簇记录,因此诊断时要同时查看两个索引。索引不合适可能扩大扫描范围,进而扩大锁集合。
RC 对普通搜索通常减少间隙锁,但外键和重复键检查等仍有例外。降低隔离级别会改变范围保护,不能把它理解成消除所有锁等待。
3. 准备:固定数据与观察入口
仅在独立可丢弃的实验库准备;先主动结束初始化连接的旧事务,再记录原自动提交设置。后面的建表不属于业务事务。
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET @lock_saved_autocommit=@@session.autocommit;
SET autocommit=1;
CREATE TABLE IF NOT EXISTS lock_demo (
id BIGINT PRIMARY KEY, score INT NOT NULL, payload VARCHAR(32) NOT NULL,
KEY idx_score(score)
) ENGINE=InnoDB;
DELETE FROM lock_demo;
INSERT INTO lock_demo VALUES (1,10,'one'),(2,20,'two'),(3,30,'three');A 负责持锁,B 发起冲突操作,可选的 C 负责观察。三者使用独立连接,各自先结束旧事务,再准备环境。autocommit=1 表示没有显式事务时,每条语句自动提交;执行 START TRANSACTION 后,仍须用 COMMIT 或 ROLLBACK 结束事务。CONNECTION_ID() 用于记录连接身份,方便与诊断结果对应。
sql
USE kb_database_lab;
SET @lock_saved_autocommit=@@session.autocommit;
SET @lock_saved_level=@@session.transaction_isolation;
SET @lock_saved_row_timeout=@@session.innodb_lock_wait_timeout;
SET @lock_saved_mdl_timeout=@@session.lock_wait_timeout;
SET autocommit=1;
SELECT CONNECTION_ID();A 在事务外执行 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;。B 在事务外设置 SET SESSION innodb_lock_wait_timeout=30;,将 InnoDB 行锁等待超时设为 30 秒。每组实验后,先结束所有参与事务,再将数据重置为初始三行。下文描述的是预期结果;实际锁集合应以目标实例保存的输出为准。
3.1 C 联接等待者、持有者和对象(可选诊断)
诊断要回答“谁在等谁,争用什么”。data_lock_waits 给出等待者与阻塞者的对应关系,称为等待边;data_locks 给出锁对象和模式;threads 把内部线程对应到连接 ID;innodb_trx 补充事务时间和修改量。下面将这些信息联接起来,过滤出实验表上的等待。
sql
SELECT waiting_thread.PROCESSLIST_ID AS waiting_connection,
blocking_thread.PROCESSLIST_ID AS blocking_connection,
requested.OBJECT_SCHEMA, requested.OBJECT_NAME,
requested.INDEX_NAME, requested.LOCK_TYPE,
requested.LOCK_MODE AS requested_mode,
requested.LOCK_DATA AS requested_key,
held.LOCK_MODE AS blocking_mode,
waiting_trx.trx_started AS waiting_trx_started,
waiting_trx.trx_wait_started AS waiting_lock_wait_started,
blocking_trx.trx_started AS blocking_trx_started,
blocking_trx.trx_rows_modified AS blocking_modified
FROM performance_schema.data_lock_waits AS waits
JOIN performance_schema.data_locks AS requested
ON requested.ENGINE=waits.ENGINE
AND requested.ENGINE_LOCK_ID=waits.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS held
ON held.ENGINE=waits.ENGINE
AND held.ENGINE_LOCK_ID=waits.BLOCKING_ENGINE_LOCK_ID
LEFT JOIN performance_schema.threads AS waiting_thread
ON waiting_thread.THREAD_ID=waits.REQUESTING_THREAD_ID
LEFT JOIN performance_schema.threads AS blocking_thread
ON blocking_thread.THREAD_ID=waits.BLOCKING_THREAD_ID
LEFT JOIN information_schema.innodb_trx AS waiting_trx
ON waiting_trx.trx_mysql_thread_id=waiting_thread.PROCESSLIST_ID
LEFT JOIN information_schema.innodb_trx AS blocking_trx
ON blocking_trx.trx_mysql_thread_id=blocking_thread.PROCESSLIST_ID
WHERE requested.OBJECT_SCHEMA='kb_database_lab'
AND requested.OBJECT_NAME='lock_demo';账号需相关视图权限,全局 innodb_trx 观察通常还需 PROCESS。视图动态变化,联接时事务或锁可能消失,空结果不证明没有发生等待;LOCK_DATA 也未必显示全部键值。重复采样、记录连接 ID 和原始输出并关联 SQL。
这里的 trx_started 是事务开始时间,不是这次锁等待开始时间,不能直接据此计算等待时长。trx_wait_started 在事务处于 LOCK WAIT 时记录当前锁等待的开始时间,否则为 NULL;动态联接也可能缺行。字段含义见官方 INNODB_TRX 表说明,仍需结合应用语句时间线核验。B 正在等待时不能在同一个连接再发诊断 SQL,观察要使用 C;没有观察权限也可以先完成两连接的主线实验,但不能据等待现象猜出精确锁集合。
4. 验证一:完整唯一命中与不存在记录
4.1 命中主键
A:
sql
START TRANSACTION;
SELECT * FROM lock_demo WHERE id=2 FOR UPDATE;B 执行 UPDATE lock_demo SET payload='changed' WHERE id=2;,预期等待;C 应看到 PRIMARY 上记录锁,常见展示为 X,REC_NOT_GAP。A COMMIT 后 B 完成;其他主键通常不因该记录锁阻塞。
操作顺序是“A 查询返回 → B 更新开始等待 → 在 A 执行 COMMIT → 等 B 返回”。应在 B 的等待超时前释放 A 的锁。本例 B 开启自动提交,没有显式事务,更新成功就提交;如果改成显式事务,B 也必须提交或回滚。若 B 先超时,该次更新已经失败,不会因为 A 随后提交就自动重试;应记录错误、结束事务后重新准备。
4.2 目标不存在
确保只有 id=1、2、3。A RR:
sql
START TRANSACTION;
SELECT * FROM lock_demo WHERE id=4 FOR UPDATE;没有返回行,B 执行 INSERT INTO lock_demo VALUES (4,40,'four'); 仍预期等待。因为当前最大主键为 3,id=4 位于索引末端的间隙。C 可能看到末端 supremum 伪记录相关保护及插入意向等待;supremum 是表示索引页记录上界的内部伪记录,不是业务表中的一行。若 B 尚未超时,A ROLLBACK 后 B 预期完成。
结束事务、删除 id=4,把 A 改 RC 重复,普通缺失搜索预期不以相同 RR 间隙保护阻止插入。记录是否存在与隔离级别都必须进入结论,不能仅说主键查询只锁一行。
5. 验证二:二级索引范围与回表锁
A 在事务外恢复 RR:
sql
EXPLAIN SELECT * FROM lock_demo FORCE INDEX(idx_score)
WHERE score BETWEEN 10 AND 20 FOR UPDATE;
START TRANSACTION;
SELECT * FROM lock_demo FORCE INDEX(idx_score)
WHERE score BETWEEN 10 AND 20 FOR UPDATE;EXPLAIN 用于查看访问计划,FORCE INDEX 用于控制本例的索引访问路径,不作生产优化建议。B 插入 (4,15,'four') 预期等待;C 先保存等待边,再查询本表所有锁,以区分正在等待的请求和已持有的锁:
sql
SELECT ENGINE_TRANSACTION_ID,THREAD_ID,INDEX_NAME,
LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_SCHEMA='kb_database_lab' AND OBJECT_NAME='lock_demo';预期可能有 idx_score 范围锁、PRIMARY 记录锁和表级 IX。不能看到 IX 就认为整表写锁,也不能按 BETWEEN 文本宣称边界外一概不受影响:搜索终止点和执行路径会影响锁集合。
A 提交、B 完成,重置后改 RC 重复。区间内插入预期可完成,但对已锁记录的冲突更新仍等待。本例没有外键/唯一冲突,不据此推断 RC 所有插入都无等待。
6. 验证三:长事务、DDL 与 MDL 队列
DDL(数据定义语句,如 ALTER TABLE)改变表结构,且可能隐式提交,只在实验库进行。本组实验先确保没有遗留的 mdl_marker 列。A 执行 START TRANSACTION,再普通查询 lock_demo,保持事务不结束。这个查询虽然没有 FOR UPDATE,仍使用表定义并持有相关 MDL;B 修改同一表的结构需要取得不兼容的 MDL,因此预期等待。B 执行:
sql
SET SESSION lock_wait_timeout=15;
ALTER TABLE lock_demo ADD COLUMN mdl_marker INT NULL;B 等待时,C:
sql
SELECT object_schema,object_name,waiting_pid,waiting_query,
blocking_pid,blocking_account
FROM sys.schema_table_lock_waits
WHERE object_schema='kb_database_lab' AND object_name='lock_demo';sys 视图与 metadata_locks 的 Instrument(采集项)需可用,账号也需相应权限;缺失时检查底层 metadata_locks 的 GRANTED(已授予)/PENDING(等待中)状态及 threads,不要为实验擅自开启全局采集。新查询可能排在等待中的 DDL 后,形成更长的等待链;应另开 D 测试,避免阻塞负责观察的 C。
A COMMIT 后 B 可完成或已超时,先核对结果再清理新增列。不要反复提交 ALTER 增加排队,不自动执行视图生成的 KILL 文本。innodb_lock_wait_timeout 不是 MDL 超时配置。
7. 实现:NOWAIT / SKIP LOCKED 的领取边界(可选进阶)
sql
CREATE TABLE IF NOT EXISTS lock_task (
id BIGINT PRIMARY KEY, state VARCHAR(16) NOT NULL,
KEY idx_state_id(state,id)
) ENGINE=InnoDB;先准备两条 READY(待领取)数据:
sql
DELETE FROM lock_task;
INSERT INTO lock_task VALUES (1,'READY'),(2,'READY');初始化只做一次,沿用第 3 节的自动提交环境,并先结束其他实验事务。消费者是负责领取任务的连接;两个消费者分别在显式事务中开始领取:
sql
START TRANSACTION;
SELECT id FROM lock_task WHERE state='READY' ORDER BY id
LIMIT 1 FOR UPDATE SKIP LOCKED;取得行后,把返回 ID 作为参数更新为 RUNNING,检查影响行数并提交;没有返回行则结束事务而非无条件更新。多个消费者应取得不同未锁任务,结果不保证完整一致或公平,不用于余额与报表。
例如取得 id=1 时,仍在同一连接和事务中执行;其他消费者替换为自己实际取得的 ID:
sql
UPDATE lock_task SET state='RUNNING' WHERE id=1 AND state='READY';
SELECT ROW_COUNT() AS affected_rows;影响恰好 1 行才 COMMIT,否则 ROLLBACK。领取测试中让第一个消费者暂不提交,再让第二个领取,最后结束两边事务并由 C 验证结果。
NOWAIT 让遇到冲突记录锁的请求立即失败,SKIP LOCKED 跳过冲突行;二者都不免除 MDL 等其他等待。领取事务把任务状态改为 RUNNING(执行中)并提交后,行锁就不再承担任务所有权的保护。
如果消费者提交后崩溃,任务可能停在 RUNNING。生产系统还需租约(有期限的任务占用)、接管(重新领取失去执行者的任务)和幂等(重复执行不产生重复业务效果)机制。领取成功不等于任务已经完成;可靠发布见Outbox。
8. 排障与取舍
按等待连接、持有连接、事务时长、争用对象和访问路径还原阻塞链。持有者可能显示为 Sleep,它只表示当前没有执行语句,不代表事务已结束;最后一条 SQL 也未必是最初持锁的语句。先处理异常批处理、缩短事务、修复索引和访问顺序,再评估隔离级别变化。
KILL QUERY 可能只中断当前语句,不保证其他事务锁释放;KILL CONNECTION 可能触发昂贵回滚。先确认归属、修改量和重试,风险动作见26。降低隔离、跳锁和延长超时都需重新验证17 的业务不变量。
9. 总结与导航
返回行数不是锁数量;诊断必须把等待边、索引对象、事务边界和业务规则连起来。
- 后续:20 死锁定位与重试。
- 应用:03 在线表变更、23 连接耗尽。
- 框架:Spring 传播与资源持有。
- 参考资料:MySQL Reference Manual 的 InnoDB Locking、Locking Reads、Metadata Locking、data_locks/data_lock_waits 和 sys schema_table_lock_waits;实际输出、版本与权限仍需目标实例验收。
10. 实验退出与名词解释
所有参与连接先确认等待语句已经返回,再主动结束事务。不要看到 A 已提交就假定 B 也结束:B 可能随后完成写入,或在超时后仍有旧事务。各实验连接在事务外恢复自己的设置:
sql
SET SESSION transaction_isolation=@lock_saved_level;
SET SESSION innodb_lock_wait_timeout=@lock_saved_row_timeout;
SET SESSION lock_wait_timeout=@lock_saved_mdl_timeout;
SET autocommit=@lock_saved_autocommit;只执行本连接已保存变量对应的恢复;初始化连接若只保存自动提交,就只恢复这一项。重连后重新准备,不使用已丢失的变量。所有连接退出后,可按需删除 lock_task、lock_demo 实验表;若要保留表,只在确认 mdl_marker 确已创建后单独清理该列。DDL 不应在未完成业务事务中执行。
| 名词 | 本文含义 |
|---|---|
| 记录锁 | 协调对索引记录的冲突访问,不按返回行数计数 |
| S/X | 共享/排他锁模式,表达允许哪些冲突操作同时进行 |
| IS/IX | 表级意向共享/排他模式,表示将访问下层记录,不等于整表互斥 |
| 间隙锁 | 保护索引中记录之间的位置,主要约束插入 |
| Next-Key Lock | 记录锁与该记录之前间隙保护的组合 |
| 插入意向锁 | 插入时对目标位置的锁请求,与表级 IX 不同 |
| MDL | 元数据锁,协调表定义的使用与结构修改 |
| 等待边 | 某事务正在等待另一个事务持有的冲突资源 |
| NOWAIT/SKIP LOCKED | 冲突记录锁立即失败/跳过冲突行,不消除所有等待 |