MySQL死锁测试与锁粒度优化：从压测复现到索引改造的完整复盘

做数据库死锁测试，绝大部分情况下都是被线上故障逼出来的，我这次也不例外。起因是压测环境并发一跑到128，死锁告警就开始刷屏，一个小时累计触发上百次，库存扣减和订单创建两类事务频繁回滚，应用连接池里塞满了等待锁的请求。为了把这个问题彻底弄清，我花了两个多星期，从构造死锁场景、复现问题开始，一路做到锁粒度优化和优化前后的对比验证。这份验证报告既是我自己的复盘，也希望给正在被数据库并发锁困扰的同行一点参考。无论你是后端开发还是DBA，只要线上出现过“Deadlock found when trying to get lock”，这套测试思路和优化路径都可以直接拿来用。

1. 压测现场被死锁逼出来的专项验证

先交代一下业务模型。我们有一个库存表和一个订单表，核心操作是“下单扣库存”。正常逻辑是先扣减库存，再插入一条订单记录，这两步必须放在同一个事务里，否则会出现库存扣了但订单丢了的脏数据。涉及的表结构简化如下：

CREATE TABLE inventory ( id BIGINT AUTO_INCREMENT PRIMARY KEY, sku_id VARCHAR(32) NOT NULL, warehouse_id VARCHAR(16) NOT NULL, stock_qty INT NOT NULL DEFAULT 0, UNIQUE KEY uk_sku_warehouse (sku_id, warehouse_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, order_no VARCHAR(32) NOT NULL, sku_id VARCHAR(32) NOT NULL, qty INT NOT NULL DEFAULT 1, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), UNIQUE KEY uk_order_no (order_no) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

环境是MySQL 8.0.36，InnoDB引擎，事务隔离级别用默认的可重复读（RR）。之所以不切读已提交（RC），是因为生产上就是RR，验证报告必须贴近真实环境，也要顺带观察间隙锁对死锁的影响。连接池用的HikariCP，压测机和数据库同机房，尽量避免网络抖动干扰锁等待数据。

1.1 死锁复现的两条路径：手动双会话与多线程压测

先从最直观的手动复现开始。死锁的本质是循环等待，你只需要把“两个事务各自握着一把锁，然后又去争抢对方手里的那把锁”这个过程完整摆出来。

终端一执行：

BEGIN; UPDATE inventory SET stock_qty = stock_qty - 1 WHERE sku_id = 'SKU001' AND warehouse_id = 'WH001';

终端二执行：

BEGIN; UPDATE inventory SET stock_qty = stock_qty - 1 WHERE sku_id = 'SKU002' AND warehouse_id = 'WH001';

此时两个事务各自拿到了SKU001和SKU002这两行库存记录的排他锁。接着，终端一继续插入订单，并尝试更新SKU002：

INSERT INTO orders(order_no, sku_id, qty) VALUES ('ORD-001', 'SKU001', 1); UPDATE inventory SET stock_qty = stock_qty - 1 WHERE sku_id = 'SKU002' AND warehouse_id = 'WH001';

这条更新会被终端二的事务堵住。然后终端二也插入订单，并尝试更新SKU001：

INSERT INTO orders(order_no, sku_id, qty) VALUES ('ORD-002', 'SKU002', 1); UPDATE inventory SET stock_qty = stock_qty - 1 WHERE sku_id = 'SKU001' AND warehouse_id = 'WH001';

到这一步，InnoDB的死锁检测机制会立刻发现循环等待，然后把其中一个事务回滚，另一个继续执行。手动复现的好处是时序完全可控，适合用来理解死锁产生的过程。它告诉我们：死锁测试的核心不是“撞大运”，而是构造出确定的锁顺序冲突。

手动能复现，自动化压测脚本就好写了。我准备了一个Python脚本，用多个线程模拟不同事务模板的混合流量。脚本的关键逻辑是：一部分线程走“先扣库存、后写订单”的模板，一部分线程走“先写订单、后扣库存”的模板，再加上随机SKU选择，让交叉时序在并发中被自然触发。

import random import threading import pymysql CONFIG = {"host": "127.0.0.1", "port": 3306, "user": "app", "password": "***", "database": "db_test"} SKUS = ["SKU%03d" % i for i in range(1, 51)] DEADLOCK_COUNT = 0 LOCK_WAIT_COUNT = 0 def do_transaction(conn, order_no, sku, template): global DEADLOCK_COUNT, LOCK_WAIT_COUNT try: conn.begin() with conn.cursor() as cur: if template == "inventory_first": cur.execute( "UPDATE inventory SET stock_qty = stock_qty - 1 " "WHERE sku_id=%s AND warehouse_id='WH001'", (sku,)) cur.execute( "INSERT INTO orders(order_no, sku_id, qty) " "VALUES (%s, %s, 1)", (order_no, sku)) else: cur.execute( "INSERT INTO orders(order_no, sku_id, qty) " "VALUES (%s, %s, 1)", (order_no, sku)) cur.execute( "UPDATE inventory SET stock_qty = stock_qty - 1 " "WHERE sku_id=%s AND warehouse_id='WH001'", (sku,)) conn.commit() except pymysql.err.OperationalError as e: if e.args[0] == 1213: # Deadlock found DEADLOCK_COUNT += 1 elif e.args[0] == 1205: # Lock wait timeout exceeded LOCK_WAIT_COUNT += 1 conn.rollback()

写压测脚本时有个小技巧：为了快速暴露问题，可以在两个SQL之间加一个1到5毫秒的随机延时，把冲突窗口人为放大。这个sleep纯粹是测试阶段的放大手段，生产代码千万不要加。不加延时时，死锁可能需要并发到256才频繁出现；加了之后，64并发就能稳定复现。

1.2 统计口径：死锁次数和锁等待次数要分开看

很多人在验证锁优化效果时只盯着死锁次数，这不够。死锁是循环等待后被InnoDB主动回滚，锁等待则是事务在排队等一把别人持有的锁，两者性质不同。我同时采集了下面的指标：

另外，MySQL默认只在内存里保留最后一次死锁信息，也就是SHOW ENGINE INNODB STATUS里的LATEST DETECTED DEADLOCK。要拿到全部死锁记录，我在压测期间开了innodb_print_all_deadlocks=ON，这样每次死锁都会写到错误日志，方便事后统计和逐条分析。这个参数在日常开发环境建议常开，生产环境死锁频率不高的话也建议开，日志量在可控范围，但它能救你于水火。

2. 根因定位，从 InnoDB 状态报告反向还原加锁现场

复现出问题之后，最忌讳的是看一眼SQL就凭感觉下结论。死锁的根因分析必须回到InnoDB的锁状态报告上去，那里记录了事务持有哪把锁、在等哪把锁，是还原现场的第一手材料。

2.1 死锁检测机制：InnoDB到底是怎么判定的

InnoDB内部维护了一张锁等待的有向图：每个事务是一个节点，事务A等待事务B持有的锁，就形成一条A指向B的边。每次加锁产生等待时，InnoDB都会检查这张图里有没有环，一旦发现环，就是死锁。它不会干等着超时，而是选择一个回滚代价较小的事务回滚，释放它持有的锁，让另一个事务继续执行。

这个过程主要受两个参数影响：

有人会问：死锁检测有开销，并发极高时能不能关掉省点CPU？我的建议是生产环境别关。如果关掉死锁检测，两个事务相互等待时只能等innodb_lock_wait_timeout超时，50秒对在线业务来说是不可接受的，堆叠起来的线程会把整个服务拖垮。相比之下，死锁检测回滚一个事务，代价小得多。

2.2 读死锁日志：HOLDS THE LOCK 和 WAITING FOR 是关键词

一次典型的死锁日志长这样，我基于MySQL 8.0的输出稍作精简，关键字段都保留了：

------------------------ LATEST DETECTED DEADLOCK ------------------------ 2025-01-15 14:23:08 0x7f3a1c0c9700 *** (1) TRANSACTION: TRANSACTION 29041, ACTIVE 1 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1160, 1 row lock(s) MySQL thread id 83, OS thread handle 139763840000000, query id 17890 127.0.0.1 app_user updating UPDATE inventory SET stock_qty = stock_qty - 1 WHERE sku_id = 'SKU001' AND warehouse_id = 'WH001' *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 3 page no 5 n bits 80 index uk_sku_warehouse of table `db_test`.`inventory` trx id 29041 lock_mode X locks rec but not gap *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 3 page no 6 n bits 80 index uk_sku_warehouse of table `db_test`.`inventory` trx id 29041 lock_mode X locks rec but not gap *** (2) TRANSACTION: TRANSACTION 29044, ACTIVE 1 sec starting index read mysql tables in use 1, locked 1 2 lock struct(s), heap size 1160, 1 row lock(s) MySQL thread id 87, OS thread handle 139763840500000, query id 17893 127.0.0.1 app_user updating UPDATE inventory SET stock_qty = stock_qty - 1 WHERE sku_id = 'SKU002' AND warehouse_id = 'WH001' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 3 page no 6 n bits 80 index uk_sku_warehouse of table `db_test`.`inventory` trx id 29044 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 3 page no 5 n bits 80 index uk_sku_warehouse of table `db_test`.`inventory` trx id 29044 lock_mode X locks rec but not gap *** WE ROLL BACK TRANSACTION (1)

怎么读这份日志？我的习惯是三步：

第一步，看TRANSACTION后面的SQL文本，它告诉你这个事务是在哪条语句上出事的。上面事务1卡在更新SKU001，事务2卡在更新SKU002。

第二步，看HOLDS THE LOCK(S)。事务1HOLD的是page no 5上uk_sku_warehouse索引的X锁，事务2HOLD的是page no 6上同一个索引的X锁。两人各有各的地盘。

第三步，看WAITING FOR THIS LOCK TO BE GRANTED。事务1等在page no 6，也就是事务2握着的那一行；事务2等在page no 5，也就是事务1握着的那一行。这就是教科书式的循环等待。

日志末尾的WE ROLL BACK TRANSACTION (1)表示InnoDB最终回滚了事务1，应用层会收到一个1213错误码。回滚哪个事务取决于它计算出的“代价”，通常修改行数少、undo量小的事务会被牺牲掉。所以应用层绝对不能假设自己的事务一定会被保全，1213错误码的捕获和重试是必须的。

2.3 三条根因结论，把死锁来源彻底拆开

日志看多了之后，我们把自己的死锁情况汇总了一下，根因集中为三条。

第一条，加锁顺序不一致。业务代码里存在两种事务模板：一种先扣库存再写订单，一种先写订单再扣库存。两个事务如果同时操作不同SKU，各自先拿了一把锁，再申请对方手里的锁，循环等待就成立了。这是最典型、也最容易修的死锁来源。

第二条，锁范围大于预期。排查EXPLAIN时发现，部分库存更新SQL没有走uk_sku_warehouse索引，而是走了全表扫描。前面说过，InnoDB的行锁是挂在索引记录上的，不走索引的更新会扫描并锁住大量匹配到的记录，一个事务实际锁的“行”不是1行，而是几千行。这时就算两个事务操作的是不同的SKU，也可能因为扫描路径重叠而产生锁竞争。

第三条，RR隔离级别下，间隙锁和Next-Key Lock参与交叉。默认RR下，范围查询会带上间隙锁，插入操作还会申请插入意向锁。锁的种类变多，交叉点自然变多。这一条不是每个死锁都能命中，但它解释了很多“更新语句明明没交集，却还是死锁”的诡异现象。

3. 锁粒度改造的三板斧：顺序、索引、事务时长

定位完根因，接下来才是重头戏。锁粒度优化的目标，不是简单把表锁换成行锁，而是让每个事务实际锁住的索引记录数最少、持有时间最短、申请顺序全局一致。我按这三条原则做了三轮改造。

3.1 先澄清一个误区：行锁并不天然优于表锁

先把锁粒度的基本概念理清楚。表锁的粒度是一张表，并发能力差，但开销小，管理简单。行锁的粒度是一行记录，并发能力强，但每个记录锁都需要锁结构内存，加锁和解锁的开销也更大。InnoDB选择行锁是因为OLTP场景大部分请求只涉及少量行，平均下来收益大于成本。

关键在于：InnoDB的行锁不是直接锁在一行行数据上，而是锁在索引记录上。一张表如果查询条件走不上索引，存储引擎就只能把扫描过程中遇到的记录挨个加锁。你以为自己在做行级锁控制，实际效果和表锁差不多，甚至因为锁结构多，开销比表锁还高。

打个比方：把一栋楼装上门禁系统，你觉得每间房单独管理很精细，但管理员开门时没看门牌号，从一楼挨个试着开到了顶楼，过程中整栋楼的所有房间都显示“已锁”。这就是索引失效时InnoDB干的事情。所以锁粒度优化的本质，是保证管理员每次只经过目标房间，而不是把门禁装得更密。

3.2 第一板斧：统一多表操作加锁顺序

针对第一条根因，改造方案很直接：所有涉及库存和订单的事务，统一走“先库存、后订单”的顺序。代码层面不靠自觉，而是封装事务模板方法，不允许业务方自由拼接SQL顺序。

这里我踩过一个坑：只统一业务入口还不够。有些定时任务和补偿任务直接写SQL，绕过了服务层模板，又把顺序打乱了。所以我还把所有直连数据库的脚本、批处理任务全部盘点了一遍，凡是同时操作多张表的，都固定顺序。涉及更多资源时，可以维护一张全局资源顺序表，例如按表名排序，大家都按这张表的顺序加锁。

统一顺序为什么有效？因为死锁的四个必要条件里，互斥、持有并等待、不可剥夺都是InnoDB的行锁机制客观决定的，我们改不了，但“循环等待”是可以打破的。所有事务都按同一顺序加锁，等待关系只会是单向链，形不成环，死锁自然消失。

3.3 第二板斧：把 update 的 where 条件打到索引上

针对第二条根因，改造重点是索引。原始库存表只在主键id上有索引，但业务上查询条件是sku_id + warehouse_id。压测数据量到百万行之后，这条update直接全表扫描。我加了一个联合唯一索引：

ALTER TABLE inventory ADD UNIQUE KEY uk_sku_warehouse (sku_id, warehouse_id);

索引加完之后，执行计划的变化非常直观。优化前是全表扫描：

mysql> EXPLAIN UPDATE inventory SET stock_qty = stock_qty - 1 WHERE sku_id='SKU001' AND warehouse_id='WH001'; +----+-------------+-----------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | UPDATE | inventory | ALL | NULL | NULL | NULL | NULL | 1000000 | 0.00 | Using where | +----+-------------+-----------+------+---------------+------+---------+------+---------+----------+-------------+

优化后命中了联合唯一索引，rows直接从一百万降到1：

+----+-------------+-----------+------+---------------+-----------------+---------+-------------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------+---------------+-----------------+---------+-------------+------+----------+-------+ | 1 | UPDATE | inventory | ref | uk_sku_warehouse | uk_sku_warehouse | 70 | const,const | 1 | 100.00 | NULL | +----+-------------+-----------+------+---------------+-----------------+---------+-------------+------+----------+-------+

加锁范围从“全表扫描命中的所有记录”缩小到“唯一索引定位到的1行”，这一步是死锁次数大幅下降的最关键因素。这里我还想多说一句：联合索引的字段顺序很重要，必须把等值条件的字段放前面，才能让索引快速收敛。验证阶段务必用EXPLAIN看实际计划，而不是凭直觉认为“建了索引就一定会用”。

3.4 第三板斧：事务裁剪，缩短锁的持有时间

第三条根因靠隔离级别调整可以缓解，但我这次没有改隔离级别，而是先做了事务裁剪，效果已经足够。RR对业务有正确性保证，改RC需要团队评估，我不愿意为了消死锁引入新的语义风险。

事务裁剪的核心是：锁持有时间越短，死锁窗口越窄。很多事务里塞了大量无关操作，最典型的是下面这种写法：

BEGIN; SELECT stock_qty FROM inventory WHERE sku_id = 'SKU001' FOR UPDATE; -- 业务代码在这里做复杂校验、调用外部接口、计算优惠金额 UPDATE inventory SET stock_qty = stock_qty - 1 WHERE sku_id = 'SKU001'; COMMIT;

SELECT FOR UPDATE拿到锁之后，Java代码里可能还要调用优惠服务、计算运费、写日志，这些时间全是白花花的锁持有时间。正确的做法是：事务外先准备好所有数据，事务里只做必要的锁和更新，外部调用一律放到事务外。大批量场景也要分片，我压测时一次更新500条和一次更新5000条，锁等待时长能差出好几倍。

4. 优化前后数据对比：并发从32到256的验证结果

改造完成之后，我用同一套压测脚本重新跑了一轮。数据来自我自己的测试环境，不是硬件性能基准，绝对值参考意义有限，但优化前后是同一环境、同一脚本、同一数据量，相对变化是可信的。

4.1 测试口径先讲清楚

压测前重置库存和订单表，50个SKU，每个SKU初始库存1000。每轮跑1000个事务，线程数分别固定在32、64、128、256，每种并发重复10轮，取中位数。连接池大小跟随线程数调整，TPS和p99从脚本端统计。这里有个容易忽略的细节：压测前必须清理死锁相关的状态计数，否则累加值会把上一轮的数据带进来，统计结果会失真。

4.2 死锁次数与核心性能指标

优化前的数据，说句实话，惨不忍睹。并发256时10轮累计死锁500多次，几乎每轮都在触发。优化后整体收敛到个位数级别：

锁等待的改善同样明显：

需要说明的是，这些数值对硬件配置、数据分布、SQL写法都极其敏感，直接抄作业没有意义。真正有价值的结论是趋势：死锁次数下降一个数量级以上，锁等待次数下降一个数量级，p99延迟下降70%左右。

4.3 怎么解读这组数据

p99从430ms降到116ms，体感会非常明显。过去高并发时段接口经常超时，现在尾部延迟被削掉一大截，这比TPS数字的提升更能说明问题。

但我必须提醒一句：优化后并发256仍然出现了4次死锁。这4次谁干的？我翻了全部死锁日志，清一色是同一个SKU的热点行竞争——两个事务同时更新同一行库存记录。这是业务天然冲突，跟锁顺序、索引覆盖率没关系。你没法通过锁粒度优化让两个事务同时写同一行还不打架，只能通过重试机制兜底，或者把热点行的串行化需求交给上层队列处理。

另外我还观察到一个现象：优化前并发从128升到256，TPS几乎不涨，从3050只爬到3180，说明系统瓶颈已经从“执行事务”退化成了“排队等锁”。优化后同样的并发跨度，TPS从4240涨到4710，虽然涨幅也在收窄，但至少没有出现平台期。这就是锁竞争对吞吐量的真实压制。

5. 锁相关的几个高频坑，和一套能落地的日常治理思路

验证报告写到这里，优化的结论已经明确。但我不想停在数据层面，锁问题真正的难点在于日常防不胜防。下面这几个坑是我在实际工作中反复遇到的，都跟锁粒度和死锁直接相关。

5.1 批量更新的顺序坑

夜间跑批是死锁高发场景。两个批处理任务同时在更新库存表：任务A按sku_id升序处理，任务B按id升序处理。它们的目标行集合有重叠，但加锁顺序完全不同，跑着跑着就互相踩脚。

解决方式很朴素：批量任务统一排序规则，按同一个字段升序处理。最稳妥的做法不是直接对UPDATE语句加ORDER BY，而是先把要更新的主键或唯一键按固定顺序查出来，再按这个顺序分批执行更新。这样无论多少个任务并发，加锁顺序都是一致的。

# 示例：先按固定顺序取出要更新的主键，再按顺序更新 ids = select_ids_order_by_sku("SKU001", "WH001", limit=1000) for id_ in ids: update_stock_by_id(id_, -1)

5.2 1213错误码必须捕获并重试

死锁优化做得再好，也做不到绝对零死锁。热点行竞争、突发流量、临时表查询，都可能在某个瞬间触发循环等待。所以应用层必须把1213当成一种正常情况对待。

重试要有限次、带随机退避，避免所有线程同步重试造成二次锁风暴。我常用的Python模板是：

for i in range(3): try: with conn: with conn.cursor() as cur: cur.execute(update_sql, params) break except pymysql.err.OperationalError as e: if e.args[0] == 1213 and i < 2: time.sleep(random.uniform(0.01, 0.05)) continue raise

注意，不是所有失败都能重试。如果是锁等待超时（1205），多半是当前SQL本身太重或者长时间持锁，盲目重试意义不大，需要先排查SQL执行计划。只有1213死锁回滚，重试才是有明确收益的。

5.3 监控比事后排查重要得多

死锁问题最怕的是“不触发就永远不知道”。我在这次专项验证之后，把下面几项落成了常态化监控：一是innodb_print_all_deadlocks=ON，保证每一次死锁都进错误日志；二是定期采集错误日志里的死锁段落到监控平台，死锁次数超过阈值就告警；三是用performance_schema.data_lock_waits观察当前锁等待情况；四是慢查询日志里重点看lock_time高的SQL。

这套组合拳的价值在于，死锁不再靠用户投诉才发现，而是能在压测阶段、灰度阶段就被拦截。等它到了生产上大规模爆发，再牛的分析手段也是救火。

5.4 哪些场景值得绕开数据库锁

最后一个话题，什么时候不该硬扛数据库锁。纯热点计数型的场景，比如库存扣减、秒杀、点赞计数，数据库行锁的竞争天然激烈，锁粒度优化只能缓解，不能根除。这类场景更合适的方案是：Redis预扣库存 + 异步对账 + MQ队列串行化，把高频写操作从数据库里剥离出来。还有乐观锁CAS写法，适合简单的扣减校验：

UPDATE inventory SET stock_qty = stock_qty - 1 WHERE sku_id = 'SKU001' AND stock_qty >= 1;

影响行数为1表示扣减成功，为0表示库存不足。这个方案没有锁等待，也不会有死锁，代价是并发竞争时大量请求更新失败，需要上层快速返回或重试。

但我也要泼一盆冷水：不是所有死锁都要用缓存和消息队列去解。引入一套新的链路，意味着数据一致性、缓存失效、消息重复处理的复杂度全部跟着上来。数据库锁虽然看着笨重，但它的确定性是很大的优势——事务要么提交要么回滚，语义清晰。对大多数普通业务场景来说，做好顺序统一、索引下推、事务裁剪这三件事，已经能把死锁问题压到足够低的水平。

经历过这轮专项验证之后，我最大的变化是：线上再报数据库死锁，第一反应不再是去调死锁检测参数，而是去看这条update到底锁了多少行、几个事务的加锁顺序能不能统一、锁在事务里被持有多长时间。锁粒度优化不是某个数据库配置项能一劳永逸解决的，它需要把代码路径、索引设计和事务边界放在一起同时改对。最后再分享一个小技巧：任何锁相关的优化方案，上线前都先做一轮带死锁统计的压测验证，用数据说话，比什么都可信。