MySQL 锁机制实战：从全局锁到间隙锁，用 data_locks 看清每一把锁

MySQL 锁机制实战从全局锁到间隙锁用 data_locks 看清每一把锁本文所有实验均在真实云服务器Ubuntu 24.04 / MySQL 8.0.46IP119.3.***.***完成输出为实机回显未做任何修饰与编造。一、引言一次整点备份把线上写挂了的事故那是一个平凡的周二晚上运营同学要做一次整库逻辑备份脚本里写着mysqldump --single-transaction本该无锁结果 DBA 手滑写成了flush tables with read lock;后面忘了unlock。一瞬间所有写入线程全部卡在Waiting for global read lock下单接口 100% 超时告警群炸了。这类事故的本质是没搞清楚 MySQL 里到底有几类锁、各自锁住什么、会互相阻塞谁。MySQL 的锁按粒度可以分成三大类全局锁 / 表级锁 / 行级锁而行级锁在 RR可重复读隔离级别下又因为间隙锁gap lock和next-key lock变得格外微妙是幻读、死锁的主要来源。本文用一组真实多会话实验配合 MySQL 8 新增的performance_schema.data_locks视图把每一把锁都透视出来让你在show processlist和死锁日志里不再两眼一抹黑。二、实验环境项目值操作系统Ubuntu 24.04.4 LTS8C / 14G数据库MySQL 8.0.46默认隔离级别 REPEATABLE-READbinlogROW 格式binlog_row_image FULLperformance_schema开启data_locks 数据来源测试库表test.t(id int primary key, c int, d int, key(c))初始数据贯穿全文的标尺mysql select * from t order by id; ---------------- | id | c | d | ---------------- | 0 | 0 | 0 | | 5 | 5 | 5 | | 10 | 10 | 10 | | 15 | 15 | 15 | | 20 | 20 | 20 | | 25 | 25 | 25 | ----------------说明下文所有会话 A / 会话 B / 会话 C均为独立的mysql -uroot test交互连接阻塞语句发出后我们用一个独立观察连接执行show processlist/select ... from performance_schema.data_locks进行第三方视角的透视。三、锁的分类与兼容矩阵3.1 锁的分类ASCII 图MySQL 锁全景 │ ┌─────────────────┼─────────────────────┐ │ │ │ 【全局锁】 【表级锁】 【行级锁】(InnoDB) FTWRL lock tables record lock(记录锁) 只读全实例 read/write gap lock(间隙锁) MDL(元数据锁) next-key lock(记录间隙) (DDL 自动加) insert intention lock全局锁flush tables with read lockFTWRL让整个实例只读常用于全实例逻辑备份。表级锁lock tables ... read/write显式加以及MDLmetadata lock——任何 DDL、DML 都会自动加 MDL且读锁之间不互斥、读写互斥、写写互斥。行级锁InnoDB 才有由索引驱动。分为记录锁、间隙锁、next-key 锁、插入意向锁。3.2 行锁兼容矩阵同一行 / 同一间隙请求 \ 已持有记录 S记录 X间隙 S,GAP间隙 X,GAPnext-key Snext-key X记录 S兼容冲突兼容冲突兼容冲突记录 X冲突冲突冲突冲突冲突冲突间隙 S,GAP兼容冲突兼容兼容兼容兼容间隙 X,GAP冲突冲突兼容兼容冲突兼容next-key S兼容冲突兼容冲突兼容冲突next-key X冲突冲突冲突冲突冲突冲突关键记忆点间隙锁之间永远兼容多个事务可以同时锁住同一段间隙防止插入这正是防止幻读的设计记录 X 锁与任何锁都冲突所以两行都要改同一行必然排队。3.3 next-key lock 区间图RR 默认MySQL 在 RR 下对扫描到的索引记录默认加next-key lock 记录锁 它前面的间隙锁左开右闭(a, b]索引记录: 0 5 10 15 20 25 |------|------|------|------|------|------| next-key: (-∞,0] (0,5] (5,10] (10,15] (15,20] (20,25] (25,supremum]例如where id10命中记录 10加锁为记录锁 10 间隙 (10,15]等值查询一个不存在的值如 id7则退化成纯间隙锁 (5,10)因为 7 落在 (5,10) 之间没有记录可锁。四、多会话实操真实回显4.1 全局锁 FTWRL会话 A 加全局读锁会话 B 尝试写入被阻塞A: flush tables with read lock; Query OK, 0 rows affected (0.00 sec) B: insert into t values (30,30,30); 无返回明显阻塞 观察者 show processlist: Id User Command Time State Info 16 root Query 4 Waiting for global read lock insert into t values (30,30,30)A 释放后B 立刻完成A: unlock tables; Query OK, 0 rows affected (0.00 sec) B after unlock: Query OK, 1 row affected (4.40 sec) -- 注意这 4.40s 就是它苦等 FTWRL 的时间实战结论FTWRL一上全实例写入全卡。生产上做备份要么用--single-transactionInnoDB 一致性快照不加全局读锁要么用xtrabackup加了 FTWRL 务必有自动超时/unlock 兜底。4.2 表锁lock tables t readA: lock tables t read; Query OK, 0 rows affected (0.00 sec) A: insert into t values (30,30,30); ERROR 1099 (HY000): Table t was locked with a READ lock and cant be updated本会话对自己加的读锁表也不能写直接报错 1099。另一个会话 B 去写则被阻塞B: insert into t values (30,30,30); 阻塞 观察者 show processlist: Id User Command Time State Info 20 root Query 4 Waiting for table metadata lock insert into t values (30,30,30)注意在 MySQL 8 里lock tables read造成的写阻塞在 processlist 里显示的等待状态是Waiting for table metadata lock表锁底层也走 MDL 实现并不是老版本里的 “Waiting for table level lock”。这是真实回显别被旧书误导。4.3 MDL 元数据锁最隐蔽的慢查询元凶会话 A 开启事务并只做查询注意没有任何写/锁语句A: begin; select * from t where id0; ---------------- | id | c | d | ---------------- | 0 | 0 | 0 | ----------------此时会话 B 要加字段DDL被阻塞B: alter table t add column e int; 阻塞 会话 C 再做一次普通查询竟然也跟着阻塞了 C: select * from t where id5; 阻塞 观察者 show processlist: Id User Command Time State Info 24 root Query 8 Waiting for table metadata lock alter table t add column e int 25 root Query 4 Waiting for table metadata lock select * from t where id5A 一提交B、C 才先后放行A: commit; Query OK, 0 rows affected (0.00 sec) B after: Query OK, 0 rows affected (8.41 sec) -- 等了 8 秒 C after: 1 row in set (4.41 sec) -- C 排在 B 后面形成 MDL 队列实战结论长事务 任何 DDL 雪崩。A 的长事务一直拿着 MDL 读锁B 的 DDL 要 MDL 写锁被挡而 C 的普通查询也要 MDL 读锁——结果 C 并不和 A 冲突却要排在 B 后面等写锁释放MDL 队列按请求顺序 FIFO。所以线上明明只是查一下却卡住十有八九是某条长事务/未提交事务挡住了 DDL。定期select * from information_schema.innodb_trx揪出长事务是救命操作。五、行锁与两阶段锁协议InnoDB 的两阶段锁协议锁在需要时才加但直到事务提交/回滚才释放。这意味着把最可能造成冲突、最晚用到的锁尽量往后放能缩短持锁时间。会话 A 改 id5 但不提交会话 B 改同一行被阻塞直到innodb_lock_wait_timeout本机默认 50s实验特地设成 5sA: begin; update t set dd1 where id5; Query OK, 1 row affected (0.00 sec) B: begin; update t set dd1 where id5; 阻塞 5 秒后 B after ~5s: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionERROR 1205是等不到锁而超时不是死锁它不会自动回滚整个事务只是当前这条语句失败事务还开着——记得业务里捕获到 1205 要主动rollback。六、用 data_locks 透视间隙锁RR 级别4 个案例MySQL 8 的performance_schema.data_locks让我们能看见每把锁。观察语句selectengine_transaction_id,object_name,index_name,lock_type,lock_mode,lock_datafromperformance_schema.data_lockswhereobject_namet\G案例 1等值查询间隙锁查一个不存在的值 id7A: begin; update t set dd1 where id7; Query OK, 0 rows affected (0.00 sec) -- 0 行受影响因为 7 不存在 data_locksA 持有: engine_transaction_id: 2413 object_name: t index_name: PRIMARY lock_type: RECORD lock_mode: X,GAP -- 纯间隙锁 lock_data: 10 -- 锁住 (5,10) 这个间隙 验证B update id10已存在的记录【不阻塞】: B: update t set dd1 where id10; Query OK, 1 row affected (0.00 sec) 验证B insert id8落进间隙【阻塞】: B: insert into t values(8,8,8); 阻塞processlist 显示 update 状态、Infoinsert into t values(8,8,8) A 提交后 B 才完成: Query OK, 1 row affected (3.91 sec)解读对主键等值查不存在的行RR 下加的是间隙锁(5,10)用lock_data10表示这段间隙的右边界。它只挡往间隙里插不挡改已有的 10。这正是防止幻读的关键。案例 2非唯一索引等值c5加 S 锁A: begin; select id from t where c5 lock in share mode; ---- | id | ---- | 5 | ---- data_locksA 持有: engine_transaction_id: 419091093388504 index_name: c lock_type: RECORD lock_mode: S,GAP lock_data: 10, 10 -- 间隙 (5,10) engine_transaction_id: 419091093388504 index_name: c lock_type: RECORD lock_mode: S lock_data: 5, 5 -- 记录锁 c5同时覆盖其前面的间隙即 next-key (0,5]解读非唯一索引等值 S 锁加的是next-key 锁(0,5]记录锁 c5 间隙锁(5,10)。注意这里没有出现 PRIMARY 的锁——因为select id是覆盖索引索引 c 的叶子已经包含 id根本没回表所以主键上无锁。这个细节很多人会忽略。案例 3主键范围id10 and id11 for updateA: begin; select * from t where id10 and id11 for update; ---------------- | id | c | d | ---------------- | 10 | 10 | 10 | ---------------- data_locksA 持有: index_name: PRIMARY lock_type: RECORD lock_mode: X,GAP lock_data: 15 -- 间隙 (10,15] index_name: PRIMARY lock_type: RECORD lock_mode: X,REC_NOT_GAP lock_data: 10 -- 记录锁 10不含间隙解读命中唯一记录 10加记录锁 10 next-key 间隙 (10,15]注意不是 (10,20]因为id11扫描到 10 就停next-key 只到下一跳 15。案例 4幻读演示 —— RR 防幻读 vs RC 不防RR 下A(RR): begin; select * from t where id10 and id15 for update; ---------------- | id | c | d | ---------------- | 10 | 10 | 10 | ---------------- data_locks: 记录锁 10 间隙 (10,15] 同案例 3 形态 B: insert into t values(12,12,12); 阻塞processlist Stateupdate, Infoinsert into t values(12,12,12) A 提交后 B 才插入成功: Query OK, 1 row affected (3.91 sec)RC 下会话级set session transaction isolation level read committedA(RC): begin; select * from t where id10 and id15 for update; data_locksRC: index_name: PRIMARY lock_type: RECORD lock_mode: X,REC_NOT_GAP lock_data: 10 -- 只有记录锁 10没有间隙锁 B: insert into t values(12,12,12); Query OK, 1 row affected (0.00 sec) -- 直接成功幻读发生解读RR 靠next-key lock 锁住扫描区间阻止了别的事务往区间里插数据从而防住幻读而 RC 只锁已命中的记录、不加间隙锁于是 B 能插进 12 造成幻读。这也是为什么RC 下 binlog 必须用 ROW 格式——语句复制在 RC 下会因幻读导致主从不一致。七、死锁完整 LATEST DETECTED DEADLOCK 逐行解读构造死锁A 改 5、B 改 10然后 A 改 10等 B、B 改 5等 A形成环。A: begin; update t set dd1 where id5; - Query OK B: begin; update t set dd1 where id10; - Query OK A: update t set dd1 where id10; - 阻塞等 B 的 10 锁 B: update t set dd1 where id5; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction A: B 被回滚释放 10 锁后Query OK, 1 row affected (3.50 sec)show engine innodb status中真正的死锁现场实机原文逐行拆解LATEST DETECTED DEADLOCK ------------------------ 2026-07-29 11:51:02 137615651108544 *** (1) TRANSACTION: TRANSACTION 2377, ACTIVE 12 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1 MySQL thread id 31, OS thread handle 137615720371904, query id 127 localhost root updating update t set dd1 where id10 *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 2 page no 4 n bits 80 index PRIMARY of table test.t trx id 2377 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 4; hex 80000005; asc ;; -- 持有记录锁 id5 1: len 6; hex 000000000949; asc I;; 2: len 7; hex 010000011f0151; asc Q;; 3: len 4; hex 80000005; asc ;; 4: len 4; hex 80000006; asc ;; 5: SQL DEFAULT; *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2 page no 4 n bits 80 index PRIMARY of table test.t trx id 2377 lock_mode X locks rec but not gap waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 4; hex 8000000a; asc ;; -- 等待记录锁 id10 ... *** (2) TRANSACTION: TRANSACTION 2378, ACTIVE 7 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1 MySQL thread id 32, OS thread handle 137615592359616, query id 128 localhost root updating update t set dd1 where id5 *** (2) HOLDS THE LOCK(S): RECORD LOCKS ... index PRIMARY ... trx id 2378 lock_mode X locks rec but not gap Record lock ... hex 8000000a; asc ;; -- 持有记录锁 id10 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS ... trx id 2378 lock_mode X locks rec but not gap waiting Record lock ... hex 80000005; asc ;; -- 等待记录锁 id5逐行解读TRANSACTION 2377 ... update t set dd1 where id10事务 (1) 当前正在执行的语句是改 id10。(1) HOLDShex 80000005即十进制的5说明事务 (1) 手里握着 id5 的 X 记录锁它第一步update id5拿到的一直没释放。(1) WAITINGhex 8000000a10说明事务 (1)在等 id10 的 X 锁——而这把锁被事务 (2) 握着。TRANSACTION 2378 ... update t set dd1 where id5事务 (2) 当前语句是改 id5。(2) HOLDShex 8000000a10事务 (2)握着 id10 的 X 锁它第一步update id10拿到的。(2) WAITINGhex 800000055事务 (2)在等 id5 的 X 锁——而这把锁被事务 (1) 握着。闭环(1) 持有 5 等 10(2) 持有 10 等 5 → 互相等待 → 死锁。InnoDB 的innodb_deadlock_detectON本机默认开会立刻检测到环挑选回滚代价较小undo log entries 少 / 改动少的事务作为牺牲品这里牺牲了事务 (2)于是 B 收到ERROR 1213A 随后拿到 10 锁继续执行。八、踩坑清单FTWRL 忘 unlock全实例只读雪崩。备份优先--single-transaction。长事务挡 DDLMDL 队列会让后续所有查询跟着卡。监控information_schema.innodb_trx的trx_started。间隙锁导致的莫名阻塞RR 下for update会锁区间别以为只锁了那几行需要防止幻读又想减少锁冲突时评估能否降到 RC前提是 binlogROW。ERROR 1205 ≠ 死锁1205 是超时事务没回滚1213 是死锁牺牲品被回滚。业务都要兜底rollback/ 重试。覆盖索引不回表 → 主键无锁案例 2 已经证明select id from t where c5 lock in share mode主键上根本没有锁改主键别的列不会冲突。九、面试高频问答Q1MySQL 锁分几类全局锁FTWRL、表级锁lock tables / MDL、行级锁记录锁、间隙锁、next-key 锁、插入意向锁。InnoDB 行锁靠索引没走索引会退化为表锁全行扫描加 X 锁。Q2什么是 next-key lock为什么 RR 用它防幻读next-key lock 记录锁 它前面的间隙锁左开右闭(a, b]。RR 下扫描到哪些索引记录就给它们加 next-key 锁从而把满足条件的新插入也挡在区间外杜绝幻读。RC 下只加记录锁、不加间隙锁因此会出现幻读。Q3间隙锁之间兼容吗兼容。多个事务可以同时持有同一段间隙的间隙锁目的是共同阻止插入而不是互相排斥。冲突主要来自记录 X 锁。Q4MDL 为什么会让普通查询也卡住MDL 读写互斥、写写互斥且按请求顺序排队FIFO。一条长事务拿着 MDL 读锁时DDL 的写锁请求会排队而后续普通查询的读锁请求也需要排在写锁请求之后于是看似只读的查询也跟着卡。Q5ERROR 1205 和 1213 区别1205 是锁等待超时innodb_lock_wait_timeout语句失败但事务仍开1213 是死锁牺牲品事务被回滚。两者业务层都要做好重试点。Q6怎么快速定位死锁原因立刻show engine innodb status看LATEST DETECTED DEADLOCK每个 TRANSACTION 的HOLDS与WAITING FOR列出各自持锁/等锁的记录hex 是主键值据此画出等待环即可定位是哪两条 SQL、哪两行互相卡死也可开innodb_print_all_deadlocksON把每次死锁写进错误日志。Q7如何降低死锁概率固定访问顺序所有事务按同一顺序改多行、缩短事务、降低隔离级别到 RC减少间隙锁、给查询走合适的索引避免锁放大。九、扩展阅读把锁问题变成可观测的指标光会看现场还不够DBA 的真正功力是在锁变成事故之前就发现它。下面几组查询建议熟记。9.1 谁在等、等谁的锁替代肉眼刷 processlistMySQL 8 提供了sys.innodb_lock_waits和performance_schema.data_lock_waits一行 SQL 就能画出等待链selectwaiting_trx_id,waiting_pid,blocking_trx_id,blocking_pid,wait_age,locked_table,locked_index,locked_typefromsys.innodb_lock_waits\G它的价值在于不用两台机器来回比对 processlist 的 HOLDS/WAITING直接告诉你阻塞者进程号运维脚本拿到blocking_pid后甚至能自动kill掉长时间阻塞的源头当然要先确认那是安全可杀的查询而不是一个核心写事务。9.2 当前所有持锁事务清单selecttrx_id,trx_state,trx_started,trx_wait_started,trx_rows_locked,trx_rows_modified,trx_mysql_thread_idfrominformation_schema.innodb_trxorderbytrx_startedasc;trx_started越早、却一直trx_stateRUNNING的事务就是最可疑的长事务持锁源——它不提交后面所有需要它持有锁的语句都得排队。线上建议对timediff(now(), trx_started) 10的事务告警。9.3 锁等待超时与死锁的监控innodb_lock_wait_timeout本机默认 50s单条语句等锁的最长时间超时即ERROR 1205。业务敏感场景可调小如 3~5s让失败快返回。innodb_deadlock_detect ON开启后 InnoDB 用等待图实时检测死锁一旦成环立刻挑牺牲品。关掉它能减少高并发下的检测开销但代价是死锁要等到锁等待超时才能解开得不偿失一般保持开启。想事后复盘所有死锁设innodb_print_all_deadlocks ON每次死锁都会写进错误日志而不只是保留最近一次到show engine innodb status。9.4 一个容易被忽略的合规点账号与密码本文所有命令都用的mysql -uroot操作系统用户直连云上 Ubuntu 默认 root 走 socket 认证无需密码但生产环境请务必① 禁用 root 远程登录② 业务用最小权限的专属账号③ 本地脚本若必须存密码用~/.my.cnf且权限0600切勿把密码写进博客、脚本注释或提交到代码仓库。本文严格遵循此原则正文中不出现任何真实口令。9.5 隔离级别与锁的联动小结隔离级别普通 SELECTfor update / 锁读间隙锁幻读READ UNCOMMITTED无锁读未提交记录锁无有READ COMMITTED快照读无锁仅记录锁无有REPEATABLE READ默认快照读无锁next-key lock有无靠间隙锁防住SERIALIZABLE隐式加共享锁共享 next-key有无记住间隙锁只在 RR/SERIALIZABLE 存在RC 下彻底消失——这正是案例 4 里 RR 阻止插入、RC 允许插入的根本原因也是RC binlogROW成为很多互联网公司默认组合的理由并发高、锁少、主从一致靠 ROW 保证。九、补新人最容易踩的五个锁误区很多同学背得熟RR 防幻读一上线照样被锁坑根子在于把概念当成了直觉。下面五个误区都是真实血泪。误区一以为只读查询一定不加锁。普通SELECT在 RR 下是快照读MVCC确实不加锁但一旦写成SELECT ... LOCK IN SHARE MODE或FOR UPDATE立刻变成当前读并加锁。更隐蔽的是update/delete的WHERE条件无论怎么写执行时都要先当前读定位行于是自动加记录锁/间隙锁。所以我明明只是查这句话在排错时常常不成立——要看的是它到底是不是当前读。误区二以为间隙锁只锁住那一行。间隙锁锁的是一段区间比如案例 1 里update ... where id77 不存在锁的是(5,10)这段开区间所有落在里面的插入id6/7/8/9统统被挡。很多明明没改这行却插不进去的诡异阻塞源头就是某条for update把一段间隙悄悄锁死了。误区三以为 RC 和 RR 只是会不会脏读的区别。对开发最直观的差异其实是间隙锁RR 有、RC 没有。同一个范围for updateRR 下防住幻读别的事务插不进来RC 下却放任插入导致幻读。不少团队为了提升并发把隔离级别降到 RC却没意识到这等于放弃了间隙锁保护必须靠 binlogROW 来兜底主从一致——这一步漏掉就是数据不一致。误区四看到Waiting for table metadata lock就以为是表被锁了。不是。这是 MDL元数据锁等待罪魁往往是一条还没提交的长事务稳稳拿着 MDL 读锁挡住了后面的 DDL 写锁而后续普通查询又被 DDL 挡在后面排队。看到这个状态第一反应应该是去information_schema.innodb_trx里揪最老的那个 RUNNING 事务而不是去 kill 那个 DDL。误区五死锁后无脑重试。应用捕获到 1213 直接原地重试结果因为两事务仍按相反顺序抢锁重试又死锁。正确的做法是重试加随机退避、缩短事务、并尽量让所有调用方按同一顺序访问多行。死锁本身不可怕InnoDB 会帮你挑一个牺牲可怕的是重试风暴把数据库 CPU 打满。锁问题排查 SOP建议贴在工位show processlist找State含Waiting for ... lock的线程记下Id与Infoselect ... from performance_schema.data_locks看谁HOLDS、谁WAITING把lock_data的 hex 翻成主键值information_schema.innodb_trx找持锁时间最长的事务判断是不是长事务/未提交事务必要时kill query id停语句或kill id端连接kill 前务必确认User/Info不是核心写链路死锁看show engine innodb status的LATEST DETECTED DEADLOCK按本文第七节的持锁/等锁画法还原等待环。把这套 SOP 练成本能线上锁问题从两眼一抹黑变成照方抓药平均止损时间能从几十分钟压到几分钟。十、总结MySQL 的锁体系看似繁杂但抓住全局 → 表(MDL) → 行(记录/间隙/next-key)三层再记住RR 靠 next-key 防幻读、两阶段锁协议延迟释放、死锁靠等待环检测三条主线就能把绝大多数锁问题讲清楚、查明白。真正的功力在于用data_locks和show engine innodb status把抽象锁具象化——本文所有回显均来自真实云服务器建议你在自己的环境把上面 4 个间隙锁案例亲手跑一遍把lock_data的 hex 值翻译成主键值那一刻锁就从黑盒变成了透明。本文实验均在真实云服务器完成输出为实机回显。