MySQL如何避免大批量插入导致锁超时_分批提交事务的实践建议

根本原因是单事务持有锁时间过长导致等待超时；需显式BEGIN/COMMIT分批，每批1000–5000行，避免隐式提交、锁冲突及连接状态残留。

为什么大批量 INSERT 会触发 Lock wait timeout exceeded

问题根源往往不在于数据量本身，而在于单个事务持有行锁或表锁的时间太长了，导致其他排队等待的事务最终超时。MySQL默认的innodb_lock_wait_timeout是50秒，这意味着，一旦某条INSERT事务因为某些原因卡住——比如遇到了索引冲突、磁盘I/O缓慢，甚至是主从延迟引发的元数据锁（MDL）——那么后续所有需要访问同一资源的SQL语句，都会在苦苦等待50秒后，抛出这个令人头疼的错误。

分批插入必须控制事务粒度，不是简单切分数组

这里有个常见的误区：很多人以为把十万条数据切成一千条一组循环执行，就万事大吉了。但如果没有显式地控制事务边界，结果往往是每个INSERT语句都自动开启了一个独立的小事务。这非但没有解决问题，反而放大了日志写入和锁竞争的开销，得不偿失。

• 务必使用BEGIN和COMMIT显式包裹每一批操作。标准的做法是：先执行BEGIN，然后批量执行一条包含多行值的INSERT INTO ... VALUES (...),(...),...语句，最后再COMMIT。
• 每批的行数建议控制在1000到5000条之间。具体数值取决于单行数据的平均长度和数据库的innodb_log_file_size配置。如果单批超过一万行，很容易因为日志文件空间不足而触发刷盘阻塞，让性能瞬间倒退。
• 避免在循环内频繁地COMMIT后立刻BEGIN。在每批提交之后，可以人为地加入一个极短的休眠，比如PHP中的usleep(1000)或Go中的time.Sleep(time.Millisecond)。这个小小的间隙，能有效缓解CPU的过度抢占和锁队列的堆积问题。

INSERT ... ON DUPLICATE KEY UPDATE 分批时要小心唯一键判断逻辑

如果使用“存在则更新，不存在则插入”的语法，分批提交并不会改变其语义，但有一个关键点必须警惕：唯一键冲突的检测发生在语句执行期间，而非事务提交之时。换句话说，如果同一批数据内部有两条记录试图插入相同的唯一键值（比如同一个邮箱地址），数据库会立即报错，而不会等到最终提交时才失败。

• 确保每批数据内部没有重复的唯一键值。可以在应用层预先处理，例如使用PHP的array_unique($rows, SORT_REGULAR)或Go的map结构进行去重。
• 不要依赖数据库去重来“兜底”。否则分批就失去了意义，而且错误发生的位置会变得难以定位，增加调试成本。
• 如果业务逻辑允许直接覆盖旧记录，优先考虑使用REPLACE INTO。它的本质是先删除再插入，在主键或唯一键冲突时的行为更加可预测，虽然性能开销需要评估。

别忽略 autocommit 和连接状态的隐式影响

很多ORM框架或连接池默认开启了autocommit=1。在这种设置下，即使你手动写了BEGIN，下一条非DML语句（比如一个SELECT查询或SET命令）也可能意外触发自动提交，导致你精心设计的事务提前结束，功亏一篑。

• 执行分批操作前，先确认连接状态。执行一句SELECT @@autocommit;看看，必要时显式将其设为0：SET autocommit = 0;。
• 避免在事务中混用SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE这类加锁查询。它们会显著延长锁的持有时间，很可能抵消掉分批操作带来的性能收益。
• 使用连接池时要格外小心。某些连接池（例如MySQLi的持久化连接）可能会复用上一次未清理的事务状态。一个更稳妥的做法是，在每次开始新的一批操作前，先执行一次ROLLBACK来确保连接状态是干净的。

说到底，分批操作本身的技术并不复杂，真正的难点在于确保每一批的边界是否干净、锁是否真正被释放、连接状态是否完全可控。这些细节一旦疏忽，很容易又绕回到锁超时的老路上。