如何通过事务与控制批量大小解决MySQL大批量更新导致的锁等待问题？

本文共计989个文字，预计阅读时间需要4分钟。

MySQL的行锁在执行UPDATE时并非按需加锁，而是先定位再加锁——即使用WHERE id IN (1,2,3,...)这样的条件，只扫描范围大、执行时间长，就会持续持有锁，阻塞其他事务对这些行的读写。更糟糕的是，如果找不到索引，可能升级为间隙锁或表锁，直接影响性能。

常见现象：SHOW PROCESSLIST 里一堆 Locked 或 Waiting for table metadata lock；业务侧出现超时、重试雪崩；INFORMATION_SCHEMA.INNODB_TRX 显示事务长时间未提交。

• 别指望加个索引就万事大吉——即使 WHERE 条件走索引，更新本身仍要加 X 锁，锁住所有命中的聚簇索引记录和二级索引项
• 事务越长，锁持有时间越长；批量越大，锁覆盖行数越多；两者叠加就是锁等待温床
• AUTOCOMMIT=1 下每个 UPDATE 是独立事务，看似安全，但高并发下仍可能因锁竞争排队

怎么拆分 UPDATE 才不丢数据又不拖慢

核心是控制单次事务的「行数」和「执行时间」，而不是简单按固定条数切分。1000 行在 SSD 上可能 50ms，但在机械盘或高负载下可能 300ms+，反而更易触发锁等待。

实操建议：

• 优先按主键范围切分：比如 WHERE id BETWEEN 10000 AND 10999，避免 OFFSET 分页导致的重复扫描
• 单批次控制在 100–500 行之间（不是硬指标），用 SELECT COUNT(*) 预估后再定；若更新涉及多表关联，行数要更保守
• 每批后加 SLEEP(0.01)（毫秒级）缓解 CPU 和锁争抢，比盲目提速更有效
• 务必在事务外先 SELECT id 获取待更新主键列表，再按批构造 WHERE id IN (...)，防止条件漂移

innodb_lock_wait_timeout 调小有用吗

没用，还可能让问题更隐蔽。这个参数只控制“等锁最多忍多久”，设成 1 秒，不代表锁就释放了——它只是让当前语句报错 Lock wait timeout exceeded，而原持有锁的事务还在跑，其他连接照样堵着。

真正该看的配置：

• innodb_deadlock_detect=ON（默认），确保死锁能被快速发现并回滚一方
• innodb_flush_log_at_trx_commit=1 别关，否则崩溃后数据不一致风险远大于锁等待
• 不要调大 innodb_lock_wait_timeout 来“掩盖”问题——那只是把报错延迟到用户感知更差的时候

用 INSERT ... ON DUPLICATE KEY UPDATE 替代 UPDATE 行不行

可以，但仅限于有唯一键冲突场景，且要注意语义差异：它本质是“尝试插入，冲突则更新”，会触发唯一索引的 S 锁 → X 锁转换，锁行为和原生 UPDATE 不同，有时反而更轻量。

不过得小心：

• 必须有 UNIQUE 或 PRIMARY KEY 支撑，否则语法报错 Duplicate entry '' for key 'PRIMARY'
• 如果更新字段里含函数（如 updated_at = NOW()），要写进 ON DUPLICATE KEY UPDATE 子句，不能只靠 VALUES()
• 批量执行时，INSERT ... VALUES (),(),... 的单条语句长度受 max_allowed_packet 限制，超长会报错 Packets larger than max_allowed_packet are not allowed

锁粒度和性能要看实际执行计划，别假设它一定比 UPDATE 好——在无主键/唯一键条件下，它甚至可能触发全表扫描。