MySQL存储过程需先设SET autocommit=0，再用START TRANSACTION和分批COMMIT控制事务；每批提交（如500行）可平衡锁持有时间与崩溃风险，避免history list暴涨、锁表及性能崩盘。
MySQL 存储过程中不能直接 COMMIT，除非显式声明为 AUTOCOMMIT=0 且在支持事务的存储引擎（如 InnoDB）中运行；盲目设大事务或忽略提交节奏，极易锁表、拖慢 purge、甚至让 history list length 爆涨卡住查询。
存储过程里怎么安全地 COMMIT
MySQL 存储过程默认运行在 autocommit=1 模式下，每条 DML 都是独立事务 —— 这意味着你写个循环 INSERT 10 万次，就等于执行了 10 万次 fsync，性能崩盘，还容易被 wait_timeout 中断。
正确做法是：先关自动提交，再手动控制事务边界。但要注意两点限制：
- 存储过程内不能在函数或触发器中调用
COMMIT或ROLLBACK（会报错ERROR 1305 (42000): SAVEPOINT does not exist类错误） - 必须在存储过程开头显式执行
SET autocommit = 0;，否则START TRANSACTION和COMMIT无效 - 每批处理后必须
COMMIT，不能依赖连接关闭自动回滚 —— 长连接可能复用，残留未提交事务会持续持有锁
示例片段：
DELIMITER $$
CREATE PROCEDURE batch_insert()
BEGIN
DECLARE i INT DEFAULT 1;
SET autocommit = 0; -- 关键：否则下面的 COMMIT 不生效
START TRANSACTION;
WHILE i <= 10000 DO
INSERT INTO log_table (msg) VALUES (CONCAT('log-', i));
IF i % 500 = 0 THEN
COMMIT; -- 每 500 条提交一次
START TRANSACTION; -- 重新开启新事务
END IF;
SET i = i + 1;
END WHILE;
COMMIT; -- 处理完最后一批
END$$
DELIMITER ;为什么每 500 行 commit 一次不是拍脑袋定的
批量大小本质是在“锁持有时间”和“崩溃丢失风险”之间找平衡点。太小（如 10 行）→ 提交太频繁，innodb_flush_log_at_trx_commit 的刷盘开销压不下来；太大（如 5 万行）→ 事务运行超 60 秒，TRX_STARTED 时间过长，undo 日志无法清理，history_list_length 持续上涨，查 SELECT * FROM t 都变慢。
监控是否踩坑，直接查：
SELECT TRX_ID, TRX_STARTED, TRX_STATE, TRX_QUERY FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(), TRX_STARTED)) > 60;
如果返回结果多，说明你的存储过程里 COMMIT 频率太低，或者中间混入了耗时操作（比如 SLEEP()、外部 HTTP 调用）。
innodb_flush_log_at_trx_commit=2 是刚需，不是可选项
即使你已按 500 行一批提交，若 innodb_flush_log_at_trx_commit 仍为默认值 1，每次 COMMIT 还是强制 fsync 到磁盘 —— 对 SSD/NVMe 提升有限，但对普通 SATA 盘就是性能瓶颈。
生产环境建议统一设为 2：
- 动态生效：
SET GLOBAL innodb_flush_log_at_trx_commit = 2;（需 SUPER 权限） - 永久生效：写入 my.cnf 的
[mysqld]段，否则重启即失效 - 注意协同：
sync_binlog最好也设为 2 或 1，避免 redo log 严守而 binlog 放养，导致主从不一致
别信“我用的是 NVMe，设 1 也没事”——实测中，设为 2 后批量插入吞吐常能翻倍，且崩溃丢失风险可控（最多 1 秒事务）。
最容易被忽略的三个细节
一是存储过程退出前没重置 autocommit。如果过程里设了 SET autocommit = 0 却没恢复，后续同一连接执行的普通 SQL 会意外进入手动事务模式，造成隐式锁表。
二是没处理异常分支。循环中一旦某条 INSERT 报错（如唯一键冲突），COMMIT 不会执行，事务悬停，连接持续占用锁资源。务必加 DECLARE EXIT HANDLER FOR SQLEXCEPTION 并 ROLLBACK。
三是误以为“存储过程封装了逻辑，就天然适合大事务”。其实 InnoDB 对事务长度无宽容度 —— 10 万行更新哪怕全在一个存储过程里跑完，只要没分段 COMMIT，照样触发 Lock wait timeout exceeded 和 purge 堵塞。
