长事务中join更新易卡住，因未提交事务会延长锁持有时间；若join无索引或全表扫描，则加大量行锁/间隙锁，直至事务结束才释放，导致阻塞、锁等待飙升和主从延迟。

长事务里用JOIN更新，为什么容易卡住

因为JOIN本身不直接导致长事务，但一旦嵌在未提交的事务中，它参与的每一条UPDATE或DELETE都会延长锁持有时间。尤其是当JOIN涉及大表、没走索引、或触发全表扫描时，InnoDB会加大量行锁甚至间隙锁——这些锁不会等语句执行完就释放，而要等到整个事务COMMIT或ROLLBACK才松手。

常见错误现象：SHOW PROCESSLIST里状态卡在Updating或Sending data；监控看到Innodb_row_lock_waits飙升；从库延迟突然拉高。

• 别以为“只是查一下再更新”就安全——SELECT ... JOIN ... FOR UPDATE一执行，锁就上了，后面哪怕只做毫秒级计算，锁也一直挂着
• MySQL 8.0+ 虽支持UPDATE ... JOIN语法，但它仍是单条语句的原子操作，无法自动分批；若匹配到10万行，就等于一次性锁10万行
• 如果JOIN条件字段没索引，优化器可能放弃使用索引，转而走全表扫描+全表加锁，后果比单纯DELETE更隐蔽

把JOIN更新拆成小事务的关键：先定位，再分片

不能直接对UPDATE t1 JOIN t2 ON ... SET ...加LIMIT——MySQL不支持该语法。必须把逻辑拆开：先查出要更新的主键集合，再按主键分段更新。

正确做法是两步走：第一步用SELECT id拿到目标记录ID列表（带索引WHERE + ORDER BY id）；第二步用WHERE id IN (..., ..., ...)或BETWEEN分批UPDATE。

• 务必确保SELECT和后续UPDATE用同一套索引路径，比如都依赖id主键，避免因统计信息变化导致执行计划漂移
• 不要用业务字段（如status、updated_at）做分片依据——值重复、NULL、并发修改都会让分片边界错乱
• 每批控制在500~2000行之间：太小则事务开销占比高；太大则单次锁持有时间仍过长，且失败后重试成本高
• 每次COMMIT后加time.sleep(0.05)（应用层），防止瞬间压垮MySQL连接池或刷盘IO

JOIN条件字段没索引？先建索引，再动事务

很多长事务问题表面是“慢”，根子在索引缺失。特别是UPDATE ... JOIN中ON子句的字段，如果不在被驱动表（t2）上建索引，就会触发t2全表扫描——每扫一行，就尝试加一次锁，最终锁住整张t2表或大片间隙。

验证方式很简单：EXPLAIN SELECT t1.id FROM t1 JOIN t2 ON t1.ref_id = t2.id WHERE t2.status = 'pending'，看type是不是ref或range，key列是否命中索引。

• 如果t2.status是高频筛选条件，但没索引，优先建INDEX idx_status (status)；若还需关联t1，考虑联合索引INDEX idx_status_ref (status, ref_id)
• 避免在t2上建太多二级索引——每次UPDATE t2都会触发索引维护，反而拖慢批量速度
• 建索引不是万能解药：如果t2有1亿行且status = 'pending'占80%，即使走了索引，也可能因选择率太高被优化器弃用，此时应改用主键范围分片

事务提交后，undo log不会立刻消失

很多人以为COMMIT完就万事大吉，其实InnoDB的Purge线程清理undo日志是有延迟的。尤其当事务刚提交、紧接着又有大量新事务写入时，旧undo版本可能堆积数秒甚至数分钟。

这意味着：即使你已严格分批COMMIT，如果批次间隔太短（比如连续发100个事务，每个隔10ms），undo表空间仍可能持续增长，监控里innodb_undo_log_truncated或磁盘使用率缓慢爬升。

• 观察information_schema.INNODB_METRICS中的log_logical_writes和undo_logs_added指标，确认是否真在“释放”而非“转移压力”
• 如果发现purge跟不上，可临时调高innodb_purge_batch_size（默认300），但别超过2000，否则 purge 线程自身会抢CPU
• 真正容易被忽略的一点：应用层分批提交时，别复用同一个数据库连接对象跑所有批次——连接空闲太久可能被中间件断开，导致后续批次报MySQL server has gone away，反而让事务卡在半途