大事务易卡住mysql 8.0，因长生命周期导致行锁、间隙锁、mdl锁长期持有，引发锁等待、undo log膨胀、主从延迟及超时；须改用分批提交（如每1000行commit），并动态调优批次大小。
为什么大事务在存储过程中容易卡住
MySQL 8.0 存储过程里一次性处理几万行数据时，BEGIN...COMMIT 包裹整个逻辑，很容易触发锁等待、undo log 膨胀、主从延迟甚至 Lock wait timeout exceeded。这不是因为 SQL 写得慢，而是事务生命周期太长——InnoDB 的行锁、gap lock、MDL 锁都一直挂着不放，其他会话一碰就堵。更隐蔽的是，autocommit=1 在存储过程里默认失效，你没显式 COMMIT，它就真的一路攒到结束才提交。
用分批提交替代单一大事务
核心思路：把“一次提交全部”改成“每 N 行提交一次”，让锁及时释放、undo 版本链不爆炸、复制压力更平滑。关键不是改 SQL，而是控制事务边界。
-
DECLARE v_counter INT DEFAULT 0;和DECLARE v_batch_size INT DEFAULT 1000;是必须的计数器和批次阈值 - 在循环体内部（比如
WHILE或游标处理中），每次处理完一行就SET v_counter = v_counter + 1; - 加判断：
IF v_counter % v_batch_size = 0 THEN COMMIT; END IF;，注意这里不能只写COMMIT，必须确保前面有START TRANSACTION或已处于事务中 - 循环结束后补一次
IF v_counter % v_batch_size != 0 THEN COMMIT; END IF;，避免尾部数据滞留
游标 + 分批提交的典型陷阱
用游标遍历大结果集时，很多人直接把 COMMIT 塞进游标循环里，结果发现报错 Cursor is not open 或事务中断后游标失效。这是因为 MySQL 8.0 中，COMMIT 会隐式关闭当前所有打开的游标。
- 正确做法是：把游标查询拆成“查 ID 列表”+“按 ID 批量更新”，避开游标生命周期问题
- 或者改用基于偏移的分页循环：
SELECT ... LIMIT offset, batch_size，每次查一批再处理一批，不依赖游标 - 如果非用游标不可，必须在每次
COMMIT后重新OPEN cursor_name，但要注意FETCH位置重置问题——这会让逻辑变复杂，一般不推荐
影响分批效果的三个隐藏参数
分批提交不是设个 v_batch_size 就万事大吉。下面三个参数会实际限制你的“批处理吞吐”：
-
innodb_log_file_size太小会导致频繁 checkpoint，批量写入时反而拖慢提交速度；建议至少 512MB 起（配合innodb_log_buffer_size=64M） -
max_allowed_packet会影响单次INSERT ... VALUES (...),(...)的最大行数，如果批量插入语句超限，会直接报错Packets larger than max_allowed_packet are not allowed -
binlog_format=ROW下，大事务分批后 binlog event 数量激增，若主库开了半同步（rpl_semi_sync_master_enabled=ON），每批都要等从库 ACK，可能比不分批还慢——这时要配合调低rpl_semi_sync_master_timeout或临时关半同步
真正难的不是写分批逻辑，而是根据监控里的 History list length、Innodb_row_lock_time_avg 和从库 Seconds_Behind_Master 动态调优 v_batch_size。固定写死 1000 行，在高并发更新场景下可能刚够用，在低峰期又浪费了 group commit 的红利。
