MySQL 没锁升级？一条 UPDATE 怎么把 5 万行全锁了

以下分析基于 MySQL 8.0.32。5.7 的 data_locks 表结构不同，见文中标注。
场景：客服批量改订单状态，UPDATE 跑了 30 秒后整个表写不进去了
路径：SHOW PROCESSLIST → INNODB STATUS 锁堆 → data_locks 拆锁 → 索引修复

上篇讲了两个并发事务交叉更新导致的死锁——InnoDB 选了其中一个事务杀了。这次我们从"死"到"堵"，看一种更频繁的 MySQL 困境：一条 UPDATE 把整张表锁了。

UPDATE orders SET status = 1 WHERE status = 0;

——锁了 53617 行，每行一个 X 锁（排他锁，其他事务不能读也不能写该行）。
不是 MySQL 做了"锁升级"——是 InnoDB 对扫描到的每行都加了锁，53617 个行锁堆在那里。
第 2 个线程来 update 另一行，直接堵死。

【现象】整个表的写入都停了

第一个信号：客服工单暴涨

某天运维突然收到告警：订单服务超时率飙升到 60%。查看应用日志，所有 UPDATE 和 INSERT 到 orders 表的操作都卡在 30 秒以上。

-- 最简单的状态更新，卡了 35 秒
UPDATE orders SET status = 1 WHERE status = 0;   -- Time: 35.2s
INSERT INTO orders ...                            -- Time: 28.7s
UPDATE orders SET notify_flag = 1 WHERE id = ?;   -- Time: 32.1s

核心特征：读不受影响（SELECT 正常），写全部阻塞。这是锁问题不是资源瓶颈。

SHOW PROCESSLIST: 全部 Updating

SHOW PROCESSLIST：全是 “Updating”

连接上去看第一个东西：

mysql> SHOW FULL PROCESSLIST;
+-----+------+-----------+-------+---------+------+----------+------------------+
| Id  | User | Host      | db    | Time    | State | Info                    |
+-----+------+-----------+-------+---------+------+----------+------------------+
| 127 | app  | 10.0.0.1  | shop  | 247     | Updating | UPDATE orders SET status = 1 WHERE status = 0 |
| 129 | app  | 10.0.0.2  | shop  | 203     | Updating | UPDATE orders SET status = 5 WHERE status = 3 |
| 131 | app  | 10.0.0.1  | shop  | 198     | Updating | UPDATE orders SET notify_flag = 1 WHERE id = 89721 |
| 133 | app  | 10.0.0.3  | shop  | 167     | Updating | INSERT INTO orders ...                          |
| 135 | app  | 10.0.0.1  | shop  | 1       | Updating | UPDATE orders SET status = 1 WHERE status = 0  |
| 136 | app  | 10.0.0.2  | shop  | 0       | ---      | NULL                                            |
+-----+------+-----------+-------+---------+------+----------+------------------+

4 个线程全部卡在 “Updating”，Time 列持续增长。最老的那个已经等了 247 秒还没提交。

如果你在生产看到这种画面：Time 持续增长的 “Updating” 队列，说明有 DML 持有了大量锁不释放，后续写入全部排队。

KILL 了，然后呢？

运维第一反应：KILL 最老的那个线程。

mysql> KILL 127;
Query OK, 0 rows affected

线程 127 被杀了，Time 列清零——但新的 UPDATE 进来又开始排队。因为根因没解决：第 2 个线程 129 仍然拿着它自己的锁。更关键的是，导致锁海啸的 SQL 模式还在跑。

KILL 治标不治本。 追根因，必须看 InnoDB 到底在锁什么。

INNODB STATUS: 53617 行锁

【解析】不是锁升级，是锁了每一行

WHERE 没有索引 = 全表逐行加锁

这是整篇文章最核心的一句话：

InnoDB 的 UPDATE/DELETE 在 WHERE 条件字段无索引时，不会跳过扫描的行——它会锁住扫描过程中经过的每一行。

对应到本次事故：

UPDATE orders SET status = 1 WHERE status = 0;
  • status 字段没有索引
  • InnoDB 只能走聚集索引全表扫描（type=ALL）
  • 从第 1 行扫到最后 1 行，逐行加 X 锁（排他锁，其他事务不能读也不能写）
  • 一共扫描了 53617 行，加锁 53617 行
mysql> EXPLAIN UPDATE orders SET status = 1 WHERE status = 0;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | UPDATE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 60248 |    100.0 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

type=ALL（全表扫描）、key=NULL（无索引可用）、rows=60248（估计扫描行数）、Extra=Using where（Using where — 存储引擎层返回记录后再用 WHERE 过滤）。这三个加在一起就是一个信号：这条 UPDATE 会碰每一行，锁每一行。

用 FORMAT=JSON 看 cost 更直观：

mysql> EXPLAIN FORMAT=JSON UPDATE orders SET status = 1 WHERE status = 0\G
{
  "query_block": {
    "cost_info": { "query_cost": "12049.65" },
    "table": {
      "table_name": "orders",
      "access_type": "ALL",
      "rows_examined_per_join": 60248,
      "cost_info": {
        "read_cost": "12049.65",
        "eval_cost": "0.00",
        "prefix_cost": "12049.65"
      }
    }
  }
}

这里的 C2 分析：优化器没有选错。 它走了唯一的可行路径——全表扫描，因为 status 字段根本没有索引可供选择。cost 12049.65 全部花在读取上，eval_cost=0.00 说明没有额外的评估开销。这不是优化器误判，是 schema 设计缺失导致优化器别无选择。

先看数据分布确认范围大小：

mysql> SELECT status, COUNT(*) AS cnt,
    COUNT(*) / SUM(COUNT(*)) OVER() AS ratio
FROM orders GROUP BY status;
+--------+-------+--------+
| status | cnt   | ratio  |
+--------+-------+--------+
| 0      | 53617 | 0.89   |
| 1      | 3445  | 0.057  |
| 2      | 1893  | 0.031  |
| 3      | 1293  | 0.021  |
+--------+-------+--------+

status=0 占了 89% 的行。这意味着即使 status 有索引，等值匹配也过滤不掉多少行——优化器可能会直接走全表扫描。但正因为没有索引，InnoDB 不得不全表扫描并逐行加锁，锁的数量直接等于全表行数。

SHOW ENGINE INNODB STATUS：53617 行锁

mysql> SHOW ENGINE INNODB STATUS\G
---
TRANSACTIONS
---
Trx id counter 5278693
Purge done for trx's n:o < 5278685 undo n:o < 0 state: running
History list length 847

---TRANSACTION 5278681, ACTIVE 247 sec
2 lock struct(s), heap size 1192, **5 rows lock**(s)   -- 注意这里：线程 129 只锁了 5 行
MySQL thread id 129, OS thread handle 14056, query id 87213

---TRANSACTION 5278679, ACTIVE 251 sec
1845 lock struct(s), heap size 196608, **53617 row lock**(s)  -- 线程 127：53617 行锁！

lock struct（锁结构体）是 InnoDB 管理行锁的内存单元，每个 struct 可包含多个行锁位图。1845 个 struct 管理 53617 行锁，平均每个 struct 约 29 行——说明锁已经分散到大量内存结构中。
MySQL thread id 127, OS thread handle 13982, query id 87195

关键信息在事务段的最后一行：

  • 线程 127：1845 个 lock structs，53617 个行锁
  • 线程 129（另一个 UPDATE）：只有 2 个 lock structs，5 行锁——它在等 127 释放

这里解释了"不是锁升级"：InnoDB 并没有把行锁自动提升为表锁（MySQL 没有这个机制）。它是忠实地对 53617 行每行都申请了 X 锁。只是因为锁的数量太多，效果等价于表锁——其他事务的任何写操作都要排队。

无索引 vs 有索引 UPDATE 锁范围对比

为什么不是锁升级？

SQL Server 有 lock escalation：当单个事务的锁数量超过阈值（默认 5000），引擎自动把行锁合并成表锁，释放行锁内存。

MySQL InnoDB 没有这个机制。它的锁是 B+ 树索引上的 record lock + gap lock，以 lock struct 为单位管理，每个 struct 可以包含多个行锁。即使锁了 5 万行，也不会自动升级为表锁——但也因此不会自动降级，锁只会越积越多直到提交或回滚。

所以这条 UPDATE 堵住整张表的真正原因：

无索引 WHERE → 全表扫描 → 逐行加 X 锁 → 53617 行锁堆积 → 其他写操作全部等待
         ↑                          ↑
    原因在这                   效果等价锁升级

事务没提交，锁不释放

再看事发时的应用代码：

@Transactional
public void batchUpdateStatus(Integer fromStatus, Integer toStatus) {
    // UPDATE orders SET status = ? WHERE status = ?
    orderDao.updateStatusByStatus(fromStatus, toStatus);
    
    // 这里是问题
    sendNotifyToExternalSystem();  // HTTP 调用，耗时 5-10 秒
    
    // 事务在这里才提交
}

sendNotifyToExternalSystem() 是一个 HTTP 外部调用。这个调用在事务内部执行，而且可能超时重试。调用的 5-10 秒里，事务没有提交，53617 个行锁全部持有。

警告：不要在事务里放 RPC/HTTP 调用。 事务所持的锁在 COMMIT/ROLLBACK 之前全部不会释放。

【路径】🔍 下次怎么发现

第一步：SHOW PROCESSLIST 的 “Updating” 队列

看到 3 个以上线程同时处于 “Updating” 且 Time 持续增长，不要先救火——先抓现场。KILL 了之后锁信息就丢了。

-- 抓现场前别 KILL
SHOW ENGINE INNODB STATUS\G

第二步：INNODB STATUS 的 TRANSACTIONS 节

在输出中搜 row lock(s)：

mysql> SHOW ENGINE INNODB STATUS\G | grep -E "row lock|lock struct"
1845 lock struct(s), heap size 196608, 53617 row lock(s)
2 lock struct(s), heap size 1192, 5 rows lock(s)

行锁数远大于合理值（比如一个 UPDATE 预期锁几行，结果锁了几万行），说明 WHERE 字段大概率没有索引。

第三步：performance_schema.data_locks（8.0+）

mysql> SELECT 
    ENGINE_TRANSACTION_ID,
    OBJECT_NAME,
    INDEX_NAME,
    LOCK_TYPE,
    LOCK_MODE,
    LOCK_STATUS,
    LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_NAME = 'orders'\G

输出节选：

ENGINE_TRANSACTION_ID: 5278679
OBJECT_NAME: orders
INDEX_NAME: NULL          -- NULL = 聚集索引（全表扫描）
LOCK_TYPE: TABLE          -- 意向锁
LOCK_MODE: IX
LOCK_STATUS: GRANTED

ENGINE_TRANSACTION_ID: 5278679
OBJECT_NAME: orders
INDEX_NAME: GEN_CLUST_INDEX  -- 聚集索引
LOCK_TYPE: RECORD         -- 行锁
LOCK_MODE: X              -- 排他锁
LOCK_STATUS: GRANTED
LOCK_DATA: 0x0001
---
-- ... 重复 53617 行

LOCK_TYPE=RECORD、LOCK_MODE=X、INDEX_NAME=GEN_CLUST_INDEX（InnoDB 自动生成的聚簇索引名）——这三条一起说明：InnoDB 正在对聚集索引的每行记录加排他锁。

5.7 的差异： 5.7 没有 performance_schema.data_locks，改用 information_schema.INNODB_LOCKS：

mysql> SELECT * FROM information_schema.INNODB_LOCKS\G

但 5.7 的表没有 LOCK_DATA 字段，排查精度不如 8.0。

performance_schema.data_locks 输出

【重构】加一个索引，从 5 万降到 5

方案 A：WHERE 字段加索引

mysql> CREATE INDEX idx_orders_status ON orders(status);
Query OK, 0 rows affected (5.2 sec)

注意：MySQL 8.0 支持在线 DDL（ALGORITHM=INPLACE, LOCK=NONE），可以在线加索引不阻塞写入。5.7 建议低峰期操作或用 pt-online-schema-change。

加上索引后再跑同一条 UPDATE：

mysql> EXPLAIN UPDATE orders SET status = 1 WHERE status = 0;
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table  | type  | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | orders | range | idx_orders_status| idx_orders_status | 2    | const | 5    |    100.0 | Using where |
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+----------+-------------+

type=range（索引范围扫描）、key=idx_orders_status（用了新索引）、rows=5（只扫 5 行）。

锁数量对比：

指标 加索引前 加索引后
扫描行数 60248 5
lock structs 1845 2
行锁数 53617 5
执行耗时 247 秒 0.003 秒
其他线程等待 全部排队 完全不影响

53617 ÷ 5 = 10723 倍。 一个索引差了一万倍——不是因为索引有什么魔法，是因为它让 InnoDB 知道了"只锁这 5 行就够了"。

方案 B：拆分事务 + 分批提交

即使有索引，批量 UPDATE 10 万行仍然会锁很多行。安全做法：

@Transactional
public void batchUpdateStatus(Integer fromStatus, Integer toStatus) {
    // ❌ 全部 5 万行在一个事务里
    orderDao.updateStatusByStatus(fromStatus, toStatus);
    // 锁 5 万行直到事务结束
}

改成：

public void batchUpdateStatus(Integer fromStatus, Integer toStatus) {
    int batchSize = 500;
    int updated;
    do {
        updated = orderDao.updateStatusWithLimit(fromStatus, toStatus, batchSize);
        // 每批 500 行立即提交，锁持有时间 < 50ms
    } while (updated > 0);
}

-- 分批 UPDATE：每次只更新 500 行
UPDATE orders SET status = 1 WHERE status = 0 LIMIT 500;

每次只锁 500 行，提交后再锁下一批。其他线程的写入最多等 500 行锁的持有时间（<50ms），而不是 53617 行（247 秒）。

分批提交 + 事务拆分代码

方案 C：检查代码里的事务边界

@Transactional
public void processOrderStatusUpdate(...) {
    // 事务内：只放数据库操作
    orderDao.updateStatus(...);
    orderDao.insertLog(...);
    
    // ❌ 事务内不要放这些：
    sendHttpNotify();            // RPC 调用
    Thread.sleep(1000);          // 等待
    redisTemplate.opsForValue(); // Redis 操作（可能超时）
}

规则： @Transactional 内的代码越少越好。外部调用、等待、磁盘 IO 都不应该在事务里。

【标记】🗝 在你的项目里搜出这种隐患

查 performance_schema：哪些 DML 锁了最多行

SELECT
    digest_text,
    COUNT_STAR,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_AFFECTED,
    SUM_CREATED_TMP_DISK_TABLES
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text LIKE '%UPDATE%'
   OR digest_text LIKE '%DELETE%'
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 10;

看 SUM_ROWS_EXAMINED（扫描行数）和 SUM_ROWS_AFFECTED（实际影响行数）的比例。如果前者远大于后者，说明 WHERE 条件利用率低，大概率缺索引。

grep 代码库：查 UPDATE/DELETE 的 WHERE 字段

# 查所有 UPDATE/DELETE 的 WHERE 条件字段
grep -rn "UPDATE\|DELETE" --include="*.java" --include="*.xml" \
  | grep -i "where" | grep -v "id\b" | grep -v "索引\|index"

重点关注那些 WHERE 条件不是主键或唯一索引的 DML。如果在生产上线前就发现 WHERE status = ? 没有索引，你就不用等到线上去追 INNODB STATUS 了。

预防：写入 SQL Review 检查清单

把这条规则加进团队的 SQL Review：

所有 UPDATE/DELETE 的 WHERE 条件字段必须建索引。 在 code review 中看到 UPDATE table SET col = val WHERE non_indexed_field = ? 一律打回。

这不是性能优化——是锁安全规范。无索引的 UPDATE/DELETE 可能让整张表瘫痪。

grep 排查命令

MySQL 没有锁升级——是 InnoDB 对每条记录都严格执行了行锁协议，一条 UPDATE 锁了 5 万行，效果和表锁一样。加个索引，锁从 5 万降到 5。

下篇我们聊间隙锁——一个 INSERT 怎么被另一个事务的 SELECT 堵住的。

📺 公众号「Ai拆代码的曹操」
🌟 知识星球「Ai拆代码的曹操」