做后端和数据库运维的朋友大概率都有过这种经历线上业务一到集中时段应用层突然开始报锁等待超时明明单条SQL跑得飞快并发一起来就像被人按住了脖子。MySQL的锁机制、并发控制与优化策略从表面看无非是共享锁、排他锁、间隙锁几个名词真正拿到排查现场才发现锁的范围、加锁顺序、事务边界每个环节都在影响最终并发表现。这篇文章不打算从《数据库原理》第一章讲起而是从我实际排查过的死锁案例出发把锁家族、InnoDB加锁逻辑、锁等待定位、隔离级别带来的差异以及落地优化手段一起串起来。适合刚接触InnoDB行锁的开发者也适合正在为高并发更新链路发愁的团队。1. 从表锁到间隙锁MySQL里的锁家族分别在哪层起作用1.1 表锁和行锁的职责边界很多人会把“锁”当成一个笼统的概念其实MySQL的锁至少存在于三个层面Server层的表锁、存储引擎层的行锁、以及行锁内部衍生的间隙锁。最早的MyISAM引擎只有表锁一张表在做写入的时候所有读到这张表的请求都会被堵住。以前我维护过一批搜索词统计表用的就是MyISAM凌晨做全量聚合更新时页面查询能集体超时那体验真的酸爽。表锁的优点是实现简单、开销低加锁不需要扫描具体行适合低频全表操作。但它的缺点也致命读写互相阻塞并发写基本等于零。所以InnoDB出现后业务表几乎全部迁到了行锁模式下。行锁让不同行的读写可以并行但代价是加锁逻辑更复杂且死锁检测成本更高。实际操作里有一个容易被误踩的点即便InnoDB支持行锁某些语句依然可能触发全表锁或者大量行锁比如UPDATE没有走索引优化器扫描全表行锁会锁住所有扫描到的记录这跟锁的类型无关纯粹是“你想锁一条MySQL只能锁一片”的现实。1.2 意向锁让表锁和行锁不打架的协议理解意向锁的关键是搞清楚一个矛盾表锁和行锁是不同粒度的锁如果一个事务持有某些行锁另一个事务要加表锁数据库怎么知道这张表里有没有行锁存在难道去扫描每一行当然不行。于是InnoDB设计了一种“提前声明”的锁——意向锁。意向锁分两种意向共享锁和意向排他锁。事务想在表上加表级共享锁时必须先取得意向共享锁想在表上加表级排他锁时必须先取得意向排他锁。意向锁的位置很有意思它表示“这个事务准备在某些行上加锁”而不是真正锁住那些行。比如一个事务执行UPDATE t SET a1 WHERE id10它会在表级别留下一个意向排他锁同时在id10的那一行上加排他锁。另一个事务想执行LOCK TABLES t WRITE看到意向排他锁就知道这表里有行锁直接排队等待不用傻乎乎去扫行。这个设计解决了“行锁与表锁并存”的冲突判定问题但它在排查时不常被注意到因为意向锁几乎不会成为阻塞点。真正频繁出现在SHOW ENGINE INNODB STATUS里的是下面要说的间隙锁和插入意向锁。1.3 间隙锁与插入意向锁守护的是并发缝隙间隙锁在InnoDB里是处理“幻读”的关键但它同样是并发写入冲突的常客。所谓间隙是指索引记录之间的空档。假如一张表id是主键现有记录id为1、3、5那(1,3)、(3,5)以及5之后的区间都算间隙。在默认的可重复读隔离级别下事务执行SELECT ... WHERE id BETWEEN 2 AND 4 FOR UPDATE不仅锁住已存在的记录还会把(1,3)和(3,5)两个间隙都锁上。间隙锁有一个特殊的性质它锁的是“禁止其他事务在间隙中插入记录”的权限而不是某条真实记录。为什么需要这种锁因为可重复读隔离级别要求同一事务中多次快照读结果一致如果没有间隙锁另一个事务在(1,3)中间插入id2的记录第一个事务再次执行同样查询就会看到新行幻读就发生了。插入意向锁则和间隙锁是一对搭档。当一个事务想向某间隙插入记录时它必须先获取该间隙的插入意向锁。如果该间隙已被其他事务的间隙锁占据插入意向锁就会进入等待状态。很多高并发下单的报表里同一秒内大量INSERT排队就是插入意向锁在间隙锁后面排队导致的。这种场景下innodb_lock_wait_timeout一旦超时应用端就会收到“lock wait timeout exceeded”的错误而这距离死锁其实是一步之遥。2. InnoDB行锁的扩张逻辑一条UPDATE到底锁住多少记录2.1 一致性读与当前读同一个SELECT锁完全不一样提到“SELECT不会加锁”这是新手最常听的结论但它漏了一个前提普通SELECT是快照读也就是一致性读走MVCC机制不加锁而SELECT ... FOR UPDATE、UPDATE、DELETE是当前读必须读取最新版本因此会加锁。这个区别直接影响锁的扩张范围。比如在可重复读隔离级别下事务A执行START TRANSACTION; SELECT * FROM t WHERE statuspending FOR UPDATE;这条语句会把所有statuspending的记录以及它们之间的间隙全部加上当前读锁。假设表里pending记录有三条分布在不同的索引页锁范围就覆盖三个页面的记录和间隙。另一个事务B想往这些记录的间隙里插入一条新pending数据一模一样的Update路径会立刻被卡住。我曾经在排障时遇到过一种情况一条SELECT COUNT(*) FROM t WHERE statuspending很快但一条UPDATE t SET statusdone WHERE statuspending迟迟拿不到锁。原因就在于前者是快照读读的是历史版本后者是当前读需要等待事务A释放锁。很多团队在分析慢查询时只看执行计划没有意识到“当前读”和“快照读”的锁差异才会对“为什么单条SQL执行计划没问题却持续锁等待”百思不解。2.2 唯一索引点查也会变成next-key锁“我走的是唯一索引明明只查一行为什么别的行插入不了”这是我被问过很多次的问题。答案藏在InnoDB的锁定算法里。InnoDB默认的行锁算法是next-key lock即“记录本身加锁同时记录之前的那个间隙也加锁”。简单说如果主键索引里没有这条记录比如查询id10现有最大id是8那next-key锁就会锁定(8, 10]这个范围。如果id10存在next-key锁锁定的是(上一个id值, 10]这一段。所以即使你一个字一个字地写了WHERE id10在可重复读隔离级别下锁的范围也不只是id10这一条记录而是“前一条记录到这条记录之间的整个区间”。举个例子表里有主键id现有记录是1、2、5、8。事务A执行UPDATE t SET namex WHERE id5。表面上是锁id5实际上InnoDB会在(2,5]这个区间上放置next-key锁。这意味着另一个事务想要插入id3或id4的记录都会被堵住因为插入行为需要获取间隙锁而间隙(2,5)已经被事务A的next-key锁覆盖了。只有当WHERE条件命中唯一索引且记录一定存在时InnoDB才能把next-key锁退化成普通的记录锁只锁这一行如果记录不存在依然会锁一个间隙。所以“唯一索引查一行”不等于“只锁一行”这是理解锁扩张最反直觉的一点。2.3 回表与加锁顺序为什么锁的名字和最终持有行对不上InnoDB的锁是加在索引记录上的不是加在“你想象的数据行”上的。如果SQL走了二级索引那么加锁顺序是先在二级索引的索引记录上加锁再去主键索引聚簇索引回表读取完整记录并加锁。这两步缺一不可锁的持有者是两个索引上的记录。遇到一条SQL同时涉及多张表或多条索引时锁的扩张顺序会变得更加隐蔽。比如一条UPDATE语句的WHERE字段没有索引优化器可能会走主键全扫描那么所有扫描到的主键记录都会被加上锁如果WHERE字段有二级索引但数据存在大量重复值二级索引会锁住多条索引记录同时每条记录回表后还会锁住对应的聚簇索引记录。这也是为什么优化器选择索引时会考虑“区分度”——区分度越差命中的索引记录越多回表越多锁也就越多。这里分享一个我自己的教训某系统曾经为了让更新逻辑简单直接在多个字段上建了联合索引但联合索引的字段顺序设计得不合理导致一次UPDATE需要回表扫描上千行。并发下锁等待一多写放大立刻显现。后来把联合索引的字段顺序调整成“等值字段在前、范围字段在后”才把锁住的行数从上千压到几十行。3. 死锁还是锁等待一次完整的排查链路还原3.1 锁等待的底色先分清“正常排队”和“异常堵塞”开始排查之前先澄清一个概念锁等待本身不是故障。高并发场景下多个事务同时操作同一批数据有一个事务先拿到锁其他事务排队这是完全正常的调度行为。MySQL的默认锁等待超时时间是50秒意味着正常情况下语句等待锁最多50秒超过就被杀掉并返回错误。真正需要紧张的是两种情况一是锁等待频繁且持续说明业务上存在“热点行”或“事务过长”二是出现了真正的死锁InnoDB会自动检测并回滚其中一个事务让另一个继续。死锁不需要等到超时它会立刻被检测出来并且会记录到错误日志里。我推荐的排查顺序是先看死锁日志再看当前正在进行的锁等待最后结合业务代码判断事务边界。很多朋友一上来就看performance_schema或者sys.schema_table_lock_waits不是不行而是没有先把手里的已知条件排查完容易被冗余信息带偏。3.2 从errlog和show engine innodb status读取死锁现场死锁日志首先看错误日志位置一般在datadir下的hostname.err或者用SHOW ENGINE INNODB STATUS直接看最新的死锁记录。实际输出长这样SHOW ENGINE INNODB STATUS\G重点关注LATEST DETECTED DEADLOCK这一段它会把参与死锁的两个事务各自持有的锁、等待的锁、以及线程ID列出来。别被一大段锁信息吓到我一般只挑三个字段看事务ID、持有哪些锁的表和索引、等待哪条记录。有一个非常实用的经验死锁日志里记录的“record lock”和“gap lock”数量会直接告诉你两个事务锁范围的交叠点在哪里。如果日志里显示两个事务都锁定了同一间隙那么并发插入逻辑是冲突根源如果显示都锁了同一行记录那么两个事务的事务体量需要核查。比如我之前处理过一个模拟项目里的死锁Transaction 1: UPDATE order_items SET quantity quantity - 1 WHERE order_id 123 Transaction 2: UPDATE orders SET amount amount 1 WHERE order_id 123表面上是不同表但两条语句都回表查了order_id123的用户主记录都往聚簇索引的那一行上加锁。事务1先拿到订单表的锁事务2先拿到用户表的锁再互相等待对方持有的下一把锁死锁就必然发生。这种跨表但共享同一聚簇记录的场景日志里看起来不像“同一张表”其实锁冲突点完全一致。3.3 用锁等待表找到谁堵住了谁死锁解决了再看没有死锁但持续锁等待的情况。MySQL 5.7及以后我习惯查这几张表SELECT * FROM information_schema.innodb_trx; SELECT * FROM information_schema.innodb_lock_waits; SELECT * FROM information_schema.innodb_locks;其中innodb_trx能看到当前所有未结束的事务及其trx_started时间innodb_lock_waits直接给出“谁在等哪把锁那把锁被谁握着”innodb_locks列出具体的锁描述。MySQL 8.0里这些表位置有变动推荐直接查performance_schema.data_locks和performance_schema.data_lock_waits字段更全还可以关联threads表定位应用会话。我习惯组合成一条查询直接看阻塞者和被阻塞者的关系SELECT r.trx_id AS waiting_trx, r.trx_mysql_thread_id AS waiting_thread, b.trx_id AS blocking_trx, b.trx_mysql_thread_id AS blocking_thread, l.lock_table, l.lock_index, l.lock_type FROM performance_schema.data_lock_waits w JOIN performance_schema.data_locks l ON w.REQUESTED_ENGINE_LOCK_ID l.ENGINE_LOCK_ID JOIN information_schema.innodb_trx r ON w.REQUESTING_TRX_ID r.trx_id JOIN information_schema.innodb_trx b ON w.BLOCKING_TRX_ID b.trx_id;这条SQL的核心价值是快速把业务上的“表现”和InnoDB内部的“锁持有者”对应起来。拿到blocking_thread之后可以通过performance_schema.threads去查这个线程当前正在执行的SQL看看它的事务到底在做什么有没有长时间存在但迟迟不提交的情况。很多线上锁等待的根因并不是某条SQL写得差而是某个线程开启事务后没结束一直握着锁不释放。3.4 锁等待不等于锁升级别把现象搞混还有一种常见误解是把锁等待当成“锁升级”。MySQL InnoDB不存在从行锁升级成表锁的机制这和某些数据库引擎实现方式不一样。你看到锁等待要么是行锁没有释放要么是行锁的范围天然比较大而不是因为它“升级”到了表锁。在我的排查经验里“锁升级”这个说法经常出现在和同行的沟通中但真正的故障原因通常是以下三个事务没提交、UPDATE语句扫描范围过大、以及高并发下同一批热点行被反复争抢。先厘清这个概念排查思路才不会跑偏。若确实发现某些表频繁整表锁等待去查表的索引情况大概率是某条写语句压根没走索引。4. 隔离级别悄悄改了你的事务锁范围从RR到RC到底动了什么4.1 默认RR的锁语义可重复读带来的不只是读一致性MySQL默认的事务隔离级别是REPEATABLE READ简称RR。很多开发知道“RR能保证同一事务内多次读结果一致”但忽略了它为此付出的锁代价——间隙锁和next-key lock就是RR的标配。间隙锁原本是为了防止幻读但它也会阻塞其他事务在范围内插入记录造成并发插入能力下降。举一个常见的业务场景订单表和订单明细表订单明细的插入非常频繁。如果这两张表用的是RR隔离级别一个正在执行“查询某订单所有明细”的事务如果查询走的是范围条件那么它可能会锁定order_id索引上的一部分间隙。此时其他事务想向同一订单范围内插入新的明细记录就会被阻塞。虽然这个查询本身可能只需要几毫秒但一旦事务迟迟不提交锁会一直被持有后续插入就会不断排队。RR的另一个隐藏影响是它在UPDATE和DELETE的当前读上同样使用next-key锁因此并发更新同一批数据时锁冲突概率比RC高得多。很多团队面对高并发写入压力第一步不是优化SQL而是先审视隔离级别设置这个方向是对的。4.2 降级到RC锁范围变小但代价在哪里READ COMMITTED隔离级别简称RC在InnoDB中被定义为“只锁记录不锁间隙”也就是说事务读取时会拿到已提交版本当前读只持有记录锁。这对于高并发插入和更新显然更友好但也带来两个需要接受的改变一是同一个事务内多次查询可能看到不同结果非重复读事务的一致性读特性变弱二是它与binlog的配合存在限制。MySQL的主从复制依赖binlog而binlog的中继模式分为STATEMENT、ROW、MIXED。旧版本里RC隔离级别配合基于SQL语句的STATEMENT格式备份时可能会出现主从数据不一致因为这依赖精确的SQL语句上下文。不过在MySQL 5.7之后默认binlog格式是ROW而且MIXED模式也足够安全所以RC在我国实际生产环境用得很广风险基本被规避了。实际操作时我更推荐先把业务的事务边界梳理清楚再决定要不要降级。如果业务对同一事务内快速重复读的一致性要求不高比如报表查询、订单查询等RC带来的锁缩小是值得的但如果有“同一个事务内必须看到自己刚修改后的数据和其他人提交的最新数据”这类强一致性需求RR更适合同时锁冲突只能靠优化SQL路径来缓解。4.3 什么时候降级什么时候不降判断标准不能局限于“并发高就降RC”。我的一般经验是先看写冲突的频繁程度。如果应用层已经频繁出现锁等待超时而且通过查询innodb_trx发现锁等待多集中在插入语句上那RC往往见效很快如果锁等待多集中在同一行的更新上RC的作用不大因为行锁依然存在该排队的还是要排队。还有一种场景不宜盲目降级涉及多表连接的复杂事务并且业务逻辑有意依赖于可重复读来确保数据一致性。比如订单支付后要同时更新订单状态、库存表、积分表如果事务里在更新订单表后立刻读取库存并进行判断RR可以保证在事务内看到的库存不会因为其他事务提交而变化RC则可能读到其他事务提交后的新值业务逻辑就要多写很多条件判断来兜底。数据库隔离级别的选择本质是“业务一致性与并发性能”的权衡不是一句“推荐RC”能概括的。5. 与锁冲突的优化策略索引设计、事务粒度与并发度三板斧5.1 索引选择性决定锁的物理范围锁是加在索引记录上的索引的设计直接决定一条写语句会锁多少行。这一点值得单独强调。索引选择性高例如订单号、用户ID这类高区分度字段一次UPDATE往往只命中一两行索引选择性低比如状态字段只有两个值一次UPDATE statuspending可能遗成千上万行锁的范围也跟着膨胀。所以优化锁冲突的第一个动作是检查SQL的执行计划EXPLAIN SELECT * FROM t WHERE order_statuspending;看key列和rows列如果优化器选择的索引只有几百行扫描锁范围大概率可控如果rows预估是几万那就要重新评估索引。一个反直觉的点是有时候覆盖索引比普通索引更能减少锁范围。因为UPDATE语句底层的“当前读”需要回表读取完整记录如果查询字段和WHERE条件全部覆盖在索引里可以减少回表记录数锁自然少一些。前面提到过的联合索引顺序也很重要。等值条件的字段应该放在联合索引最左侧范围条件的字段放后面。如果反过来范围字段在先可能会导致范围扫描把大量行卷进来。这个调整成本很低但收益常常比换数据库版本还要明显。5.2 缩短事务时间比调任何参数都有效锁等待的时长取决于持锁时间而持锁时间取决于事务的持续时长。一个事务从START TRANSACTION到COMMIT中间经历的每条SQL、每次网络交互、每个业务逻辑计算都会延长持锁时间。很多慢事务并不是单条SQL慢而是业务代码在事务里做了多余的事情。我在一个模拟项目里见过这样的代码Transactional public void pay(Order order) { orderDao.updateStatus(order.getId(), PAID); // 立即写入 pointsService.addPoints(order.getUserId(), 10); // 远程调用 smsService.sendNotify(order.getUserId()); // 再查一次DB inventoryDao.reduceStock(order.getProductId()); // 再次写库 }表面上一串操作逻辑完整但真正需要被数据库事务保护的只有两条写操作其他远程调用和耗时查询完全可以移出事务。把addPoints、sendNotify挪到事务提交后执行持锁时间减少了80%。代码review时我经常提醒团队成员事务里尽量只有“必须和本次写入保持一致”的操作其他都放到事务外。减少事务粒度的方法还包括把一批更新拆成多个小事务而不是一次性更新几十万行。大批量更新虽然可以用一条SQL搞定但锁持有时间太长系统并发会断崖式下降。拆分的方式可以是按主键范围分批每批几百行批次之间留一点间隔这样其他事务的写请求可以在批次间隙排队进入。5.3 并发度控制把冲突概率算出来索引和事务都优化到位后如果业务本身就有极高的热点行写入压力比如秒杀场景里所有请求都在减同一件商品的库存无论怎么调索引单行锁的并发上限就在那里。这时候要考虑的是让并发度与锁机制匹配。一个直观的冲突概率模型如果每秒有N个事务都要更新同一行平均持锁时间是T秒那么锁争抢的概率约等于N乘以T。N固定时唯一能做的就是减少TN太大时就需要在上游做合并把并发请求变成串行请求或者分桶。分桶是应对热点行的常见思路。比如库存更新不直接减一条记录而是拆成多个库存桶分布在不同行上程序按用户维度散列到某个桶每次只更新一个桶。这个方案牺牲了一点写逻辑的复杂度但让单行锁的竞争急剧下降。我在某个高并发活动的模拟项目中实践过类似方案把库存从一行变成20个桶锁等待超时数从每分钟几百次降到个位数。当然分桶要处理桶间不足时的合并查询属于“脏活”但收益非常可观。5.4 先做的三个检测配置优化动作不要一上来就改innodb_lock_wait_timeout。我推荐先做好三件事很多问题在观察阶段就能暴露。第一打开死锁检测记录确保死锁日志能落到错误日志中。InnoDB默认会做死锁检测但有些团队会把innodb_lock_wait_timeout调得很大导致死锁检测周期变长。第二开启慢查询日志并记录执行计划重点看锁等待期间的SQL。第三设置一个合理的事务超时时间比如innodb_lock_wait_timeout可以设置成5秒或更短让异常请求快速失败而不是堆在数据库连接池里发呆。这三个配置都不需要停机而且能让你在锁冲突发生时第一时间拿到可用的诊断数据。另外值得提一句不要随便把innodb_rollback_on_timeout改成0或1来规避问题。锁等待超时后事务默认不自动回滚只是报错如果你直接复用同一个事务继续执行数据状态可能和预期不一致后面的提交会带着“脏状态”一起写进去比原来的并发问题更难排查。6. 一次模拟项目的锁冲突复盘症状、定位与改进效果6.1 项目背景与初始症状前阵子帮朋友看了个模拟项目X一个典型的订单库存系统技术栈很普通MySQL 8.0默认RR隔离级别业务量并不大但每天有3到4次集中下单峰值。峰值期间应用日志开始大量出现两类报错“Deadlock found”和“Lock wait timeout exceeded”。开发者最开始怀疑数据库连接池不够调大了连接数结果情况更糟因为更多并发会话同时涌入数据库锁冲突不减反增。我接手时先看了三样东西应用日志里的报错频率、业务集中时段的innodb_trx表、以及慢查询日志。从中发现一个明显规律报错集中在“创建订单”和“更新库存”两个动作上而且基本都发生在同一批热门商品上。订单表用订单号做唯一索引商品表用商品ID做索引看似没问题但实际上INSERT订单和UPDATE库存两个动作在事务里交叉进行产生了一条我们常说的“锁环路”。6.2 定位过程从死锁日志到代码路径打开SHOW ENGINE INNODB STATUS死锁日志里能看到两个事务在交叉等待事务A持有了商品表某一行记录的锁等待订单表一个间隙锁。事务B持有了订单表某个间隙的锁等待商品表同一行记录锁。一开始代码里创建订单的顺序是先锁商品再插入订单。事务B创建另一个商品订单时先插入了订单数据再尝试锁商品。两者顺序不一致自然形成环路。定位到这一步问题不是索引也不是隔离级别而是应用层的事务内部操作顺序不统一。这是一个非常典型、也非常容易被忽略的根因——代码里两条互相独立的更新链路在并发时会因为“锁获取顺序不同”互相死锁。6.3 实施修改与前后对比修复方案有三条按成本从低到高是统一锁获取顺序、缩小事务粒度、隔离级别从RR降为RC。我建议优先做前两条降级RC作为最后手段。实际操作是所有涉及商品库存的更新在事务里都先按商品ID做排序后依次加锁同时把创建订单和扣减库存中不必要的读操作挪出事务。经过这些改动后死锁日志基本不再出现锁等待超时从峰值时段的每秒几十次降到偶尔一两次。这个案例给了我一个很深的感触MySQL锁机制的排查到最后往往不是数据库参数问题而是业务代码和事务编排问题。索引设计、事务边界、锁顺序这些才是决定并发表现的核心变量。参数调优只是收尾不是根因。最后再分享一个排查技巧遇到死锁或者锁等待先别急着改参数把SHOW ENGINE INNODB STATUS里的死锁段信息截图保存同时把innodb_trx的当前快照也存一份然后去代码里搜所有开启了事务的方法按锁获取顺序梳理一遍。大多数锁问题花在“看代码”上的时间比花在“调数据库”上的时间更值钱。