这篇文章的选题我很早就想写了。起因是前阵子帮一个社区的朋友排查线上死锁他跑了一个定时任务每天凌晨批量更新订单状态结果第二天一上班就收到一堆锁等待超时的告警。日志一拉两条 UPDATE 语句堵在同一条索引记录上偏偏两边还都加了别的事务谁也等不到谁。我翻了翻他当时的建表语句和事务代码发现问题根本不在于 SQL 写得不好而是在于他完全没有意识到 MySQL 的锁机制在背后是怎么运作的。其实“锁”这个词在 MySQL 里听着抽象但它几乎决定了你每一个并发事务的成败。很多人知道有“行锁”“表锁”这些名词可真到了排查死锁的时候却连SHOW ENGINE INNODB STATUS里的信息都看不懂。这篇就当是给所有被锁困扰过的开发者、运维和 DBA 一份从全局到行级、从原理到实战的完整梳理。你会看到全局锁到底锁住了什么表锁和 MDL 锁如何悄悄卡住你的查询InnoDB 的行锁又是怎样在索引记录上跳舞以及遇到死锁时最快能落地的排查和改写手段。1. 从一条慢 SQL 引出的锁问题1.1 一次线上事故的复盘想理解锁机制最好先回到事故现场。上面提到的那个定时任务伪代码如下BEGIN; UPDATE order_info SET status PAID WHERE order_no A10001; UPDATE order_info SET status REFUNDING WHERE order_no B10002; COMMIT;另一个事务则倒过来操作BEGIN; UPDATE order_info SET status REFUNDING WHERE order_no B10002; UPDATE order_info SET status PAID WHERE order_no A10001; COMMIT;两个事务分别持有对方下一步要更新的行锁于是互相等待直到innodb_lock_wait_timeout默认的 50 秒耗尽。MySQL 只能把其中一个事务回滚掉应用侧就会看到“Lock wait timeout exceeded; try restarting transaction”的报错。问题看起来是程序写得太“对称”但我当时让他先跑了一遍SHOW ENGINE INNODB STATUS发现一个更隐蔽的细节虽然两条记录在逻辑上不同但在 B 树里它们落在了同一个索引页的相邻位置。事务 A 第一条 UPDATE 除了锁住order_no A10001这条记录还在记录之前和之后加上了间隙锁这就直接把事务 B 要更新的B10002给堵在了更小范围的锁等待里。等你真正弄清楚这个因果链就会明白死锁不是运气差而是加锁顺序、索引选择、隔离级别三者共同作用的结果。1.2 为什么每个 MySQL DBA 都要懂锁很多开发者觉得锁只是数据库内部的事跟写业务没关系。但只要你写过一条带WHERE条件的 UPDATE 或 DELETE你就已经在和锁打交道了。比如DELETE FROM user_login_log WHERE create_time 2025-01-01;这条 SQL 看起来只是想清理半年以上的日志可如果没有合适的索引MySQL 会扫描主键做全表判断每扫到一行就加一次行锁。扫描过程中别的会话想插入新的登录日志如果被间隙锁挡住就会出现“明明没碰同一条记录却互相卡住”的诡异现象。理解锁机制的收益有三个层面一是能快速读懂performance_schema.data_lock_waits这类视图定位到底谁在等谁二是能在设计表结构和 SQL 的时候主动避开全表更新、大范围扫描这些容易放大的锁范围三是遇到死锁时不靠重启数据库、不靠瞎改超时参数来碰运气而是直接分析加锁顺序用最小的代码改动解决问题。2. 锁的类型全景图全局锁、表锁、行锁各自管什么2.1 全局锁Flush tables with read lock 到底锁了什么全局锁是整个实例维度的锁执行FLUSH TABLES WITH READ LOCK;之后整个实例进入只读状态。所有数据表的 DML、DDL 都会被阻塞直到执行UNLOCK TABLES释放。这个操作最经典的用途是作为全库逻辑备份的锚点。比如用mysqldump做一致性快照时需要保证备份期间没有任何写入否则备份出来的数据可能是“半个事务”的状态恢复回去一定会出问题。但全局锁的代价太粗暴了。线上业务一执行等于所有写入全部停摆。于是 InnoDB 提供了更优雅的替代方案利用 MVCC多版本并发控制在备份时开启一个可重复读的隔离级别事务mysqldump --single-transaction就是靠这个原理在备份期间不阻塞任何业务写入依旧能看到一致的数据快照。我在实际运维中只会在两种场景下考虑全局锁一是主从切换时为了保证主库和备库的数据点位完全一致短暂加一下二是某些历史遗留的表还是 MyISAM 引擎--single-transaction对它无效只能靠 FTWRL 保证一致性。如果你遇到的是 InnoDB 表能用--single-transaction就别碰全局锁。2.2 表级锁表锁、MDL 锁、意向锁的协同表锁是最容易被忽略的“隐藏杀手”。显式的表锁语句是LOCK TABLES order_info WRITE;这个锁会把整张表锁住其他会话不可以读也不可以写WRITE 锁下读也会被阻塞。虽然 InnoDB 支持行锁但它同样提供表级锁只是默认不主动使用。真正的坑其实在 MDL 锁元数据锁。MDL 锁不需要你手动加它是 MySQL 在 5.5 版本引入的目的是保护表结构定义。当你执行一条 SELECT 查询时会先拿一个 MDL 读锁执行 ALTER TABLE 时则要拿 MDL 写锁。MDL 读锁之间不互斥写锁和读锁互斥。这就引出一个非常常见的线上事故一个慢查询长期占着 MDL 读锁后面一个 DDL 在排队等写锁而 DDL 后面所有的查询、更新全部排队等待。最终表现为“数据库莫名不可用”SHOW PROCESSLIST里能看到大量 “Waiting for table metadata lock” 的会话。意向锁则是 InnoDB 为了让表锁和行锁能共存而设计的“信号牌”。你执行SELECT ... FOR UPDATE加行级排他锁时InnoDB 会先在表上加一个“意向排他锁”。另一个会话想对全表加表锁时一看存在意向排他锁就知道“有行数据被锁住了”立即返回冲突。这样查表时不需要逐个扫描行记录效率高很多。2.3 行锁Record Lock、Gap Lock、Next-Key Lock 的三角关系行锁才是 InnoDB 的看家本领。按锁定的范围它可以细分成三种Record Lock记录锁只锁索引树上的某一条记录。比如主键id 10加锁后其他事务不能修改或删除这一行。Gap Lock间隙锁锁住记录之间的“空隙”。比如索引里有 10 和 20 两条记录间隙锁会把(10, 20)这个区间锁住作用是防止其他事务在这个区间插入新记录。Next-Key Lock临键锁记录锁 间隙锁的组合锁住的范围是(10, 20]即左开右闭区间。它既保护了记录本身又阻止了在这个区间插入新数据。为什么要分这么多类型因为默认的隔离级别是REPEATABLE READ可重复读MySQL 必须防止“幻读”——也就是同一事务里两次相同的查询返回了不同的行集合。如果不锁间隙事务 A 第一次查id 10没有记录事务 B 插入一条id 15并提交事务 A 再查一次就会多出一行这就是幻读。Next-Key Lock 就是 InnoDB 在 RR 隔离级别下用来狙击幻读的武器。理解了这三种锁你在看SHOW ENGINE INNODB STATUS的死锁信息时就能脑补出一幅 B 树索引页的图哪个事务持有什么锁等待的又是哪个锁一目了然。这也是我把行锁放到一个单独章节慢慢讲的原因。3. 行级锁的底层实现与隔离级别3.1 InnoDB 为什么只认行锁MySQL 里有很多存储引擎MyISAM 就只有表锁InnoDB 才有行锁。要理解行锁的价值得回到索引结构本身。InnoDB 的主索引是聚簇索引数据行就存在 B 树的叶子节点上每条记录都有一个主键值作为唯一标识。行锁本质上是“锁定索引记录”而不是直接锁磁盘上的数据行。这个说法很关键因为这意味着如果你更新条件里没有索引或没走索引InnoDB 找不到确定的索引记录就只能扫描聚簇索引等于把全表所有索引记录都锁一遍。行锁的好处是并发度高不同事务可以同时修改不同行只要锁定的索引记录不重叠。坏处是拿锁的成本更高因为要精确定位到叶子节点、检查锁对象是否冲突。这也是为什么 InnoDB 需要一套高效的锁系统来管理锁对象而不是简单粗暴地在表头挂一把锁。3.2 RR 隔离级别下的 Gap 锁是怎么产生的Gap 锁的产生和事务隔离级别直接相关。在 READ COMMITTED 级别InnoDB 只使用 Record Lock不会加 Gap Lock所以不会出现“两个事务之间因为区间插入互相阻塞”的情况但它会有幻读问题。对于很多业务来说RC 级别已经够用而且并发能力更强所以有不少团队会把隔离级别调成 RC。但默认的 RR 级别就不同了。我们看一个典型的场景-- 事务 A BEGIN; SELECT * FROM orders WHERE amount 100 FOR UPDATE;假设orders表里 amount 字段有二级索引索引中已有 80、120、150 几条记录。事务 A 这条 SQL 不仅会把 120、150 两条记录加上 Record Lock还会在 80 和 120 之间、120 和 150 之间、以及 150 之后到无穷大的区间加上 Gap Lock。目的就是保证事务 A 再次执行同样的查询时不会出现新的满足条件的行。但这也意味着事务 B 如果尝试插入一条 amount 130 的新订单会被 Gap Lock 卡住。很多人在这个点上踩坑他们以为只锁住了查出来的两行结果另一个会话插一条与查询条件相关的新记录也被阻塞。不是 MySQL 锁多了而是你选的隔离级别决定了它必须帮你防幻读。如果你业务上能接受提交后再读看到最新数据把隔离级别调成 RC 会显著降低 Gap Lock 出现的概率。3.3 加锁语句与索引的三次握手我们可以把一条 UPDATE 的加锁过程简化三步优化器选择索引根据 WHERE 条件选择成本最低的执行计划。如果选主键索引锁直接落在主键行如果选二级索引InnoDB 会先在二级索引上加锁再回表到聚簇索引把主键对应的记录也锁上。遍历索引记录并加锁每访问到一条索引记录就按当前隔离级别生成 Record Lock 或 Next-Key Lock。注意这里的“访问到”指的是扫描过程中碰到的每条记录不是最终结果集里的每条记录。提交或回滚时释放锁并不是立刻释放的普通读一致性读走 MVCC 不加锁但 UPDATE、DELETE、SELECT FOR UPDATE 这些加锁读都会持有锁直到事务结束。拿一个细节举例假设执行UPDATE users SET nickname new_name WHERE mobile 13800000000;如果mobile建了唯一索引InnoDB 只需要在唯一索引上找到对应记录加锁再回表锁主键。整个过程锁范围极小。而如果mobile没有索引优化器只能走主键索引的全表扫描每扫一条主键记录都会加锁效果等同于锁全表。这也是我下一章要强调的重点——行锁的高并发优势是建立在索引正确的前提之上的。4. 死锁排查与优化实战从 show engine innodb status 到锁等待日志4.1 两个事务相互等待的完整现场当我们遇到死锁报错第一反应应该是拿到死锁现场。MySQL 会把最近一次死锁的信息记录在SHOW ENGINE INNODB STATUS的输出里重点看LATEST DETECTED DEADLOCK部分。下面是我实际分析过的一个简化例子-- 事务 A UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; -- 事务 B UPDATE account SET balance balance - 100 WHERE id 2; UPDATE account SET balance balance 100 WHERE id 1;两个事务都是先更新 id1 再更新 id2只不过 A 的顺序是 1→2B 是 2→1。假设 A 先持有 id1 的锁B 先持有 id2 的锁然后 A 去申请 id2 的锁时发现被 B 持有B 去申请 id1 的锁时发现被 A 持有死锁形成。InnoDB 检测到死锁后会选择一个事务回滚一般是回滚代价较小的那个按 UNDO 数量估算。应用层收到的报错信息类似Deadlock found when trying to get lock; try restarting transaction这在日志里不算罕见正确做法是捕获这个错误码然后重试整个事务。4.2 如何快速定位锁等待和源头事务死锁是“互相等”而锁等待是“单方向等”。排查锁等待时我建议按下面三步走查当前锁等待关系SELECT * FROM performance_schema.data_lock_waits;这个视图能看到哪个事务REQUESTING_THREAD_ID在等哪个事务BLOCKING_THREAD_ID持有的哪个锁。比早期的INNODB_LOCKS表信息更完整。查正在运行的事务和 SQLSELECT trx_id, trx_state, trx_started, trx_query FROM information_schema.innodb_trx ORDER BY trx_started;从这里可以找到长时间未提交的事务。记住一个事务哪怕是只执行了一条 SELECT也会开启事务并持有快照后续对同表执行 UPDATE就可能和别的正在写入的事务冲突。查系统进程SHOW PROCESSLIST;找到被卡住会话的状态如果看到Waiting for lock或Lock wait timeout exceeded基本可以断定是锁等待。我在定位时还有一个值得养成的习惯给事务里的 SQL 打上注释标记比如/* batchJob:123 */ UPDATE ...这样看trx_query时能立刻知道是哪个业务模块发起的事务。这个习惯在大规模集群里帮助极大不然你只能看到一堆抽象 SQL。4.3 常见死锁场景与 SQL 改写思路我把这几年遇到的高频死锁场景整理成了三类每一类都有对应的改写思路。交叉更新上面说的 A 和 B 反向更新核心解法是固定加锁顺序。比如业务上约定所有涉及账户余额的更新都从小到大按账户 id 排序后再执行。项目里可以在 DAO 层统一封装一个批量更新方法把入参按 id 排序。先查后改导致间隙锁叠加BEGIN; SELECT COUNT(*) FROM orders WHERE status 0 FOR UPDATE; -- 业务逻辑... UPDATE orders SET status 1 WHERE order_id 123; COMMIT;这种写法的问题在于SELECT FOR UPDATE的 WHERE 条件如果走了二级索引都可能产生大量 Next-Key Lock。建议先确认能否避免FOR UPDATE改用普通读 乐观锁版本号的方式或者把查询范围缩小到具体的业务批次。批量更新范围重叠UPDATE orders SET status 1 WHERE create_time BETWEEN 2025-01-01 AND 2025-01-31;两个事务如果都执行这个更新即使更新的是不同 create_time 的记录间隙锁也可能互相覆盖。解决办法是按更细的粒度分批例如每次只处理 100 条主键范围内的记录并且批与批之间错开主键区间。改写 SQL 的原则是尽量让锁的范围等于你真正要修改的数据范围不要用宽泛条件把索引区间整个锁住。5. 锁与索引的关系没有索引行锁会退化为表锁5.1 为什么无索引更新会锁全表这是一个很经典的问题InnoDB 行锁是靠索引记录实现的那 WHERE 条件不带索引会怎样答案是扫描了多少条索引记录就会锁多少条。全表扫描等于锁全表而且因为扫描过程中会持续上锁其他事务的插入也被间隙锁大面积覆盖。举个实际数字一张user_login_log表有 200 万行某天运维同事执行DELETE FROM user_login_log WHERE create_time 2024-06-01;create_time没有索引这条 SQL 会全表扫描等于把所有记录的锁都过一遍。期间任何对这张表的 INSERT / UPDATE / DELETE 都会被阻塞而且每条 DML 都要等前面的锁释放越积越多最后直接把数据库连接池打满。要避免这种事故第一是必须给create_time建索引第二是如果删除量确实很大用“分段删除”的方式每次用主键范围取一批 id比如WHERE id BETWEEN ? AND ?循环删除单批事务的锁范围就只限制在这批 id 里。这也是很多 DBA 处理归档数据时的标准操作。5.2 唯一索引与主键索引的加锁差异很多人以为 WHERE 条件用了唯一索引加锁就和主键一样。其实有区别。如果 WHERE 条件用唯一索引UPDATE users SET email newexample.com WHERE user_id 100;只要user_id是唯一索引InnoDB 会先锁定user_id 100这个二级索引记录再去聚簇索引锁定主键id对应记录。两个锁都要持有直到事务结束。但如果 WHERE 条件用普通二级索引UPDATE users SET status 1 WHERE age 25;age不是唯一索引那么所有age 25的二级索引记录都会被加锁同时回表对应的聚簇索引记录也都加锁。锁的数量等于匹配行数。这里有个容易被忽略的坑如果age索引的区分度很低比如表里一半人都 25 岁那么锁的记录数量依然很大。所以在设计索引时不能只看“有没有索引”还要看索引列的区分度。区分度越低一次扫描命中的记录越多锁影响面越大。5.3 如何利用索引下推和覆盖索引减少锁范围想减少锁范围一种很有效的思路是让 UPDATE 只回表修正真正要改的行而不是让优化器大范围扫描。举例来说查询条件里有多个列可以在联合索引中尽量包含 WHERE 列。假设业务经常按status和create_time更新UPDATE task SET exec_result SUCCESS WHERE status RUNNING AND create_time 2025-06-01;如果只在status上建索引MySQL 会先找到所有RUNNING的记录再逐行判断create_time涉及大量锁。如果在(status, create_time)上建联合索引优化器可以精确命中那一小段符合条件的索引区间锁的数量会大幅减少。这就是“索引条件下推”Index Condition Pushdown带来的间接好处让存储引擎在索引层面过滤掉更多行而不是把每一条都回表加锁。覆盖索引则更适合 SELECT 加锁场景。比如要锁定一批满足条件的订单号SELECT order_id FROM orders WHERE merchant_id 1000 AND status PAID FOR UPDATE;如果表有(merchant_id, status, order_id)联合索引这个查询完全可以在索引中完成不需要回表读整行数据。虽然FOR UPDATE还是会锁住命中的二级索引记录和聚簇索引记录但回表扫描的开销显著降低。锁范围没有变小多少但加锁的速度更快事务持有锁的时间更短从而减少锁冲突概率。6. 常见问题速查与经验总结6.1 锁等待超时与死锁的区别锁等待超时和死锁经常被混为一谈但它们机制完全不同。锁等待超时一个事务在等待另一个事务释放锁等到了innodb_lock_wait_timeout默认 50 秒仍然没等到就会报错。此时另一个事务还在正常运行并没有被回滚。死锁两个以上事务互相持有对方需要的锁形成环形等待。InnoDB 的死锁检测线程会主动介入把其中一个事务回滚掉让另一个事务继续执行。注意死锁检测默认是开启的所以死锁不需要等超时通常几百毫秒内就会触发。处理建议锁等待超时先查data_lock_waits找到阻塞源头看看那个事务为什么长时间不提交。死锁处理则要抓住两点一是应用层捕获ER_LOCK_DEADLOCK错误码 1213自动重启事务二是从代码层面消除固定的加锁顺序冲突。6.2 实际生产环境中的锁监控命令清单给你一份我压箱底的排查命令组合我每次遇到锁问题都会按顺序执行看整体事务情况SELECT trx_id, trx_state, trx_started, trx_query, trx_rows_locked FROM information_schema.innodb_trx;看锁等待明细SELECT * FROM performance_schema.data_lock_waits;看当前持有的锁对象SELECT * FROM performance_schema.data_locks;看最近死锁信息SHOW ENGINE INNODB STATUS;如果发现某个事务长期不提交查看它的连接信息SELECT id, user, host, db, command, time, state, info FROM information_schema.processlist WHERE id 具体连接ID;这套组合覆盖了从“谁在等谁”到“等了多久”到“锁住了哪些对象”的完整链路。注意版本问题MySQL 5.7 之后推荐用performance_schema.data_locks老的information_schema.innodb_locks在新版本里已经废弃。如果你还在用 5.6那就只能靠SHOW ENGINE INNODB STATUS手工解析了。6.3 几条独家避坑建议最后分享几条我在各种环境里踩过坑后沉淀下来的经验。第一条别把隔离级别当摆设。如果业务数据对幻读不敏感比如只有 insert 和 delete、很少做范围查询后再插入的场景直接把隔离级别设置成READ COMMITTED能减少大量间隙锁带来的并发阻塞。我见过某个订单系统只是改一个transaction_isolation参数高峰期锁等待次数直接降了 70%。第二条大事务是锁问题的万恶之源。一个事务里如果塞了太多查询、远程调用、甚至耗时算法都会让锁的持有时间无限拉长。锁并不可怕可怕的是锁被长时间握住不放。所以写事务代码时要遵守“事务内不做无关操作”的原则把批量更新拆分到独立小事务里执行。第三条监控要前置。不要等到死锁报错才去查。在数据库慢查询监控里专门把 “Lock wait timeout” 和 “Deadlock found” 这两个错误码拉出来做告警同时定期采集innodb_row_lock_current_waits和innodb_row_lock_time_avg这两个状态值。一旦平均值异常上升即使没有报错也能提前发现锁冲突恶化的趋势。第四条也是我最想强调的一点——别动不动就清缓存、杀连接、重启 MySQL。锁问题是事务逻辑和索引设计的问题不是数据库“坏了”。重启只会让正在运行的事务全部回滚业务侧可能会补偿数据反而扩大混乱。正确姿势是冷静下来用上面的命令把锁等待链查清楚从源头解决。说起来我个人的习惯是在每个项目的数据库设计评审阶段就把“这条业务 SQL 会加多少锁、加多久、会不会影响其他会话”当成必答题。一旦养成这种思维很多并发问题在设计阶段就被掐灭了根本轮不到上线后去死锁排查。这也是我和这篇文章最想传达给你的东西锁机制并不神秘它只是 InnoDB 为了保证数据一致性和并发效率在索引之上画下的一条条边界。你理解了边界在哪里、为什么会存在自然就知道怎么写 SQL 才能不越界、不踩坑。
