SQL大规模修改数据导致锁表_分批次执行UPDATE任务方案-SQL-PHP中文网

MySQL UPDATE未走索引或扫描过多时会升级为表级锁，导致其他操作阻塞；安全分批需基于有索引的单调字段（如id）分段，每批单独事务并及时提交，推荐1000–5000行。

sql大规模修改数据导致锁表_分批次执行update任务方案

UPDATE 为什么一跑就锁全表？ MySQL 的 `UPDATE` 在未命中索引或扫描行数过多时，会升级为表级锁（尤其在 `READ-COMMITTED` 以下隔离级别），InnoDB 虽以行锁为主，但若 WHERE 条件没走索引、或扫描范围太大，就会锁住所有扫描过的记录——甚至触发间隙锁，把相邻空隙也锁死。常见现象是：执行一条慢 `UPDATE` 后，其他 SELECT/INSERT/UPDATE 都卡住，`SHOW PROCESSLIST` 看到一堆 `Waiting for table metadata lock` 或 `Locked` 状态。

锁的本质不是“语句长”，而是“扫描多、索引失效、事务长”

即使加了 `WHERE id > 1000 AND id ，如果 id 没建索引，照样全表扫+全表锁`

`UPDATE ... LIMIT N` 不解决锁问题：它只限制修改行数，但 WHERE 仍可能扫全表

怎么分批 UPDATE 才真正释放锁？核心是让每次事务短、扫描范围小、且能稳定定位下一批。不能靠 `LIMIT` 直接切，得用主键/有序索引字段做游标。

必须基于有索引的单调字段（如 `id`、`created_at`）分段，避免重复或漏改

每批用 `WHERE id > ? AND id ，而非 LIMIT，确保可重复执行`

每批单独事务，执行完立刻 `COMMIT`，锁立即释放

批大小建议 1000–5000 行：太小（如 100）网络和事务开销高；太大（如 5w）单次锁时间仍长

示例（安全分批）：

id > 15000 AND id ，依此类推。
WHERE 条件写错导致数据错乱的典型坑
分批最怕逻辑错位：同一条记录被改两次，或某条永远被跳过。
别用 OFFSET 分页式分批（如 LIMIT 1000 OFFSET 1000）：并发执行时数据变动会导致偏移错乱别在 WHERE 中混用非单调字段（如 WHERE status = 'pending' AND id > ?）：若中间有新插入 pending 记录，会重复处理如果必须按状态筛选，先用 SELECT id FROM ... WHERE status = 'pending' ORDER BY id LIMIT 5000 拿出 ID 列表，再用 WHERE id IN (…) 更新——但注意 IN 列表长度限制（MySQL 默认 65535 参数）线上执行前必须验证的三件事
分批脚本不是写完就能跑，尤其在生产库。
先用 EXPLAIN 确认每批的 WHERE 是否走了索引，type 至少是 range，rows 接近你设的批大小在从库或备份库上跑一遍完整批次流程，检查最终结果是否符合预期（比如总更新数是否等于原条件匹配数）加个简单限流：每批后 SLEEP(0.1)（应用层控制），避免瞬间打满 IO 或连接数；别依赖数据库自身“慢”来缓冲分批本身不难，难的是边界判断是否稳、索引是否真生效、以及执行中没人动源数据——这些地方一松，要么数据不一致，要么锁又堆起来了。