前一阵在排查一个线上卡顿电商订单表相关操作全部堆在“Waiting for table metadata lock”一个普通的 ALTER TABLE 卡了四十多分钟。我第一反应是查慢查询结果列表干干净净后来才意识到问题出在 MySQL 锁上。MySQL锁这块平时写 CRUD 感觉不到它的存在但一旦出现锁等待、死锁、锁表你才会发现它几乎能决定整个系统的可用性。这篇内容没有停留在概念层面我会从 InnoDB 存储引擎出发把全局锁、表级锁、行级锁、意向锁、MDL 锁这些平时最容易混淆的东西串起来再结合真实踩过的坑讲排查思路。同时会给出可以直接复制执行的 SQL方便你在自己的测试环境里复现和理解。不管你是刚转数据库开发的帮手还是已经写过几年业务代码但没系统整理过锁机制的老手看完之后再去处理锁等待和死锁起码不会再靠重启数据库解决问题。这一篇可能比你在网上找到的多数 “MySQL 锁详解” 都要更贴近实际场景因为里面每一个例子都是我实际在项目里遇到过的而不是从官方文档里抄下来的。1. 并发控制与锁的基本盘1.1 为什么数据库非要有锁数据库最核心的价值就是“让多个客户端并发读写同一份数据最终结果仍然正确”。但并发必然带来三个问题脏读、不可重复读、幻读。为了处理这些问题才引入了事务隔离级别而锁就是隔离级别落地的最底层工具。你可以把锁理解成“厕所门闩”。一个人进去之后把门闩插上其他人只能在外面等里面的人出来门闩打开下一个人才能进去。这个门闩机制保证了同一时刻只有一个人在用厕所但也带来了排队成本。数据库锁也是同样逻辑它牺牲一部分并发性来换取数据一致性问题是你得知道什么时候该排队、什么时候不该排队。这里有个特别容易被误解的点锁不是越少越好也不是越多越好而是“锁的粒度”要对场景。银行转账时必须锁行否则余额会算错但一个只读报表查询如果锁住了整个表业务写入就会全部阻塞。所以 MySQL 才设计了从全局锁到行级锁的一整套层次每一层都在平衡并发度和一致性的矛盾。1.2 MySQL锁的宏观分类MySQL 的锁体系可以按两个维度切分一个维度是“锁的范围”另一个维度是“锁的属性”。按范围分从大到小是全局锁、表级锁、行级锁。全局锁锁住整个实例表级锁锁住某张表行级锁锁住索引记录或间隙。按属性分最基础的是共享锁S锁读锁和排他锁X锁写锁。共享锁之间兼容共享锁和排他锁互斥两个排他锁也互斥。所有复杂的锁机制最后都是在这个兼容性矩阵上做的扩展。你要记住一个结论MyISAM 引擎只有表锁InnoDB 引擎才有行锁。这也是为什么线上高并发系统基本都用 InnoDB。如果建表时还在用 MyISAM那么任何写操作都会串行执行并发吞吐直接被打到地板上。InnoDB 的行锁还有一个“隐藏条件”它锁的其实是“索引记录”不是数据行本身。换句话说如果一条 SQL 没有走索引InnoDB 就会从第一条记录扫到全表把扫描路径上的所有记录都锁住。这个细节是很多“锁表”事故的真正原因后面我会专门展开说。2. 全局锁、表级锁与元数据锁2.1 全局锁FLUSH TABLES WITH READ LOCK全局锁是 MySQL 里最重的锁命令是FLUSH TABLES WITH READ LOCK通常缩写为FTWRL。它会让整个库进入只读状态所有 commit、update、delete 都会被阻塞所以生产环境一定要极其谨慎地使用。它最常见的应用场景是“全量备份”。早期用 mysqldump 做物理备份时为了保证备份文件的一致性需要先拿到全局读锁把各表的 binlog position 固定下来。但FTWRL会阻塞所有写入如果业务流量很高整个系统等于瞬间停摆。对于这一点现在的处理方式是改用mysqldump --single-transaction。它会基于 InnoDB 的 MVCC 快照读来做一致性备份不需要加全局锁业务写入基本不受影响。这里有个前提表必须是 InnoDBMyISAM 表依然会被锁。所以我的建议是如果还在用 MyISAM尽早迁到 InnoDB否则备份和高并发二者不可兼得。2.2 表锁LOCK TABLES 的适用边界表级锁包括两种LOCK TABLES t READ/WRITE显式表锁以及 DDL 语句隐含获取的元数据锁MDL。显式表锁的语法很简单LOCK TABLES orders READ; -- 或者 LOCK TABLES orders AS o WRITE;但我要说一句可能刺痛人的话在 InnoDB 普及之前表锁是 MyISAM 的唯一选择在 InnoDB 普及之后业务代码里几乎没有理由再主动使用LOCK TABLES。因为 InnoDB 有了行锁你再锁表等于把行锁的并发优势全部扔掉。如果你确实需要“锁住整张表”通常也意味着你的业务设计出了问题。不过有一类场景还是能看到表锁的影子就是“批量归档”。比如要把一张历史表中的 500 万条数据搬到归档表为了保证中途不被写入有人会直接LOCK TABLES history WRITE搬完再UNLOCK TABLES。这种操作只适合低峰期而且耗时超过 30 分钟的话线上基本就等着被打挂了。2.3 MDL锁最容易被忽略的锁MDLMetadata Lock锁是 MySQL 5.5 引入的机制它不属于任何存储引擎而是 Server 层维护的元数据锁。它的任务是保护表结构定义DML 需要获取 MDL 读锁DDL 需要获取 MDL 写锁读锁和写锁互相排斥。这类锁平时不出问题一出问题就是大事故。最常见的情况是一个长事务里的SELECT一直开着不提交这时候你执行一条ALTER TABLE它会排队等待 MDL 读锁释放后面所有对这个表的SELECT、INSERT、UPDATE也全部被这个等待中的ALTER TABLE挡住。最终表现就是这条表的所有操作全部卡死。定位方法非常简单-- 5.7 查 MDL 等待 SELECT * FROM performance_schema.metadata_locks; -- 8.0 可以用 sys 库 SELECT * FROM sys.schema_table_lock_waits;看到等待状态是PENDING的ALTER TABLE再找它前面那个持有 MDL 读锁的事务Kill 掉那个长事务阻塞立刻消失。这个坑我在生产环境踩过三四次每次都发生在凌晨定时批量任务和 DBA 变更窗口重叠的时候所以现在团队规定所有 DDL 变更之前必须先检查sys.schema_table_lock_waits并且设置lock_wait_timeout不能让 DDL 无限期等下去。3. InnoDB行锁记录锁、间隙锁与临键锁3.1 记录锁与唯一索引InnoDB 默认隔离级别是 REPEATABLE READ可重复读在这个级别下行锁并不是单纯的“锁一行记录”而是分了三种记录锁、间隙锁、临键锁。记录锁Record Lock最简单它锁的是索引上的一条明确记录。比如-- id 是主键 UPDATE user SET name 张三 WHERE id 100;在id 100这条主键索引记录上X 锁会被加上其他事务想修改同一行就会被阻塞。这里要强调InnoDB 行锁必须有索引才能生效如果没有索引InnoDB 会锁全表所有记录。注意这不是传统意义上的表锁是“逐行加锁”到了全表但实际并发效果和表锁一样灾难。记录锁的典型问题是“隐藏间隙”。你以为只锁了一行但查询条件里有范围InnoDB 会连范围两侧的间隙一起锁。比如UPDATE orders SET status 1 WHERE order_no A001 AND order_no A100;即使实际只有 50 条记录锁的范围也会覆盖A100之后一个区间。其他事务想插入新订单号可能就会卡住。3.2 间隙锁与临键锁解决幻读的代价间隙锁Gap Lock锁的是一个区间但它不锁区间的记录只锁区间本身“不允许插入”。间隙锁唯一的作用就是阻止其他事务在某个范围内插入新记录从而解决幻读问题。临键锁Next-Key Lock可以理解为记录锁加上前面的间隙锁。比如索引上有记录 10、20Next-Key Lock 会锁(负无穷, 10]、(10, 20]、(20, 正无穷)这样的区间。它锁的不只是记录本身还包括记录前面的间隙。这样在可重复读隔离级别下一个事务查询两次区间内怎么都不会插入新数据。间隙锁带来的代价是“锁范围容易被放大”。比如你要更新一条status 0的记录如果status是普通索引且区分度很低得走索引扫描然后命中多个区间结果就是明明只改了一条记录却锁住了整个索引段其他想插入status0记录的事务全部阻塞。这种问题在电商订单状态、工单状态这类低区分度字段上极其常见。3.3 索引与锁范围为什么明明只更新了一行却锁了一片我在现场排查过一个案例程序里跑一条UPDATEWHERE 条件是一个没有索引的字符串字段。执行计划走了全表扫描结果这条UPDATE把整张表几十万行全锁住了所有其他请求全部堆积在 lock wait 上。复盘下来程序员的原始意图是“只更新一条数据”但他的 WHERE 条件完全没法走索引InnoDB 只能通过全表扫描过滤目标行而全表扫描过程中触达的每一行都要先加锁再判断是否需要更新。即使最终只更新一行其他行的锁也已经加过了。所以这里有一个铁律InnoDB 的行锁必须锁定在索引上写操作的 WHERE 条件一定要带索引而且最好带高区分度索引。主键索引最优唯一索引次之普通索引要看区分度。如果无法避免对低区分度字段加锁可以考虑把业务逻辑改成先通过主键查出目标主键 ID再带 ID 执行更新把锁范围精确压在一行上。4. 共享锁、排他锁与乐观/悲观锁的落地4.1 S锁、X锁与意向锁之间的关系共享锁和排他锁是数据库锁的基础属性。共享锁用于读多个共享锁可以同时持有排他锁用于写排他锁与任何其他排他锁或共享锁都不兼容。InnoDB 在加行级锁之前还会先在表上加意向锁目的是快速判断表级操作和行级操作是否冲突。举个例子事务 A 对某一行加了 X 锁事务 B 想对整张表加表级 S 锁它需要检查是否已有事务持有了不兼容的行锁。如果没有意向锁B 要逐行检查有了意向锁A 加行锁前会顺手在表上标记“IX”B 看到表上有 IX立刻就知道冲突直接进入等待。这是一个典型的“以空间换时间”设计。意向锁的兼容矩阵可以精确到四句话IS意向共享锁与 IS、IX 兼容IX意向排他锁与 IS、IX 兼容表级 S 锁与 IS 兼容与 IX 不兼容表级 X 锁与所有锁都不兼容。这四句话几乎是所有锁冲突分析的底层逻辑建议直接刻在脑子里。4.2 悲观锁的实现方式悲观锁认为“冲突一定会发生”所以每次读取数据都直接加锁直到事务结束。MySQL 里最常见的悲观锁语法是SELECT ... FOR UPDATE也叫“当前读”BEGIN; SELECT * FROM accounts WHERE id 1 FOR UPDATE; -- 拿到锁后做余额扣减 UPDATE accounts SET balance balance - 100 WHERE id 1; COMMIT;执行FOR UPDATE时会申请 X 锁其他事务如果也想SELECT ... FOR UPDATE同一行就会阻塞。这种方式适合写入非常频繁、冲突概率极高的场景比如订单库存扣减、账户余额变动。但悲观锁的门槛是“必须搭配事务”SELECT ... FOR UPDATE之后如果迟迟不提交其他事务会一直等锁等到innodb_lock_wait_timeout超时报ERROR 1205: Lock wait timeout exceeded。实际项目里不少问题就出在这里事务里在锁后还做了网络调用锁一拿就是几秒甚至几十秒。我的经验是锁内的代码一定要轻最好只包含内存计算和一条目标 UPDATE任何可能耗时的外部调用都不能留在锁区间内。4.3 乐观锁在业务层的常见实现乐观锁认为“冲突很少发生”所以不加数据库锁只在校验阶段确认数据有没有被改过。MySQL 场景里最常见的实现是利用版本号字段-- 表结构里有一个 version 字段 UPDATE accounts SET balance balance - 100, version version 1 WHERE id 1 AND version 5; -- 如果 affected rows 0说明 version 已经不是 5需要重试这条 SQL 不会在数据库层加任何行锁但在应用层通过受影响行数判断更新是否冲突。冲突后一般会重新读取最新版本再次尝试或者直接返回用户“操作过于频繁”。另一种实现是拿旧状态值作为 WHERE 条件比如WHERE status 1但可读性和可维护性不如版本号清晰。乐观锁适合读多写少、冲突概率低的场景比如用户修改个人资料、发布评论这类操作。用乐观锁最大的好处是数据库层完全没有锁等待但代价是应用层要处理重试逻辑。如果你在重试时无脑循环高并发下可能反而把数据库打得更惨所以重试次数要设上限比如三次再多就该直接报错。5. 锁等待、死锁的排查与处理实录5.1 先看一眼当前锁状态线上出现“所有请求都卡住”时我第一件事不是看慢查询而是执行下面这组 SQL-- 当前活跃事务 SELECT * FROM information_schema.innodb_trx\G -- 锁等待关系 SELECT * FROM information_schema.innodb_lock_waits\G -- 5.7 查看锁具体挂在哪些记录 SELECT * FROM information_schema.innodb_locks\G -- MySQL 8.0 查看锁 SELECT * FROM performance_schema.data_locks\G SELECT * FROM performance_schema.data_lock_waits\Ginnodb_lock_waits这张表能直接给出“哪个事务在等哪个事务的锁”再结合innodb_trx里的trx_started找出最早发起事务的会话。绝大多数锁问题的根因都是“有一个老事务没提交”Kill 或让它提交问题就解决了。有一个经验值得写下来不要一开始就去 Kill 事务。先看它的trx_mysql_thread_id然后查看这个会话正在执行的 SQLSELECT * FROM performance_schema.events_statements_current WHERE thread_id (SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID 刚才查到的线程ID);很多时候你会发现它只是在一个SELECT查询上但因为事务隔离级别和一致性读机制读操作也会持有很长生命周期的快照间接导致 MDL 锁或 undo purge 受阻。5.2 死锁日志怎么读死锁和一般锁等待不一样。锁等待只是“堵车”死锁是“环形交通事故”。MySQL 检测到死锁后会自动回滚其中一个事务应用程序会收到ERROR 1213: Deadlock found when trying to get lock; try restarting transaction。定位死锁的入口只有一个SHOW ENGINE INNODB STATUS\G输出内容里重点看LATEST DETECTED DEADLOCK部分。它会显示两个事务各自的持锁和等锁记录格式大致是这样的*** (1) TRANSACTION: TRANSACTION 1001, ACTIVE 5 sec LOCK WAIT ... *** (1) WAITING FOR THIS LOCK TO BE GRANTED: ... *** (2) TRANSACTION: TRANSACTION 1002, ACTIVE 3 sec LOCK WAIT ... *** (2) HOLDS THE LOCK(S): ...看日志时不要被大段内容吓到你只要找到三块信息事务 1 等什么锁事务 2 持有什么锁事务 2 又在等什么锁。把这三块画成一个环死锁原因自然就出来了。5.3 处理死锁的常见套路最常见的一种死锁场景是“两个事务按不同顺序更新同一批数据”。假设订单表里有两个字段需要更新事务 A 先改status再改shipping_time事务 B 先改shipping_time再改status。两条 SQL 在并发时就可能形成互相等待。解决办法听起来很简单让所有事务按照相同顺序加锁。例如规定必须先更新主键小的记录再更新主键大的记录。这种顺序锁在实际项目里比想象中难落地因为业务代码往往分散在不同服务里很难统一但至少应该做到同一个服务内、同一组业务操作中锁的获取顺序一致。另一个更实用的手段是“把大事务拆小”。死锁的发生概率和事务持锁时间成正比持锁时间越长重叠窗口越大。把一个大事务拆成两三个小事务死锁概率会指数级下降。不要想着一批 1000 条数据的更新全部塞进一个事务分成每批 100 条、每批都快速提交效果会好非常多。6. 锁优化与避坑经验6.1 减少锁冲突的六条实操建议以下六条都是我在代码和运维层面反复验证过的经验每一句背后都站着一个真实事故。第一写操作的 WHERE 条件必须走索引。这个是行锁生效的前提。没有索引的 UPDATE/DELETE 不只是慢的问题而是锁全表的问题破坏性极高。第二控制事务的持有时间。拿到锁之后不要做远程调用、不要等外部接口返回、不要等用户输入锁内的代码只做纯内存计算和单条 SQL。第三合理设置innodb_lock_wait_timeout。默认值是 50 秒对高并发系统来说太长了我一般设成 3 ~ 5 秒宁可让请求快速失败也不要让所有线程都堆积在锁等待上。第四隔离级别尽量不升级。很多时候业务并不需要可重复读如果数据库压力大、间隙锁导致的锁冲突严重可以评估把隔离级别降到读已提交。RC 级别下 InnoDB 不会加间隙锁只加记录锁锁冲突概率大幅下降但代价是可能出现幻读需要业务层面接受。第五定期检查长事务。MySQL 5.7 开始可以在sys.schema_unused_indexes和performance_schema.events_statements_current相关视图里定位长时间未提交的事务建议做一个每天检测长事务的脚本超过 5 分钟就告警。第六对锁相关参数做监控。比如Performance Schema里wait/io/table/sql/handler相关指标、错误日志里的Deadlock found次数这些都应该纳入监控面板。6.2 常见锁问题速查表现象可能原因快速定位手段常规处理请求普遍卡住CPU 不高存在长事务持有 MDL 读锁sys.schema_table_lock_waitsKill 长事务或等其提交UPDATE 锁等待超时目标 SQL 未走索引锁全表EXPLAIN看 key 是否为 NULL给 WHERE 字段加索引间隙锁导致插入阻塞低区分度索引范围更新data_locks查看 LOCK_MODE 含 GAP换主键条件或降隔离级别报错 1213死锁多个事务加锁顺序不一致SHOW ENGINE INNODB STATUS统一加锁顺序缩短事务备份时所有写入卡住用了 FTWRL 全局锁查看processlist的 FTWRL 命令改用mysqldump --single-transaction表格结构变更长时间 pendingDDL 在等 MDL 写锁performance_schema.metadata_locks找到持有读锁的会话并 Kill上面这张表是我现在排查 MySQL 锁问题的第一页速查笔记基本能覆盖 80% 的线上场景。剩下 20% 要么是多个问题叠加要么是某些特殊条件下触发的隐藏锁需要靠现场截图和 binlog 慢慢分析。6.3 关于分布式锁的一个提醒热搜里经常把 MySQL 锁和分布式锁联系在一起很多面试题也会问“MySQL 能不能实现分布式锁”。从实践角度看用 MySQL 唯一索引做分布式锁是可行的但它有天然的瓶颈锁记录本身会成为单一热点而且所有竞争方都在抢同一行记录的锁抢不到的一方只能阻塞等待或者轮询数据库吞吐量远不如 Redis 分布式锁。我的态度是如果分布式锁的并发量很低、一天就几千次抢占那用 MySQL 唯一索引实现完全没问题操作简单、不需要引入新组件。但如果抢占频率是每秒几百甚至几千次SQL 层面的锁冲突会直接把数据库打垮。分布式锁的选型从来不是“哪个更高级”而是“哪个更配当前流量”。结尾写点实在的体会最后聊点我自己处理锁问题后的体会。每次解决完一个锁故障我都会把相关 SQL、事务隔离级别、索引情况打包记录成一篇复盘文档截图和日志都会保留。MySQL 锁不是独立的八股文知识点它和事务代码的粒度、索引设计、ORM 的默认行为全都绑在一起任何一个环节没做好最后都会以“锁等待”“死锁”的形式暴露出来。如果你的系统今天还没遇到过锁问题大概率不是因为你写得好而是流量还没到那个量级。建议现在就查一下当前有哪些长事务核心表的写 SQL 是否都走了索引DDL 变更窗口是否避开了业务高峰。这三个动作做完你能预防掉绝大多数线上事故。真遇到问题时也请记住先看innodb_lock_waits再查performance_schema.metadata_locks最后打开SHOW ENGINE INNODB STATUS一步一步来锁问题并没有想象中那么难解。