★ FEATURED ARTICLE 当你在执行一条SELECT ... FOR UPDATE或者LOCK TABLES语句时有没有想过后台的锁到底是怎么互相牵制的MySQL 表锁 X S IS IX 的兼容性这个问题我在刚做数据库运维时也翻了很久文档那时候总觉得这只是一张矩阵图背下来就完事了。直到线上出现过一次“表锁导致全部写入阻塞”的事故我才意识到这张矩阵里藏着的是 InnoDB 并发控制机制的核心逻辑。这篇文章就专门把X、S、IS、IX四种表级锁的兼容性拆开聊。不背口诀不列抽象表格了事而是把每一种锁背后的意图、它们之间为什么兼容或不兼容的底层原因讲清楚再附上可以直接在 MySQL 客户端里复现的实操过程。适合正在学习 MySQL 锁机制的开发者、刚接触数据库调优的运维人员以及那些被线上锁等待和死锁困扰想彻底搞懂原理的工程师。1. 先把四种锁的身份和用途对齐1.1 表锁和行锁之间为什么需要“意向锁”这种中间人InnoDB 和 MyISAM 一个很大的区别就是 InnoDB 默认支持行级锁。注意“默认”两个字——这并不意味着 InnoDB 完全抛弃了表级锁恰恰相反InnoDB 的表级锁依然存在而且每一行的行锁获取之前都必须先取得一个对应的表级“意向锁”。用一个生活化的例子来理解。假设整张表是一栋楼表级锁是对整栋楼的禁令比如“整栋楼今天禁止任何人进入”X 锁或者“整栋楼允许所有人进入参观但不能带走任何东西”S 锁。行级锁则是针对某一间具体房间的限制比如“房间 302 正在被某人使用”其他人不能进这间房但其他房间不受影响。现在问题来了如果事务 A 想锁住 302 房间加行级 X 锁事务 B 想知道这栋楼当前能不能整栋参观申请表级 S 锁B 如果一间一间去检查 302 有没有锁效率就太低了。意向锁就是解决这个问题的“门卫登记册”——事务 A 在给 302 加行锁之前先在大堂登记一笔“我要对整栋楼有排他意图”IX 锁事务 B 一看登记册上有 IX立刻就知道楼里有房间正在被独占不适合整栋参观于是 B 的表级 S 锁请求就会被阻塞。整个过程不需要 B 真正去查每一个房间这就是意向锁存在的意义。1.2 四种锁的“人设”谁在什么时候会拿到它们先明确一件事IS、IX从严格意义上来讲是“意向锁”它们本身不直接限制数据访问它们的作用是告知系统“我这个事务接下来打算做什么级别的操作”。MySQL 官方文档把它们归类为表级锁是因为它们确实是加在表这个层级上的标记。在一些数据库理论的教材里也会把它们称作“Intention Lock”我们可以理解成“预约标记”。S 锁Shared Lock共享锁强调“只读共享”。事务拿到了表级 S 锁意味着它可以读取整张表的数据其他事务也可以继续拿 S 锁做读取但谁都不能拿 X 锁来写。X 锁Exclusive Lock排他锁强调“独占写”。事务拿到表级 X 锁后这张表在当前事务结束前其他事务既不能读也不能写完全独占。IS 锁Intention Shared意向共享锁事务计划对表中某些行加 S 锁。它只表明“我要读某些行”但并不真正限制别的表级读操作。IX 锁Intention Exclusive意向排他锁事务计划对表中某些行加 X 锁。它只表明“我要写某些行”并不真正限制别的表级读操作但表级 S 锁和 X 锁都会对它说不。具体到获取场景锁类型典型获取语句真实意图ISSELECT ... LOCK IN SHARE MODE或SELECT ... FOR SHARE对某些行加 S 行锁前先在表上登记“我要共享读”IXINSERT、UPDATE、DELETE或SELECT ... FOR UPDATE对某些行加 X 行锁前先在表上登记“我要独占写”SLOCK TABLES t READ整表共享读禁止其他事务写XLOCK TABLES t WRITE整表独占禁止其他事务读写这里要特别提醒一个很多初学者误解的点平时执行的普通SELECT是不会加锁的它走的是 MVCC多版本并发控制的快照读不加任何表锁和行锁。只有显式地使用LOCK IN SHARE MODE、FOR UPDATE、LOCK TABLES或者执行写语句时才会真正去申请这些锁。所以别再以为“查询语句就会拿 S 锁”那是错误印象。2. 兼容性矩阵逐条拆解为什么有的锁互相“让路”有的非要“抬杠”2.1 先看标准矩阵再谈记忆技巧MySQL 官方对 InnoDB 表级锁兼容性矩阵的定义如下Y表示兼容N表示不兼容。锁类型ISIXSXISYYYNIXYYNNSYNYNXNNNN第一次看这张表最容易记住的是最后一行X跟所有锁都不兼容。这一条几乎是直觉排他锁本身就是“我来了谁都别想动”。最难记住、也最容易出问题的是中间的几个单元格IX和IS居然互相兼容IX和S却不兼容。要理解这一点需要回到它们的真实语义。IS常说“我要读”但它只打算读某些行并不代表它要锁住整张表所以另一个事务说“我也要读”IS当然没问题。哪怕另一个事务说“我要写某些行”IX只要大家写/读的不是同一行也没必要在表级别就把别人拦住。因此IS和IX互相兼容。但是当一个事务持有表级S锁它已经明确表达了“整张表我要以共享模式保护起来”。此时另一个事务如果申请IX意味着它马上要改表中的某些行这明显和“整表共享读保护”冲突所以S与IX不兼容。IS与X为什么不兼容X锁一旦加上代表表已经处于独占状态任何想进来看几行的事务都必须等待。所以哪怕事务只持有IS一碰到X也得歇着。记忆技巧我自己总结了一个先背“两对绝对不兼容”——S 与 X、X 与一切再背“两个特殊不兼容”——S 与 IX、IS 与 X。剩下来的IS 与 IS、IS 与 IX、IX 与 IX、S 与 S全是兼容这样一张矩阵就拆完了。2.2 你最容易踩的坑“S 与 IX 不兼容”引发的阻塞实际工作里S锁与IX锁的冲突是最容易忽视的。比如你用mysqldump做逻辑备份时如果没有开启--single-transaction某些版本的默认行为会执行LOCK TABLES ... READ这就是一个明确的表级 S 锁。一旦这个 S 锁停留在表上所有业务里的UPDATE、INSERT甚至SELECT ... FOR UPDATE都会因为拿不到 IX 锁而阻塞呈现出“整个库的写操作全部卡住只有普通读还活着”的诡异现象。我曾经处理过一起线上事故现象就是某张订单表通过备份工具连续执行了近 40 秒的读锁结果下游所有写接口超时。当时我第一反应是查死锁日志结果发现根本没有死锁而是LOCK TABLES的 S 锁在与 IX 锁互斥。解决思路也很清晰优先使用mysqldump --single-transaction --skip-lock-tables让备份走 MVCC 快照读避免 S 表锁长时间驻留如果必须使用LOCK TABLES就把锁的时间窗口压缩到极致并提前评估好业务容忍度。2.3 为什么IX和IX可以兼容这跟死锁有什么关系很多人刚接触矩阵时看到IX和IX兼容觉得不可思议两个事务都说自己要写怎么会兼容这里要厘清一个关键概念IX是表级意向锁它的兼容只代表“表级别允许两个写事务同时存在”但真正写数据时两个事务会各自去拿对应行上的X行锁而同一行上的两个X行锁必然是不兼容的。所以表级兼容不等于数据不冲突更不等于不会死锁。举一个实际场景。事务 A 想更新表 t1 的第 1 行和第 2 行事务 B 想更新表 t1 的第 2 行和第 1 行。两个事务都能顺利拿到表级IX锁因为它们兼容但当事务 A 锁住第 1 行后申请第 2 行事务 B 可能正锁着第 2 行申请第 1 行这就形成了循环等待InnoDB 会检测到死锁回滚其中一个事务。所以每次死锁日志里出现IX和IX时别误以为“既然兼容怎么还会死锁”真正交战的地方在行锁层面。3. 实操现场30 分钟亲手验证锁的兼容行为3.1 准备一个最小化测试环境我建议用两个 mysql 客户端窗口来模拟两个事务这样能直观地看到互相阻塞的过程。建表语句很简单CREATE DATABASE IF NOT EXISTS locktest; USE locktest; CREATE TABLE t_user ( id INT PRIMARY KEY, name VARCHAR(50), age INT, KEY idx_age (age) ) ENGINEInnoDB; INSERT INTO t_user VALUES (1, aaa, 20), (2, bbb, 30), (3, ccc, 40);执行完初始化后分别开两个会话会话 A 和会话 B。所有验证都把autocommit关闭这样可以让锁保持住方便观察。SET autocommit 0;3.2 场景一验证S 与 IX不兼容会话 A 执行LOCK TABLES t_user READ;现在会话 A 持有表级 S 锁。切换会话 B 尝试更新UPDATE t_user SET age 21 WHERE id 1;你会发现这条 UPDATE 一直处于阻塞状态。原因就是会话 B 在更新任意一行之前需要先为表加上IX意向锁而当前表上已有会话 A 的S锁两者不兼容。在会话 B 中再开一个查询确认阻塞来源SELECT * FROM performance_schema.data_lock_waits\G或者用更直观的 sys 库视图SELECT * FROM sys.innodb_lock_waits\G能看到等待的锁类型、持有的锁类型以及对应的 SQL。测试完成后回会话 A 执行UNLOCK TABLES;会话 B 的 UPDATE 会瞬间完成。这个小小的操作足以让你直观感受到S锁与IX锁冲突的破坏力。3.3 场景二验证X 与一切不兼容会话 A 执行LOCK TABLES t_user WRITE;此时表上持有 X 锁。换到会话 B无论执行SELECT * FROM t_user;还是执行UPDATE t_user SET age 22 WHERE id 2;都会阻塞。因为 X 锁与 S 锁、IS 锁、IX 锁都不兼容。但这里有个非常迷惑的细节如果你在会话 B 执行的是普通SELECT它走的是快照读还是当前读决定它会不会被阻塞。SELECT * FROM t_user默认是快照读理论上不会被表锁阻塞但实测中只要表上有LOCK TABLES ... WRITE这个普通查询也会被拦住因为LOCK TABLES会同时关闭该表的 MVCC 快照读能力让所有访问都必须走当前读。这个点值得在脑子里留个印象LOCK TABLES 不是普通行锁它的行为更接近 MyISAM 风格的整表锁。解锁UNLOCK TABLES;再验证一下FOR UPDATE与FOR SHARE的意向锁行为。比如会话 A 执行SELECT * FROM t_user WHERE id 1 FOR UPDATE;会话 B 执行SELECT * FROM t_user WHERE id 1 LOCK IN SHARE MODE;这条会被阻塞因为会话 A 拿到了行级 X 锁和表级 IX 锁会话 B 想为同一行加 S 行锁必须先在该表上登记 IS 锁。虽然表级IX与IS兼容但两者都要继续申请同一行上的行锁而同一行的X行锁与S行锁不兼容所以最终会阻塞。3.4 利用系统表观测锁等待细节排查锁等待时我习惯用这三把“尺子”-- 查看当前所有事务 SELECT * FROM information_schema.innodb_trx\G -- 查看当前锁等待关系 SELECT * FROM performance_schema.data_lock_waits\G -- 使用 sys 库的汇总视图最关键 SELECT * FROM sys.innodb_lock_waits\Gsys.innodb_lock_waits会直接给出哪张表、哪个事务在等待、持锁事务的 trx_id、等待了多少秒非常省力。如果线上环境没有 sys 库那就用performance_schema.data_lock_waits左连接data_locks自己拼大部分字段都能对得上。4. 线上场景实战三个高频故障的定位与处置4.1 场景一LOCK TABLES读锁引发的“全局写阻塞”故障表现某张核心表的写入接口全部超时但查询接口正常。SHOW PROCESSLIST里能看到大量UPDATE处于Waiting for table metadata lock或者Waiting for table level lock状态。排查方法执行SHOW ENGINE INNODB STATUS\G在TRANSACTIONS段落中定位持有表锁的事务。常见根源有两个一是备份脚本里使用了LOCK TABLES二是某个长事务执行过LOCK TABLES后既没有COMMIT也没有UNLOCK TABLES。如果是备份脚本立刻终止备份会话并解锁如果是应用的长事务评估是否可以安全KILL。实操心得遇到这种故障千万别上来就KILL持锁事务先确认事务已经空闲多久。如果事务只是正在执行一个很大的写操作强杀可能导致部分回滚对主从延迟影响很大。稳妥做法是先查sys.innodb_lock_waits拿到阻塞源头再用KILL trx_id精准处理。4.2 场景二ALTER TABLE与元数据锁的混杂有人会问ALTER TABLE拿到的不是 X 表锁吗为什么SHOW PROCESSLIST里显示的是Waiting for table metadata lock这里必须区分两个概念InnoDB 的锁矩阵管的是行锁与表锁的兼容性而metadata lockMDL是 MySQL Server 层维护的另一种锁负责保护表结构定义。ALTER TABLE在执行前和结束前都会请求 MDL 写锁如果此时有一个事务在事务中执行过查询但迟迟未提交它持有的 MDL 读锁就会阻塞ALTER TABLE。处理思路不能套用表锁矩阵。要查performance_schema.metadata_locks表找到持有 MDL 读锁的会话然后让它提交或回滚。这一步很多新人会比较疑惑因为从锁矩阵看不太容易直接解释清楚 MDL 的行为。可以把 MDL 理解成“结构层的门禁”它和 InnoDB 表锁是两个体系排查时要分开看。4.3 场景三一读一写交叉时的死锁定位死锁日志怎么读先看LATEST DETECTED DEADLOCK段。比如日志里出现Transaction A: (1) LOCK GRANTED: S, RECORD, t_user id1 (2) WAITING FOR: X, RECORD, t_user id2 Transaction B: (1) LOCK GRANTED: S, RECORD, t_user id2 (2) WAITING FOR: X, RECORD, t_user id1 WE ROLL BACK TRANSACTION A这说明两个事务各自拿到了不同行的共享锁又想升级成排他锁导致互相等待。这类问题的本质是“先共享读、后独占写”的锁升级路径。日常编码时如果判断某行数据一定会被更新就直接用SELECT ... FOR UPDATE一步到位不要先SELECT再UPDATE这样可以减少锁状态转换带来的死锁概率。针对锁等待超时还要重视一个参数innodb_lock_wait_timeout 50单位是秒默认值可能是 50。如果线上经常出现Lock wait timeout exceeded不要盲目调大它。调大只会让更多请求堆积在锁后面反而拖垮整个实例。更合理的做法是通过监控找出锁等待源头从源头治理事务时长而不是延长等待时间。4.4 兼容性速查与故障对照表把今天讨论的内容压缩成一张速查表可以直接放进自己的运维笔记里场景持有锁申请锁表现处置方向备份读锁SIX写入阻塞改用 MVCC 备份或缩短锁窗口整表写锁XS/IS/IX读写全阻塞排查持锁事务评估 KILL行写并发IXIX行锁冲突/死锁统一加锁顺序缩短事务范围结构变更MDL SMDL XALTER 等待定位未提交事务并处理锁升级死锁SX死锁回滚使用FOR UPDATE避免两阶段锁我个人在实际项目里还有一个习惯凡是涉及批量更新或者后台任务都会在事务开头打印当前会话trx_id这样一旦后续发生锁等待我能快速通过sys.innodb_lock_waits把事务和代码请求对应起来不用再去翻应用日志猜是谁发起的 SQL。4.5 最后分享一个容易忽略的细节引入意向锁后还要注意AUTO_INCREMENT表级锁和INSERT意向锁的区分。严格来说 InnoDB 里还有一种INSERT INTENTION锁它不是矩阵里的 IX 锁而是在INSERT场景中使用的一种特殊间隙锁标志。它和普通 IX 锁的区别在于它表示“事务想要在某个间隙中插入”遇到其他事务持有的Gap锁时会等待。这个锁类型不在我们讨论的四类表级锁矩阵里但在死锁日志里经常见到初次接触容易混淆。记住关键词矩阵里写的是 X/S/IS/IX日志里的INSERT INTENTION是间隙锁的补充机制两者不能混为一谈。踩过几次坑之后我现在看任何锁相关的问题都会先回答三个问题事务用的是哪级隔离级别语句走的是 MVCC 快照读还是当前读请求的锁是表级、行级还是间隙级把这三件事理清楚再看兼容性矩阵很多阻塞问题的答案就自己浮出来了。
阅读完成 · 觉得有帮助？
