MySQL：INSERT INTO SELECT引发源表INSERT冻结问题排查求助

可能原因 #

• InnoDB锁机制版本差异：MySQL 8.0.20到8.0.35之间，InnoDB对INSERT...SELECT的锁策略有调整。新版本可能扩大了行锁范围，或对分区表的锁处理逻辑变化，导致源表的实时INSERT请求无法获取锁，只能等待复制事务完成。
• 事务隔离级别隐式变更：虽然全局系统变量一致，但故障服务器的会话级事务隔离级别可能被修改（比如存储过程或应用显式设置）。REPEATABLE READ隔离级别下，INSERT...SELECT会持有更久的快照锁，而READ COMMITTED则释放更早，若故障服务器使用前者，可能加剧锁冲突。
• 分区表锁策略差异：源表是超大分区表，8.0.35对分区表的INSERT...SELECT可能锁定整个分区而非扫描的ID范围，导致实时INSERT所在分区被长时间锁定。
• 并行执行特性冲突：8.0.35默认开启的innodb_parallel_read_threads等并行读取参数，可能让INSERT...SELECT同时扫描多个分区/数据块，持有更多锁资源，引发锁竞争。
• 元数据锁（MDL）持有过长：INSERT...SELECT可能长时间持有源表的MDL锁，导致实时INSERT的存储过程（涉及表元数据访问）无法获取锁而阻塞。8.0.35对MDL锁的释放时机有调整，可能延长了持有时间。
• IO资源瓶颈：即使硬件参数相近，故障服务器的磁盘IO性能可能存在差异（比如磁盘磨损、RAID配置不同），INSERT...SELECT的大量读写操作占满IO带宽，导致实时INSERT的日志刷盘（innodb_log_file）延迟，表现为冻结。

排查步骤 #

• 定位锁等待类型：执行INSERT...SELECT时，用SHOW PROCESSLIST查看实时INSERT请求的状态，若为Waiting for table metadata lock则聚焦MDL锁；若为Waiting for row lock则查询INFORMATION_SCHEMA.INNODB_LOCKS和INNODB_LOCK_WAITS，明确锁的持有者和等待对象。
• 对比事务隔离级别：在故障和成功服务器上分别执行：

  SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;
  

  确认全局和会话级别的隔离级别完全一致。
• 分析InnoDB事务状态：执行SHOW ENGINE INNODB STATUS，查看TRANSACTIONS部分，检查INSERT...SELECT事务的锁持有情况，是否存在长事务或大量行锁。
• 验证并行执行参数：对比故障服务器与成功服务器的innodb_parallel_read_threads、optimizer_switch中parallel_execution相关配置，尝试关闭并行读取后重新测试：

  SET GLOBAL innodb_parallel_read_threads = 1;
  

• 简化场景测试：在故障服务器上用小范围ID执行INSERT...SELECT，同时执行实时INSERT，观察是否仍冻结；再测试非分区表的同操作，排除分区表影响。
• 检查存储过程执行计划：对存储过程中的校验SELECT语句执行EXPLAIN，确认是否存在全表扫描或索引失效，导致锁等待加剧。
• 监控系统资源：用iostat、vmstat等工具监控INSERT...SELECT期间的磁盘IO、CPU、内存使用率，确认是否存在IO饱和情况。
• 查阅版本变更日志：对比MySQL 8.0.20到8.0.35的官方变更日志，重点关注InnoDB锁、分区表、INSERT...SELECT相关的bug修复或特性调整。

内容的提问来源于stack exchange，提问作者Dmitry Vasiliev