MySQL锁机制全解：全局锁、表级锁、行级锁与死锁排查实战

做后端开发这几年我踩过最多的坑几乎都在数据库并发这一层。库存扣成负数、订单重复创建、报表查着查着卡死最后追根溯源全是锁在作怪。MySQL锁的分类跟具体作用听起来是基础中的基础可真到线上出问题时能把人绕晕的恰恰是这些基础概念。今天这篇我想把MySQL锁的全家桶一次讲透——从全局锁到行锁从记录锁到间隙锁每种锁为什么存在、什么时候生效、怎么观察用实际经验把链路串起来。适合刚接触数据库并发控制的新手也适合写了不少SQL但一直对锁“只可意会”的开发者。1. 锁的体系概览先分清“锁的类型”和“锁的模式”很多人一开始学MySQL锁就懵是因为把两个完全不同的维度混在一起了。一个锁既可以说它是“行级锁”也可以说它是“共享锁”。“行级”说的是锁的粒度也就是它锁住的范围有多大“共享”说的是锁的模式也就是拿到锁之后能做什么操作。这俩就像“房间的大小”和“房间的门禁等级”不是一回事。1.1 为什么谈到锁必须先分两个维度从粒度看MySQL锁主要分成三类全局锁、表级锁、行级锁。全局锁锁整个实例表级锁锁某张表行级锁锁某几行记录。粒度越细并发度越高但加锁的复杂度也越高。从模式看主要有共享锁S锁和排他锁X锁两种。共享锁之间可以兼容大家都可以读排他锁和任何锁都不兼容谁拿了排他锁别人想读想写都得等。这两个维度交叉组合就是你在SHOW ENGINE INNODB STATUS里看到的那些锁信息。比如LOCK_MODE字段里写着X、S、X,GAP等其中X、S是模式GAP是间隙锁标记REC_NOT_GAP代表记录锁。还有一个容易被忽略的点锁是加在索引上的不是直接加在数据行上的。InnoDB的聚簇索引结构决定了如果一条SQL没走索引行锁就会升级成表锁——不是MySQL故意锁表而是它不知道要控制哪些行只能全表扫描干脆把整个索引都锁了。这也是线上慢SQL导致并发暴跌的常见原因。1.2 最基础的分类全局锁、表级锁、行级锁三者的核心区别在于“锁的影响范围”和“使用场景”。锁类型锁粒度影响范围典型使用场景并发影响全局锁整个实例所有库所有表全库逻辑备份全部阻塞表级锁单张表该表所有记录DDL变更、表维护该表所有读写阻塞行级锁单行/多行命中的索引记录高并发OLTP只阻塞相关行这里我要多说一句很多文章把表锁和MDL锁混在一起说其实不一样。表锁是显式用LOCK TABLES命令加的MDL锁是MySQL自动加的元数据锁两者都会锁表但触发方式完全不同。后面第三部分我会专门展开MDL锁。行级锁是InnoDB的看家本领但它不是只锁一行那么简单。在RR可重复读隔离级别下行锁还会配合间隙锁、临键锁一起工作防止幻读。这部分是MySQL锁体系里最精华的地方也是我们后面会花最多篇幅讲的东西。2. 全局锁一次性冻住整库全局锁是MySQL锁体系里最“暴力”的一种。它一出手整个实例的写操作全部停摆所有表的INSERT、UPDATE、DELETE、DDL都会被阻塞只允许读。命令就一行FLUSH TABLES WITH READ LOCK;执行这条命令后超级权限的账号可以拿到一个全局读锁Global Read Lock。释放方式也很简单UNLOCK TABLES;但这里有一个纪律性要求FLUSH TABLES WITH READ LOCK下文简称FTWRL必须配合UNLOCK TABLES成对使用如果连接意外断开MySQL也会在连接结束时自动释放这个锁。你如果在命令行直接敲FTWRL然后忘了UNLOCK TABLES这个会话就会一直握着全局锁整个库的写入就这么被卡死了。我见过不止一次因为某人在生产环境执行完FTWRL后直接关终端导致线上写入全hang。最后只能从另外的会话KILL掉那个连接来救场。2.1 全局锁是什么、怎么用FTWRL做了什么它做了两件事一是把内存中的所有表都加上表级读锁二是关闭了所有正在进行的写事务。效果就是整库进入“只读快照”状态。为什么需要这个锁最常见的场景是全量逻辑备份。比如用mysqldump时如果你不指定--single-transaction参数默认就会先执行FTWRL拿到一个一致性的备份起点。这个起点保证了从备份开始的瞬间所有数据不会因为并发写入而出现前后不一致避免备份文件里出现“一张订单的明细和它的主记录不在同一个状态”这种诡异情况。2.2 全局锁的最佳实践备份场景现在大家用的主流备份方式是mysqldump --single-transaction。这个参数的核心思想是利用InnoDB的MVCC机制在可重复读隔离级别下开启一个长事务通过undo log读取历史版本而不是锁库。但这里有两个大前提第一所有表都必须是InnoDB如果有MyISAM表--single-transaction就不生效第二备份期间如果遇到DDL事务会报错因为DDL会破坏事务的一致性快照。所以如果是全InnoDB库优先用--single-transaction不要用FTWRL。如果库里混着MyISAM表FWTWL反而是最稳妥又最简单的方案。我的习惯是小库直接FTWRL几秒内完成大库用--single-transaction再另外用工具校验备份一致性。注意全局锁的代价是“整个实例的写全部阻塞”生产环境能用MVCC方案就尽量别用FTWRL。一旦真的用了务必设置lock_wait_timeout别让业务无限期等待。3. 表级锁锁住整张表的三种形态表级锁在InnoDB时代被很多人忽视但它并没有消失。至少有三个场景你一定会碰到表级锁显式LOCK TABLES、MDL锁、以及InnoDB的意向锁。前两个是“真正的表锁”第三个是表锁和行锁之间的协调机制。3.1 表锁LOCK TABLESLOCK TABLES是显式加表锁的命令LOCK TABLES orders READ; -- 或 LOCK TABLES orders WRITE;加读锁后当前会话和所有其他会话都只能读这张表不能写加写锁后只有当前会话能读写该表其他会话的读写全部被阻塞。用完别忘了UNLOCK TABLES;释放。什么时候会用到它说实话在OLTP场景里基本用不到。它更多用于一些特殊的数据操作比如用LOAD DATA批量导数据时先手动加个写锁避免其他事务插入或者某些老项目里用LOCK TABLES代替复杂的事务控制。如果你用的是InnoDB并开了事务千万不要去用LOCK TABLES——它会锁定表但不会受到事务提交的自然释放特别容易造成“锁已释放事务还没结束”的错乱。3.2 元数据锁MDL——最容易被忽略的表级锁MDL锁是MySQL 5.5以后引入的用来保护表的元数据防止一个事务在查询/更新某张表时另一个事务把这张表的结构给改了。它最烦人的地方在于它是自动加的你感知不到它除非它堵了。我在实际运维中遇到过经典事故业务高峰期跑一个ALTER TABLE语句想加索引结果这个DDL堵了。为什么因为MDL锁有“读锁”和“写锁”两种普通的SELECT、UPDATE等语句会拿到MDL读锁大家共享没问题但ALTER TABLE要拿MDL写锁写锁必须等所有读锁释放。如果有一个长事务一直开着不提交它手里的MDL读锁就永远不释放ALTER TABLE只能一直等。更恐怖的是这个等待的DDL还会反过来阻塞后续所有对该表的读写——因为后续请求也要申请MDL读锁但发现“已经有别人在等写锁”了排队规则会让它们也一起等着。这就是所谓的**“DDL一行表上全hang”**。排查方法很简单SELECT * FROM performance_schema.metadata_locks;这会显示所有MDL锁的持有和等待关系。看到OWNER_THREAD_ID对应的线程在长时间运行基本就定位到长事务了。解决思路也很明确先在业务侧停掉挂起的DDLKILL掉长时间事务或者等等它提交再重试DDL。更优雅的做法是用pt-osc或gh-ost这类在线变更工具但即使在线工具也必须在等待MDL写锁的瞬间能拿到机会所以还是要尽量避开业务高峰。3.3 意向锁表级锁和行级锁的桥梁现在说InnoDB的意向锁。它虽然挂在表上但实际作用是为了协调“事务A锁住了某行”和“事务B想锁住整张表”之间的冲突判断。假设事务A在orders表的第100行加了行级排他锁那么事务A在加这个行锁之前必须先给orders表加一个意向排他锁IX。事务B想对整表加写锁时只需看一眼表上有没有IX锁有就知道“有人在动里面的行”立刻阻塞而不必逐行去判断。这个机制的本质是用一把表级“旗子”告诉别人里面的行已经被行锁覆盖了。意向锁分两种意向共享锁IS事务准备给某些行加共享锁。意向排他锁IX事务准备给某些行加排他锁。它们之间的兼容性规则是IS和IX互相兼容因为它们只是“预告”具体冲突还是得看行锁。但任何意向锁和表级独占锁LOCK TABLES WRITE都不兼容。理解意向锁对排查问题非常有帮助。当你看SHOW ENGINE INNODB STATUS时事务状态里会出现类似TABLE LOCK的意向锁记录如果看到多个事务都在等同一个TABLE LOCK的X锁说明有人在整表范围内占用了锁资源。4. 行级锁InnoDB真正精细化的并发控制行级锁是InnoDB的核心竞争力。它不像表锁一刀切而是可以对索引记录加锁。但行级锁内部还分好几种记录锁、间隙锁、临键锁、插入意向锁。它们之间的关系和经验是面试常考、线上常错的重灾区。4.1 记录锁Record Lock记录锁是最“老实”的行锁直接锁住一条索引记录。它有三种存在方式共享记录锁S多个事务可以同时持有一行上的S锁主要用于普通一致性读不注意普通SELECT不走锁走MVCC。只有SELECT ... LOCK IN SHARE MODE或SELECT ... FOR SHARE才会加S锁。排他记录锁XUPDATE、DELETE、INSERT ... ON DUPLICATE KEY UPDATE等写操作加X锁。兼容性S和S兼容S和X不兼容X和X不兼容。例如-- 事务A SELECT * FROM orders WHERE id 100 FOR UPDATE; -- 事务B此时执行 UPDATE orders SET amount 200 WHERE id 100; -- 事务B会阻塞直到事务A提交或回滚。记录锁的前提是id必须走了唯一索引或主键并且等值命中。如果命中的不是唯一索引情况就复杂多了。4.2 间隙锁Gap Lock间隙锁锁的不是哪一行而是两个索引记录之间的间隙。它的作用只有一个防止“幻读”也就是防止其他事务在这个间隙里插入新记录。间隙锁是RR隔离级别下自动启用的RR隔离级别下MySQL默认用间隙锁临键锁来保证事务隔离性。举一个经典例子-- 事务A SELECT * FROM orders WHERE amount BETWEEN 100 AND 200 FOR UPDATE; -- 如果amount列有普通索引事务A锁住了amount100到200之间的所有记录以及之间所有的“间隙”。 -- 事务B此时想在这段间隙里插入一条amount150的订单 INSERT INTO orders (amount) VALUES (150); -- 事务B会被间隙锁阻塞。间隙锁有个很容易踩坑的性质它只阻塞插入不阻塞读取。也就是说间隙锁让别的事务“插不进东西”但别人还是能读到这些间隙里已有的数据也能加锁读到如果间隙里根本没有数据自然也就无所谓锁了。此外间隙锁之间可以共存因为它们的目的都是“禁止插入”互不冲突。但间隙锁带来的副作用也很明显它扩大了锁的范围降低了并发度。两个事务如果各持有一个间隙锁且间隙重叠一旦都去插入就可能形成死锁。后面第七部分我们会举这种死锁案例。4.3 临键锁Next-Key Lock临键锁是“记录锁间隙锁”的组合体。它不仅锁住记录本身还锁住这条记录前面那个间隙。为什么需要它为了严格防止幻读同时保证“在当前读”的范围内不出现新的符合条件记录。具体来说InnoDB默认的索引扫描加锁方式是Next-Key Lock。比如在amount索引上数据为(amount, id)(50,1)、(100,2)、(150,3)。执行SELECT ... WHERE amount 80 FOR UPDATE时加锁范围不仅仅是(100,2)和(150,3)这两条记录还包括(50,1)到(100,2)之间、(100,2)到(150,3)之间以及(150,3)到“正无穷”之间的间隙。因此RR级别下的范围查询锁的范围会被“放大”你本来只想要几行结果把整个区间都锁了。这就是很多并发更新场景里明明只改一行数据却和别的行更新冲突的原因。在RC读已提交隔离级别下Next-Key Lock会被优化掉只保留记录锁也是因为如此RC下没有间隙锁幻读可能发生。所以很多高并发业务宁可牺牲“幻读”的绝对安全也要把隔离级别设为RC来提高并发度就是这个道理。4.4 插入意向锁Insert Intention Lock它名字里带着“意向锁”但属于行级锁范畴准确说是“插入时的特殊间隙锁”。一个事务想往间隙里插入数据之前会先申请这个插入意向锁。它有两层意思它告诉其他事务“我准备在这个间隙里插入记录了但还没插。”它和已有的间隙锁互斥如果其他事务持有该间隙的间隙锁插入事务就必须等待。但要注意不同事务之间插入意向锁是互相兼容的只要插入的间隙不重叠或插入记录的位置不冲突它们可以同时插入到同一个间隙里的不同位置。这也是为什么多个并发插入同一张表不会互相全局阻塞而是只有间隙重叠的才阻塞。这个锁在死锁日志里出现的频率很高经常看到类似insert intention insert等待字样见到它基本可以确定现场有间隙锁正在阻止插入。5. 自增锁被低估的并发热点自增锁是InnoDB对自增列的一种特殊表级锁但它的行为和普通表锁不一样。很多人知道AUTO_INCREMENT指的是自动增长的列却不知道插入时的自增锁策略直接决定了并发插入的性能和顺序。这部分我想单独拿出来讲因为它太容易被忽略了。5.1 自增锁的工作机制InnoDB对AUTO_INCREMENT列维护一个计数器。当你有事务插入数据且没指定自增值时InnoDB需要给它分配一个新值。为了保证不重号它必须获取自增锁。自增锁的历史设计是每个INSERT语句都会持锁直到语句结束这是innodb_autoinc_lock_mode 0时的传统模式简单批量分配模式。这个模式的代价很大因为并发插入同一张表时其他插入语句会被这个自增锁阻塞。从MySQL 5.1以后默认模式变成了innodb_autoinc_lock_mode 1也就是连续模式Consecutive。在这种模式下对于INSERT ... VALUES (...),(...)...提前知道行数可以在拿到锁后批量分配连续的自增值分配完就释放锁不需要等整个语句结束。对于INSERT ... SELECT这种行数不确定的语句InnoDB无法提前知道需要多少个自增值只能退化为传统模式语句期间一直持锁。还有一个模式innodb_autoinc_lock_mode 2也叫交叉模式Interleaved它让所有自增锁都“即取即用”不必持锁到语句结束。这样并发插入互不阻塞但因为分配值的过程穿插可能导致自增值不是连续的。MySQL 8.0将默认模式从1改成了2同时基于自增对二进制日志格式的兼容性做了一些处理。5.2 lock_mode参数与批量插入我在生产环境处理过一个经典问题有个流水表用INSERT ... SELECT方式做数据迁移并行跑了几个线程结果发现自增锁把并发插入卡成了串行批量数据导入速度极慢。排查方法很简单SHOW VARIABLES LIKE innodb_autoinc_lock_mode;如果结果是2理论上应该已经是交叉模式但事务中还是有等待。后来发现瓶颈不在自增锁而是那个批量插入的SQL里带着一个ORDER BY子句导致InnoDB必须做完排序再插入排序期间持锁不释放。这里给几个实操建议对于高并发订单表、流水表尽量使用INSERT INTO ... VALUES多行插入不要用多行单独插入这样可以减少自增锁的申请次数。如果允许自增值不连续可以把innodb_autoinc_lock_mode2提升并发插入吞吐。但如果要确保主键单调递增且主从数据严格一致建议保持在1。使用INSERT ... SELECT时尽量分批次且控制批次大小避免因为持锁时间过长而拖垮下游写入。注意自增锁虽然是表级锁但它影响的是“插入语句”不影响表上的其他读写操作。所以它并不会像MDL那样一堵全堵但插入语句之间的互相等待依然可以形成明显的性能瓶颈。6. 加锁过程实战从一条UPDATE看锁的流转前面讲了很多锁的类型现在把它们串到一个真实的场景里去。假设我们有这样一张订单表CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, amount DECIMAL(12,2) NOT NULL, status TINYINT NOT NULL, order_no VARCHAR(64) NOT NULL, KEY idx_user_id (user_id), KEY idx_amount (amount) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;然后执行UPDATE orders SET status 2 WHERE user_id 100 AND amount 100;在InnoDB里这行更新经历的加锁过程大致如下。6.1 实际SQL加锁分析第一步优化器选择索引。如果user_id的筛选度更高就会用idx_user_id找到user_id100的所有索引记录然后逐条回表判断amount 100是否成立。假设这个条件下返回了100条记录那么这100条记录对应的聚簇索引主键记录都会被加上X锁。第二步由于amount上有普通索引idx_amount且SQL包含范围判断InnoDB在RR隔离级别下还会对idx_amount上命中的记录加记录锁和间隙锁。这里就非常微妙了如果查询优化器走的是idx_user_id那么amount上的间隙锁范围并不完全等同于“所有amount100的记录”而是在回表过程对应的聚簇索引记录上对amount索引上的每条记录分别加锁。所以分析加锁范围前必须明确实际执行计划不能只看WHERE条件。如果这条SQL走了idx_amount扫描那么会从amount的第一个大于100的索引记录开始对所有后续索引记录及其间隙加Next-Key Lock直到索引末尾。第三步如果user_id100 and amount100没有命中任何记录锁也不是空的。比如amount索引上存在amount50, amount90两条记录你查amount100一个都查不到但InnoDB会对amount90后面的那个间隙即90到正无穷加间隙锁。这意味着其他事务想插入任意一个amount90的记录都会被阻塞。这就是“查询没命中也能把范围锁死”的典型场景。6.2 现象与监控方法遇到锁等待应该怎么第一时间观察首选命令是SHOW ENGINE INNODB STATUS;看LATEST DETECTED DEADLOCK部分和TRANSACTIONS部分。重点关注锁等待关系比如WAITING FOR THIS LOCK TO BE GRANTED、HOLDS THE LOCK等信息。如果你想更细粒度地观察真实的加锁对象可以从两个视图出发SELECT * FROM performance_schema.data_locks; SELECT * FROM performance_schema.data_lock_waits;data_locks会显示每个事务持有哪些锁包括锁类型REC_NOT_GAP、GAP、INSERT_INTENTION、索引名称、锁模式等。我用过一个非常实用的排查方法找到WAITING状态的锁再看对应索引里锁的LOCK_DATA字段基本就知道是哪一行卡住了。举例查到一个等待的记录锁对应的LOCK_DATA是某个主键值比如400057那就直接去表里看看主键为400057的记录是什么再反查哪个事务把它锁住了。通过data_lock_waits可以把阻塞者和等待者串起来然后KILL掉持有锁的长事务或者等它提交。从这么多年的排查经验看加锁过程没有捷径必须结合执行计划、隔离级别、索引结构三个维度一起分析。平时在测试库可以多用EXPLAIN看执行计划然后人为制造并发事务观察锁状态这是理解MySQL锁最有效的方式。7. 常见问题与死锁排查实录最后一部分我把自己处理过的几个典型问题整理成清单每个都对应一个真实的“现场画面”。这些场景你在网上可能看到过类似描述但我这里给出的是亲测有效的排查思路。7.1 死锁是怎么产生的死锁的经典模型是“两个事务互相等对方的锁”。比如-- 事务A UPDATE orders SET status1 WHERE id100; UPDATE orders SET status1 WHERE id200; -- 事务B UPDATE orders SET status1 WHERE id200; UPDATE orders SET status1 WHERE id100;事务A先锁id100事务B先锁id200然后A要锁id200阻塞B要锁id100阻塞于是死锁。InnoDB不会无限等下去它会检测死锁并回滚其中一个事务通常是回滚代价较小的事务另一个事务成功。你会在应用日志里看到经典的报错Deadlock found when trying to get lock; try restarting transaction还有一种更隐蔽的死锁发生在间隙锁插入意向锁之间。比如事务A在某个间隙上持有间隙锁事务B也在同一个间隙上持有间隙锁间隙锁之间本来是兼容的但之后事务A想插入一条记录申请插入意向锁发现B持有间隙锁等待事务B也想插入记录申请插入意向锁发现A持有间隙锁等待。两个事务都拿着对方“可以兼容”的锁又都想要对方“不兼容”的新锁死锁就形成了。这类死锁在RR隔离级别下特别常见出现频率远高于两把记录锁互等的场景。7.2 排查死锁的步骤遇到死锁先别慌按下面四步走。第一步收集死锁日志。立刻执行SHOW ENGINE INNODB STATUS把LATEST DETECTED DEADLOCK段落完整复制出来。我不止一次见过有同事想等复现再抓日志结果生产环境刷屏后日志被冲掉了错过最佳定位时机。正确做法是第一时间抓哪怕只有一份也好。第二步看日志中的事务相关信息。死锁日志里会列出两个事务分别持有什么锁、等待什么锁以及对应的SQL。重点关注LOCK_TYPE、LOCK_MODE和INDEX_NAME这些字段。通常你能直接看到“事务A: record lock, index PRIMARY”之类的信息这能快速判断是记录锁冲突还是间隙锁冲突。第三步还原执行顺序。把死锁日志中两个事务的SQL结合应用日志中的实际调用顺序理清谁先执行了哪条语句。这一步能帮你明白是代码里先更新A再更新B的顺序问题还是查询范围过大的锁扩大问题。如果是顺序问题解决方案是“约定全局统一更新顺序”如果是范围问题解决方案是“加更精确的索引条件缩小锁范围”。第四步调整隔离级别或索引策略。如果你确认业务可以接受RC隔离级别下的间隙锁消失不要求RR那么把数据库隔离级别从REPEATABLE-READ改成READ-COMMITTED很多由间隙锁引起的死锁会直接消失。如果业务要求RR那么重点优化索引让查询尽量等值命中唯一索引减少间隙锁和错误的锁范围。7.3 经验总结减少锁冲突的几条硬规则最后分享几条我在项目里沉淀下来的规则每一条都是踩过坑验证过的。第一所有涉及多行更新的操作统一按主键升序处理。这个习惯能从根本上消除大批量的“交叉更新死锁”。其实就是给代码里的更新列表排个序比如按ORDER BY id查询出来再循环更新而不是直接从应用层拿到什么顺序就更新什么顺序。第二查询条件尽量走唯一索引或主键等值。等值命中唯一索引时InnoDB会解锁优化为记录锁不会扩展成间隙锁。如果你查询条件本身是普通索引的范围条件加锁范围就不可控。所以线上高并发更新语句一定要看执行计划里key列确认用到了哪个索引。第三让所有插入操作尽量紧凑避免在已有间隙锁的范围内反复试探。换句话说如果某张表高频插入但库里经常有范围查询对同一区间加间隙锁就要考虑引入哈希分表、队列等方式把并发插入分散到不同的物理表从根上错开间隙。第四监控指标不能只看CPU和内存还要看数据库的锁等待。MySQL 5.7以后可以直接查performance_schema.events_statements_current看到语句的锁等待时间配合sys.innodb_lock_waits视图找到阻塞关系。我们内部会写脚本周期性抓取锁等待超过5秒的会话然后发出告警这样大多数锁导致的事故都能在爆发前被拦截。根据我个人经验处理MySQL锁问题最忌讳的是“凭感觉改”。锁本身不是洪水猛兽它是数据库保证数据一致性的工具。如果你遇到并发问题第一反应不是调大连接数或者压死应用而是静下心来把锁的因果链理清楚——用performance_schema观察用SHOW ENGINE INNODB STATUS定位然后从索引、事务长度、更新顺序三个方向去优化。这个方法论远比背下所有锁的名字更有用。再补一个小技巧给所有业务表都配上稳定且唯一的业务主键比如用分布式发号器生成ID这样更新语句就能精准定位到行避免因为主键缺失或UUID随机值导致索引页频繁分裂、锁冲突加剧。我见过一个订单表用UUID做主键后来改成雪花ID后同一时间点插入的锁等待时间下降了将近一半。锁的优化往往就是这些细节的叠加。