运营要把一批已发货超过 7 天的历史订单状态统一改成已完成约 40 万条,我图省事写了一条 UPDATE 把这 40 万行一次性更新,脚本跑约 40 秒,这 40 秒里整个电商的下单支付接口大面积超时,我改的是历史老订单和正在下单的新用户毫不相干凭什么把前台也搞挂。排查梳理:应用日志超时报的是 Lock wait timeout exceeded 关键词是等锁不是 DB 慢,SHOW PROCESSLIST 看到一个 UPDATE 跑了 38 秒后面几十个连接全在等,查 sys.innodb_lock_waits 看到 blocking 的就是我那条大 UPDATE,查 innodb_trx 看到这一个事务 trx_rows_locked 锁了 40 多万行;核心根因 autocommit 下每条单独的 UPDATE 都被自动包成一个事务一条改 40 万行的 UPDATE 就是一个跑 40 秒的大事务,InnoDB 两阶段锁铁律事务执行中改到的行就加锁锁一直持有到 COMMIT 才释放不是改完一行放一行,所以全程 40 秒攥着 40 万把锁一把不放,把无关请求牵连进来的不是数据是锁;InnoDB 的锁不只有行锁还有间隙锁锁的是行与行之间的空隙间隙被锁后往里 INSERT 新行会被挡住哪怕那行还不存在,Next-Key Lock 等于行锁加间隙锁 RR 隔离级别范围扫描就加它范围 UPDATE 会锁住整段范围连同间隙新订单 INSERT 进来就撞锁,WHERE 没索引会全表扫可能锁全表;治本是化整为零大批量写必须分批按主键 id 区间切每批 500 到 2000 行每批一个小事务逐批 COMMIT 锁立刻释放,别用 LIMIT OFFSET 翻页,批与批之间 sleep 主动让路;大事务其他伤害 undo log 暴涨拖慢全库快照读、主从延迟飙升、长时间占满连接池、中途回滚比执行还慢。正确做法是接口超时先分清 DB 慢还是等锁,大批量写务必按主键分批逐批提交,以及一套 MySQL 大事务排查纪律。
2022 年,一次"运营让我批量改 40 万条订单状态,我图省事写在一个事务里,结果整个电商的下单、支付接口大面积超时近一分钟"的事故,把我对"事务"这件事的理解,从头到尾翻新了一遍。需求很简单:运营要把一批"已发货超过 7 天"的历史订单,状态统一改成"已完成",大约 40 万条。我写了个脚本,一条 UPDATE orders SET status='done' WHERE ...,把这 40 万行一次性更新掉。在我脑子里,这就是一次"批量写",慢一点而已,跑完就好。脚本一跑,我盯着它,大概 40 秒后跑完,返回"40 万行受影响"。我正要松口气,告警群已经炸了:这 40 秒里,整个站的下单接口、支付接口,大面积超时,用户那边一片"系统繁忙"。我懵了。我那条 UPDATE,改的是"历史订单"——那些是几天前、几周前的老单子,跟此刻正在下单的新用户,八竿子打不着。我只是在后台默默地改一批没人看的老数据,凭什么会把前台正在下单的人也一起搞挂?如果我改的是 A 那批数据,为什么 B 那批数据(完全不同的行)上的操作,也跟着卡死了?它们之间,到底有什么我没看见的东西,被我那条 UPDATE,一根线串住了?这件事逼着我把"大事务"到底有多大破坏力、InnoDB 的行锁与间隙锁、为什么"没碰到的行"也会被锁、大批量写为什么必须分批提交,彻底理清了。本文复盘这次实战。
问题背景
环境:MySQL 5.7(InnoDB),一个电商库,orders 表约 2000 万行
事故现象:
- 后台跑一条 UPDATE,一次改约 40 万行历史订单状态
- ★ 这条 UPDATE 执行约 40 秒
- ★★ 这 40 秒里,前台下单 / 支付接口大面积超时
- 改的是"历史订单",和正在下单的新用户毫不相干
现场排查:
# 1. ★ 应用日志:超时的接口,报的是什么错
$ grep -i 'timeout' app.log | tail
# Lock wait timeout exceeded; try restarting transaction
# ★★ 关键词:Lock wait —— 不是 DB 慢,是在【等锁】
# 2. ★ 事故当时,看 MySQL 在跑什么
mysql> SHOW PROCESSLIST;
# Id Command Time State Info
# 1021 Query 38 updating UPDATE orders SET status... ★ 它
# 1044 Query 35 Updating UPDATE orders SET ... id=... ← 在等
# 1051 Query 33 update INSERT INTO orders ... ← 在等
# 1063 Query 31 Updating UPDATE orders SET ... id=... ← 在等
# ... 几十个连接,Time 都是几十秒,全卡着 ...
# ★★ 一个 UPDATE 跑了 38 秒,后面一长串请求全在等它
# 3. ★ 看到底是谁在等谁的锁
mysql> SELECT * FROM sys.innodb_lock_waits\G
# waiting_query: UPDATE orders SET ... WHERE id=8801234
# blocking_query: UPDATE orders SET status='done' WHERE ... ★ 元凶
# ★★ 几十个事务,全在等我那条大 UPDATE 持有的锁
# 4. ★ 看我那条 UPDATE 的事务,持有了多少锁
mysql> SELECT trx_id,trx_rows_locked,trx_rows_modified
FROM information_schema.innodb_trx;
# trx_rows_locked: 410000+ ★★ 它一个事务锁了 40 多万行!
根因(后来想清楚的):
1. ★ 一条 UPDATE,在 InnoDB 里就是一个【事务】。这条
UPDATE 不跑完、不提交,事务就一直【开着】。
2. ★★ 事务一开始,它就给【改到的每一行】都加上了
写锁(行锁)。而且这些锁,要【一直持有到事务
提交】的那一刻 —— 不是改完一行放一行。
3. ★★ 于是这 40 秒里,那 40 万行,全程被锁死。任何
别的事务想改这中间任何一行,都得排队等。
4. ★ 更隐蔽:UPDATE 的 WHERE 不是走主键精确匹配,
InnoDB 加的是【间隙锁 / Next-Key Lock】—— 它锁的
不只是"存在的行",还有行与行之间的"间隙"。新
订单要 INSERT 进这个范围,也被挡在门外。
5. ★ 所以前台下单(INSERT)、改单(UPDATE)只要落在
这个范围,全部撞锁、排队、最后超时。
真相:一条改 40 万行的 UPDATE = 一个持有 40 万把锁、
长达 40 秒的【大事务】。大事务的锁,会牵连一大片
看似无关的请求。批量写必须【拆小、分批提交】。修复 1:接口大面积超时——先确认是不是在"等锁"
# === ★ 接口超时,第一刀:分清是"DB 慢"还是"在等锁" ===
# === ★ "数据库慢"和"在等锁",是两回事 ===
# ★ 接口超时,大家第一反应是"数据库扛不住了""DB
# 慢"。但有一种超时,DB 一点都不慢 —— 它在【等锁】。
# ★ ★ 区别:
# - DB 真慢:SQL 本身执行慢(缺索引、全表扫、数据
# 量大),CPU / 磁盘 IO 打满。
# - 在等锁:SQL 本身可能快得很,但它要改的行被
# 【别的事务锁着】,它只能干等 —— 等到超时。
# ★ 这两种,病因和解法天差地别,先分清。
# === ★ 信号 1:错误信息里的关键词 ===
# ★ 应用日志 / DB 报错里,如果出现:
# "Lock wait timeout exceeded"
# —— ★★ 这五个字基本就锁定了:是【等锁】,不是 DB 慢。
# ★ 如果是 "Deadlock found" —— 那是死锁(两个事务
# 互相等对方),又是另一回事。
# === ★ 信号 2:SHOW PROCESSLIST 看 State ===
mysql> SHOW PROCESSLIST;
# ★ 重点看 State 列和 Time 列:
# - 一堆连接 Time 几十秒,State 卡在 updating /
# Updating / "update" —— 它们在等;
# - ★ 找那个 Time 最长、还在 "updating" 的,八成
# 就是【持锁不放】的元凶事务。
# === ★ 信号 3:直接查"谁在等谁" ===
# ★ MySQL 5.7+ 有现成的视图,一查就知道阻塞链:
mysql> SELECT * FROM sys.innodb_lock_waits\G
# ★ 它直接告诉你:waiting 的是哪条 SQL,blocking
# 的(罪魁)是哪条 SQL、哪个事务 ID。
# ★ MySQL 8.0 用 performance_schema.data_lock_waits。
# === ★ 信号 4:看事务锁了多少行 ===
mysql> SELECT trx_id,trx_started,trx_rows_locked,trx_rows_modified,
trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;
# ★ trx_rows_locked 特别大(几万、几十万)、trx_started
# 是很久以前 —— 这就是个正在作恶的【大事务 / 长事务】。
# === 认知 ===
# ★ 接口超时先分清"DB 慢"还是"在等锁":DB 慢是 SQL
# 本身执行慢,等锁是 SQL 不慢但要改的行被别的事务
# 锁着。★★ 四个信号:① 报错出现 "Lock wait timeout
# exceeded" 基本锁定是等锁;② SHOW PROCESSLIST 看
# 一堆连接 State 卡在 updating;③ 查 sys.innodb_lock_waits
# 看谁阻塞谁;④ 查 information_schema.innodb_trx 看
# trx_rows_locked,特别大就是大事务在作恶。修复 2:核心根因——一条 UPDATE 40 万行,就是一个"大事务"
# === ★ 把这次事故的总根,挖出来 ===
# === ★ 一条没显式 BEGIN 的 UPDATE,也是一个事务 ===
# ★ 很多人以为"事务"是要手写 BEGIN ... COMMIT 才有的。
# ★ ★ 错。InnoDB 下,只要 autocommit=ON,你执行的
# 【每一条】单独的 INSERT/UPDATE/DELETE,MySQL 都会
# 【自动把它包成一个事务】:语句开始 = 事务开始,
# 语句成功结束 = 自动 COMMIT。
# ★ ★★ 所以我那条 "UPDATE ... 40 万行",它【就是
# 一个事务】—— 一个要改 40 万行、跑 40 秒才提交的
# 【大事务】。我以为我在做"批量写",其实我在做
# "一个超大事务"。
# === ★★ 事务的锁:加了,就持有到 COMMIT ===
# ★ 这是理解大事务危害的关键。InnoDB 的写锁(行锁)
# 遵守一条铁律 —— 两阶段锁(2PL):
# - ★ 事务执行过程中,改到一行,就给这行【加锁】;
# - ★★ 加上的锁,【不会改完就放】,而是一直【持有
# 到整个事务 COMMIT / ROLLBACK】的那一刻,才一起释放。
# ★ ★ 所以我那条 UPDATE:它改第 1 行时加的锁,要
# 一直攥到第 40 万行也改完、整条语句提交,才松手。
# 也就是说,执行的全程 40 秒,40 万把锁,一把没放。
# === ★ 于是,"看似无关却被牵连"解释清楚了 ===
# ★ 我改的是"历史订单",新用户下的是"新订单" ——
# 数据上确实不相干。但:
# - 它们在【同一张表】里;
# - 我那条 UPDATE 的 WHERE 范围(后面会讲),锁住的
# 不只是 40 万行本身,还有它们之间的【间隙】;
# - 新订单 INSERT 进来,主键 id 是自增的,正好落在
# 我锁住的范围/间隙里 -> 撞锁。
# ★ ★ 一句话:把它们"串"在一起的,不是数据,是
# 【锁】。大事务持锁太多、太久,锁就成了一根
# 牵连一大片的线。
# === ★ "大"事务,大在两个维度 ===
# ★ ① 改的行多 —— 锁的【数量】多,波及面广;
# ★ ② 持续时间长 —— 锁的【时长】久,别人等得久。
# ★ ★ 这两个维度,任意一个超标,都是"大事务"。
# 一条 UPDATE 40 万行,是两个维度【同时】爆表。
# === 认知 ===
# ★ autocommit 下每条单独的 UPDATE/INSERT/DELETE 都被
# 自动包成一个事务 —— 一条改 40 万行的 UPDATE 就是
# 一个跑 40 秒的【大事务】。★★ InnoDB 两阶段锁铁律:
# 事务执行中改到的行就加锁,锁【一直持有到 COMMIT】
# 才释放,不是改完一行放一行。所以这条 UPDATE 全程
# 40 秒攥着 40 万把锁一把不放。把"无关请求"牵连进来
# 的不是数据是【锁】。大事务大在两维:改的行多 +
# 持续时间长,这条 UPDATE 两维同时爆表。修复 3:InnoDB 锁的真相——为什么"没碰到的行"也被锁
# === ★ 这一节讲透:间隙锁,新订单为什么进不来 ===
# === ★ 行锁:锁的是"已经存在的那一行" ===
# ★ 最好理解的是行锁(Record Lock):你 UPDATE 改了
# id=100 这行,InnoDB 就给 id=100 这条记录加锁。
# ★ 别的事务想改 id=100,等;想改 id=200,不受影响。
# —— 如果世界上只有行锁,我那条 UPDATE 顶多挡住
# "改这 40 万行"的操作,挡不住"INSERT 新行"。
# === ★★ 间隙锁:锁的是"行与行之间的空隙" ===
# ★ 但 InnoDB 在【可重复读(RR,MySQL 默认)】隔离
# 级别下,为了防"幻读",还有一种锁叫【间隙锁】
# (Gap Lock)。
# ★ ★ 间隙锁锁的不是某一行,而是【两行之间的那段
# 空隙】。比如表里现有 id 是 ...100, 200, 300...,
# 那 (100,200) 就是一个间隙。
# ★ ★★ 关键:一旦 (100,200) 这个间隙被锁,任何事务
# 想往这个间隙里【INSERT 一行】(比如 id=150),
# 就会被【挡住、排队】—— 哪怕 id=150 这行此刻
# 【根本还不存在】!"没碰到的行"被锁,就是这么来的。
# === ★ Next-Key Lock = 行锁 + 间隙锁 ===
# ★ InnoDB 在 RR 下,范围扫描时实际加的,是
# 【Next-Key Lock】—— 它 = 记录锁 + 它前面的间隙锁,
# 锁的是"一行 + 它前面那段空隙"。
# ★ ★ 我那条 UPDATE 的 WHERE 是个【范围条件】(发货
# 时间 < 某日期),它要扫一大段记录 -> 沿途一路
# 加 Next-Key Lock -> 把这一【整段范围连同所有间隙】
# 全锁了。新订单的 id 落在这段范围内,INSERT 直接
# 撞间隙锁。这就是前台下单被挂住的直接原因。
# === ★ 范围越界、WHERE 没索引,锁会扩散得更狠 ===
# ★ ★ 致命加成:如果 UPDATE 的 WHERE 列【没有索引】,
# InnoDB 没法精确定位,只能【全表扫描】—— 扫的
# 过程中,它会把【扫过的每一行 + 每个间隙】都加锁。
# ★ ★★ 结果:一条本意只想改 40 万行的 UPDATE,因为
# WHERE 没走索引,可能把【整张表】都锁了。所以:
# 批量 UPDATE/DELETE 的 WHERE 条件,★ 必须走索引。
# === 认知 ===
# ★ InnoDB 的锁不只有"行锁":① 行锁(Record Lock)
# 锁已存在的某行;②★★ 间隙锁(Gap Lock)锁的是
# 行与行之间的【空隙】,间隙被锁后往里 INSERT 新行
# 会被挡住 —— 哪怕那行还不存在,这就是"没碰到的行
# 也被锁";③ Next-Key Lock = 行锁 + 间隙锁,RR 隔离
# 级别下范围扫描就加它。★★ 范围 UPDATE 会沿途锁住
# 整段范围连同间隙,新数据 INSERT 进来就撞锁;★ 若
# WHERE 没索引会全表扫,可能锁全表 —— 批量写 WHERE
# 必须走索引。-- 亲眼看间隙锁挡住 INSERT(两个会话演示)
-- 表里 orders 现有 id: 100, 200, 300
-- 【会话 A】开一个事务,范围更新
mysql> BEGIN;
mysql> UPDATE orders SET status='done' WHERE id BETWEEN 100 AND 300;
-- ★ A 没提交,(100,300) 这段范围连同间隙都被锁住
-- 【会话 B】此时想插一条 id=150 的新订单
mysql> INSERT INTO orders(id,status) VALUES(150,'new');
-- ★★ B 卡住了!id=150 这行明明还不存在,却被 A 的
-- 间隙锁挡在外面 —— 一直等到 A 提交,或等到超时:
-- ERROR 1205: Lock wait timeout exceeded
-- ★ 结论:A 那个没提交的事务,锁的不是 3 行,是
-- 整个 (100,300) 区间。B 的"无关 INSERT"被牵连。修复 4:治本——大批量写,必须拆小、分批提交
# === ★ 根因是"一个事务太大",治本就是把它拆小 ===
# === ★ 核心思路:化整为零,一大批拆成很多小批 ===
# ★ 与其 "一条 UPDATE 改 40 万行 / 一个事务",改成
# "改 1000 行就 COMMIT 一次,循环 400 次"。
# ★ ★ 为什么这就解决了问题:
# - 每个小事务,只锁 1000 行,只持续几十毫秒;
# - ★★ 一 COMMIT,这 1000 把锁【立刻全放】—— 在
# 两次小批之间,别的请求有大把机会挤进去执行;
# - 锁的"数量"和"时长"两个维度,一起被摁了下去。
# ★ 总耗时可能差不多甚至略长,但它【不再霸占全场】,
# 而是和正常业务【交替执行】,前台不再被挂死。
# === ★ 怎么分批:用主键,一段一段地切 ===
# ★ ★ 别用 LIMIT 分页(LIMIT 10000 OFFSET 390000 ——
# 越往后 OFFSET 越大,越扫越慢)。
# ★ ★★ 正确做法:按【主键 id】区间切。记住上一批
# 处理到的最大 id,下一批从这个 id 之后接着取。
# 每批 WHERE id > ? AND id <= ? —— 走主键索引,
# 每批都快,且锁范围小而明确。
# === ★ 每批之间,睡一小会儿 ===
# ★ 批与批之间,sleep 一个很短的时间(如 50~200ms)。
# ★ ★ 这口"喘息",是【主动让路】—— 给正常业务请求
# 留出执行窗口,也给主从复制留出追赶的时间。别让
# 你的批处理把 DB 的资源 100% 占满。
# === ★ 批的大小:不是越大越好,也不是越小越好 ===
# ★ 太大(如 10 万一批):又变回小一号的大事务;
# ★ 太小(如 10 行一批):COMMIT 太频繁,事务开销
# 占比高,总耗时拖长。
# ★ ★ 经验:每批 500~2000 行,是个常见的稳妥区间。
# 按你的行大小、库的繁忙程度调。
# === ★ 别忘了:WHERE 一定走索引 ===
# ★ 分批的每条 SQL,WHERE 条件必须命中索引(按主键
# 切就天然走主键索引)。否则每批都是一次小型全表
# 扫,锁还是会扩散。
# === 认知 ===
# ★ 大事务治本就是【化整为零】:一条改 40 万行的
# UPDATE,改成"每 1000 行 COMMIT 一次循环 400 次"。
# ★★ 关键收益:每个小事务只锁 1000 行几十毫秒,一
# COMMIT 锁立刻全放,批与批之间别的请求能挤进来 ——
# 锁的数量和时长两维一起被摁下。分批要点:① 按主键
# id 区间切,别用 LIMIT OFFSET(越翻越慢);② 批与
# 批之间 sleep 几十到几百毫秒主动让路;③ 每批 500~
# 2000 行别太大太小;④ WHERE 必须走索引。import time, pymysql
# ★ 大批量更新:按主键 id 分批,每批一个小事务
def batch_update(conn, batch=1000, sleep_ms=100):
last_id = 0
while True:
with conn.cursor() as cur:
# ★ 每批一个独立事务:走主键索引,只锁这一段
cur.execute(
"UPDATE orders SET status='done' "
"WHERE id > %s AND status='shipped' "
"AND ship_time < '2022-01-01' "
"ORDER BY id LIMIT %s",
(last_id, batch))
affected = cur.rowcount
# ★ 取这批处理到的最大 id,作为下一批的起点
cur.execute("SELECT MAX(id) FROM orders "
"WHERE id > %s AND id <= %s",
(last_id, last_id + batch * 50))
conn.commit() # ★★ 每批立刻提交 —— 锁马上释放
if affected == 0:
break # 没有更多行了,结束
last_id += batch * 50
time.sleep(sleep_ms / 1000) # ★ 喘口气,给正常业务让路
print(f'已处理到 id={last_id}, 本批 {affected} 行')修复 5:大事务的其他伤害——不只是锁
# === ★ 锁只是最显眼的伤害,大事务还在别处放火 ===
# === ★ 伤害 1:undo log 暴涨,拖慢全库的快照读 ===
# ★ 事务每改一行,InnoDB 都会把这行的【旧值】记进
# undo log(为了能回滚、为了 MVCC 多版本读)。
# ★ ★ undo log 要保留到【没有任何事务还需要它】为止。
# 一个大事务长时间不提交,它产生的海量 undo 就一直
# 【清不掉】,堆在那里。
# ★ ★★ 后果:别的事务做"快照读"(普通 SELECT)时,
# 要顺着 undo 链回溯到自己该看的版本 —— undo 链
# 被一个长事务拖得老长,全库的读都跟着变慢。
# (极端情况 undo 表空间被撑爆。)
# === ★ 伤害 2:主从复制延迟飙升 ===
# ★ 一个事务,在 binlog 里是【一个整体】。从库回放
# 时,要等这个事务在主库【完全提交】,才能拿到完整
# binlog 事件,然后从库再【完整重放一遍】。
# ★ ★ 主库上跑 40 秒的大事务,从库就要在 40 秒后,
# 再花时间把它整个重放 —— 这期间从库【追不上】
# 主库,主从延迟肉眼可见地飙升。
# ★ ★★ 如果业务有"读写分离",从库延迟会让用户读到
# 严重过期的数据(刚下的单查不到)。
# === ★ 伤害 3:长时间占着一个数据库连接 ===
# ★ 事务跑多久,它就占着那条数据库连接多久。一个
# 40 秒的事务,就是一条连接被占用 40 秒。
# ★ ★ 几个大事务一起跑,连接池里的连接很快被占光,
# 正常请求连"拿一条连接"都拿不到 —— 又一波超时。
# === ★ 伤害 4:一旦失败,回滚比执行还慢还痛 ===
# ★ 大事务跑到一半失败(或你手贱 Ctrl-C),要 ROLLBACK。
# ★ ★ 回滚不是"撤销"那么轻巧 —— 它要把已经改的
# 几十万行,【按 undo log 一行行改回去】。回滚耗时
# 常常比正向执行【还长】,而且这期间锁还在持有。
# ★ 所以大事务是"开弓没有回头箭",出错的代价极高。
# === 认知 ===
# ★ 大事务的伤害不只是锁:① undo log 暴涨 —— 大事务
# 不提交,它产生的旧版本 undo 清不掉,别的事务快照
# 读要回溯长 undo 链,全库读变慢;② 主从延迟飙升 ——
# 一个事务在 binlog 里是整体,从库要等它主库提交后
# 再完整重放,读写分离下会读到过期数据;③ 长时间
# 占用数据库连接,连接池被占光引发新一波超时;
# ④ 中途失败回滚要按 undo 一行行改回去,常比正向
# 执行还慢、锁还不放 —— 大事务出错代价极高。修复 6:MySQL 大事务排查纪律
# === 这次事故暴露的认知盲区,定几条纪律 ===
# === 1. ★ 接口超时先分清:DB 慢 还是 在等锁 ===
$ grep -i 'Lock wait timeout' app.log # 有这词,基本是等锁
# === 2. ★ 看锁等待链:谁阻塞了谁 ===
mysql> SELECT * FROM sys.innodb_lock_waits\G
# === 3. ★ 揪长事务 / 大事务 ===
mysql> SELECT trx_id,trx_started,trx_rows_locked,trx_rows_modified,trx_query
FROM information_schema.innodb_trx ORDER BY trx_started;
# === 4. ★★ 一条改大量行的 UPDATE/DELETE,就是一个大事务 ===
# === 5. ★★ 锁加上后持有到 COMMIT 才放 —— 事务越长,别人等越久 ===
# === 6. ★ 范围 UPDATE 会加间隙锁,牵连"还不存在的新行" ===
# === 7. ★ 批量 UPDATE/DELETE 的 WHERE 必须走索引,否则锁扩散到全表 ===
# === 8. ★★ 大批量写必须分批:按主键切,每批 500~2000 行,逐批 COMMIT ===
# === 9. ★ 批与批之间 sleep 一下,主动给正常业务和主从复制让路 ===
# === 10. 排查"批量写打挂线上"的步骤链 ===
mysql> SHOW PROCESSLIST; # ① 看有没有跑很久的写 SQL
mysql> SELECT * FROM sys.innodb_lock_waits\G # ② 看锁等待链
mysql> SELECT * FROM information_schema.innodb_trx; # ③ 揪大事务
# ④ 必要时 KILL 那个大事务的连接 id(但小心回滚也慢)
# ⑤ 改造:大 UPDATE -> 按主键分批 + 逐批提交 + sleep命令速查
需求 命令 / 做法
=============================================================
看当前在跑的 SQL SHOW PROCESSLIST; / SHOW FULL PROCESSLIST;
看锁等待链(谁阻塞谁) SELECT * FROM sys.innodb_lock_waits\G
看当前所有事务 SELECT * FROM information_schema.innodb_trx
揪大事务 看 trx_rows_locked / trx_started 是否异常
看锁详情(8.0) SELECT * FROM performance_schema.data_locks
杀掉作恶的事务连接 KILL <连接id>(注意回滚也可能慢)
批量更新 按主键 id 分批,每批 500~2000 行,逐批 COMMIT
分批的取数 WHERE id > last_id ORDER BY id LIMIT N
判断是否等锁 应用日志搜 "Lock wait timeout exceeded"
看事务隔离级别 SELECT @@transaction_isolation
口诀:一条改海量行的 UPDATE 就是一个大事务 锁持有到提交才放
范围更新会加间隙锁 批量写务必按主键分批 逐批提交
避坑清单
- 接口超时先分清是 DB 慢还是在等锁,日志里出现 Lock wait timeout exceeded 基本就是等锁
- autocommit 下每条单独的 UPDATE/INSERT/DELETE 都是一个独立事务,改海量行就是一个大事务
- InnoDB 两阶段锁,事务执行中加的锁一直持有到 COMMIT 才释放,不是改完一行就放一行
- 大事务大在两个维度,改的行多导致锁数量多,持续时间长导致锁时长久,任一超标都危险
- InnoDB 有间隙锁,范围 UPDATE 会锁住行与行之间的空隙,新行 INSERT 进这个范围会被挡住
- 批量 UPDATE/DELETE 的 WHERE 条件必须走索引,否则全表扫描会把锁扩散到整张表
- 大批量写必须分批,按主键 id 区间切,每批 500 到 2000 行,每批结束立刻 COMMIT 释放锁
- 分批别用 LIMIT OFFSET 翻页,OFFSET 越大越慢,要记住上批最大 id 从它之后接着取
- 批与批之间 sleep 几十到几百毫秒,主动给正常业务请求和主从复制留出执行窗口
- 大事务还会让 undo log 暴涨拖慢全库快照读、主从延迟飙升、占满连接池,中途回滚比执行还慢
总结
这次"后台改 40 万条老数据,把前台正在下单的人也一起搞挂"的事故,纠正了我一个关于"隔离"的、藏得极深的错觉。在我过去的脑子里,数据库里的数据,是一格一格、彼此独立的——我改我的"历史订单",你下你的"新订单",我们碰的是【不同的行】,就像两个人在图书馆里各自看各自的书,井水不犯河水。"我只动 A,绝不会影响 B",这个想法在我心里天经地义,天经地义到我从来没把它当成一个【需要成立条件】的判断,我把它当成了一条物理定律。所以当我那条只碰"历史订单"的 UPDATE,把毫不相干的"新订单"也挂死时,我的第一反应是"这不可能",是"一定是哪里巧合了"。我宁可相信是别的什么东西恰好同时出了故障,也没想过:是我自己,亲手把这两批数据连了起来。直到我把 InnoDB 的锁一层层扒开,我才看清那根我从没看见过的线:我和那个下单的新用户,我们碰的确实是不同的"行",可我们用的是【同一张表】、同一套锁机制、同一个共享的资源池。我那条 UPDATE 在事务里攥着的 40 万把锁,加上它范围扫描时顺手锁住的、连"还不存在的行"都包进去的间隙——它不是在"动几行数据",它是在那张表上,圈下了一大片【主权领地】,长达 40 秒,谁都不许进。新用户的订单想 INSERT 进来,id 恰好落在我圈的地里——它撞上的不是我的数据,是我的【锁】。数据是隔离的,锁不是。资源是共享的。复盘到最深,我意识到这件事真正教给我的,是"隔离"从来不是免费的、默认成立的,它是有【边界】的、有【代价】的——而我一直待在那个边界以内,以为边界以内的安宁就是世界的全部。两个操作"不冲突",不是因为它们碰的数据不同就自动成立;它要成立,得满足一个我从来没意识到的前提:它们之间,不能共享任何一个会被独占、会被排队的资源——不共享锁、不共享连接、不共享那条复制管道、不共享同一个 undo 链。一旦它们之间还连着哪怕一根这样的线,"隔离"就只是一个表象,底下,它们其实是一根绳上的蚂蚱。这个教训,我后来到处都看见它的影子:两个"无关"的微服务,因为共用一个数据库连接池,一个慢查询拖垮了另一个;两个"无关"的接口,因为跑在同一个线程池里,一个接口的线程被打满,另一个接口也没线程可用了;两个"无关"的定时任务,因为抢同一把分布式锁,一个卡住,另一个再也拿不到锁。它们的表象都是"我们做的是不同的事、碰的是不同的东西",而真相都一样:在那个看不见的【共享资源】层面,它们从一开始就是【同一拨人】。这次最大的收获,是我给自己立了一条新规矩:每当我要判断"我这个操作会不会影响到别人"的时候,我不再只问"我碰的数据/对象,和别人是不是同一个";我会逼自己往下再挖一层,问一句——在我和"别人"之间,有没有共享任何一个【会被独占、会排队、有总量上限】的资源?锁、连接、线程、带宽、配额……只要有一样是共享的,那"我们互不影响"这句话,就得打上一个大大的问号。InnoDB 那 40 万把攥在手里整整 40 秒不放的锁教给我的,不是一个数据库调优技巧,而是一个更朴素也更要命的道理:你以为你和别人隔着一堵墙,可只要你们脚下踩着同一块地板,你跺的那一脚,他就一定感觉得到。真正的隔离,不在于你们碰的东西不同,而在于——你们脚下,有没有同一块地板。
—— 别看了 · 2026声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理 邮箱1846861578@qq.com。
