分批提交的核心是用主键游标安全切分数据，避免漏数或重复：优先用 WHERE id > last_id ORDER BY id LIMIT N，禁用 OFFSET 分页；每批处理后立即 COMMIT 释放锁，批次大小建议 1000–5000 行。
为什么不能直接用大事务批量更新
长事务会持续持有行锁或表锁，阻塞其他读写操作，尤其在 UPDATE 或 DELETE 大量数据时，容易触发锁等待超时（Lock wait timeout exceeded），甚至拖垮整个库的并发能力。MySQL 默认的 autocommit=1 对单条语句有效，但显式开启事务后，所有操作都在同一事务内，直到 COMMIT 才释放锁。
分批提交的核心控制点：WHERE + LIMIT + 循环
关键不是“怎么提交”，而是“怎么安全切分数据”。必须靠主键或唯一有序字段做游标分页，避免漏数或重复处理：
- 优先用自增主键
id，每次取WHERE id > last_id ORDER BY id LIMIT N - 禁止用
OFFSET分页（LIMIT 10000, 100），越往后扫描越慢，且可能跳过新插入的行 - 每批处理后立刻
COMMIT，确保锁及时释放；若出错需手动回滚当前批次，不影响已提交部分 - 批次大小建议 1000–5000 行，太大仍可能锁太久，太小则网络/事务开销高
MySQL 存储过程示例（带防重与中断恢复）
以下是一个可中断、可续跑的分批更新模板，适用于需要修改状态字段的场景（如 status = 2）：
DELIMITER $$ CREATE PROCEDURE batch_update_status() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE last_id BIGINT DEFAULT 0; DECLARE batch_size INT DEFAULT 2000; <p>WHILE NOT done DO UPDATE orders SET status = 2 WHERE id > last_id AND status = 1 ORDER BY id LIMIT batch_size;</p><pre class='brush:php;toolbar:false;'>-- 检查是否还有满足条件的行 SELECT COALESCE(MAX(id), 0) INTO last_id FROM orders WHERE id > last_id AND status = 1 LIMIT 1; IF last_id = 0 THEN SET done = TRUE; END IF; COMMIT; -- 每批都提交，不等循环结束
END WHILE; END$$ DELIMITER ;
注意：ORDER BY id LIMIT 在 UPDATE 中是 MySQL 特有语法，标准 SQL 不支持；PostgreSQL 需改用 CTE 或子查询；SQL Server 要用 TOP + OUTPUT。
应用层分批更可控，但要注意连接与事务生命周期
比起存储过程，业务代码中控制分批通常更灵活，也更容易监控和失败重试：
- 每次查询只取一批 ID 列表（
SELECT id FROM table WHERE ... ORDER BY id LIMIT N），再拼成IN批量更新，避免全表扫描 - 务必在每次
UPDATE后调用connection.commit()，不要依赖框架自动提交（比如 Django 的atomic会包住整个循环） - 如果使用连接池，确认连接未被复用导致事务状态残留；建议每批新建独立事务上下文
- 日志里记下
last_id和处理时间，中断后可人工定位断点
真正难的不是写循环，而是判断哪条记录该进哪一批——只要游标条件和 WHERE 过滤逻辑不一致，就会漏数据或重复执行。上线前必须用小数据集验证边界情况。
