MySQL/MariaDB无事务无显式锁连续INSERT...SELECT操作触发死锁问题

你之前的判断踩了两个常见的引擎机制认知误区，也是这类场景触发死锁的根本原因：

• 误区1：INSERT ... SELECT在InnoDB引擎下根本不是无锁操作。MariaDB/MySQL在默认的可重复读（REPEATABLE-READ）隔离级别下，这类语句的SELECT部分不会走普通的快照读逻辑，会强制给查询扫描到的源表记录、以及扫描路径上的间隙加共享性质的Next-Key Lock。这类锁是引擎自动加的，和你显式写LOCK IN SHARE MODE的效果完全一致，不需要手动加锁。
• 误区2：死锁的形成不需要你自己写的两条脚本互相竞争。这类统计脚本碰到的死锁，几乎都是脚本自身的锁申请路径，和源表上常驻的业务写入操作的锁路径撞上，形成了循环等待。

具体触发链路 #

完全可以解释为啥第二条INSERT刚启动就报错：

• 你跑第一条从history表拉数的INSERT ... SELECT时，执行顺序是先给history表的全扫描范围加共享Next-Key锁，之后才会向目标表history_count_table申请插入需要的排他写锁。如果执行过程中碰到目标表上的残留锁（比如之前的统计任务没跑完留下的未提交事务锁），语句就会卡在目标表的锁申请阶段——这时候你之前在history表上拿到的共享锁，不会因为要等其他锁就提前释放。
• 这两个history、history_uint表本来就是高频写入的监控类业务表，后台一直有常规的业务写入操作：这类写入要申请源表的排他行锁，写完提交之后才会释放锁。
• 等你启动第二条从history_uint拉数的INSERT ... SELECT时，第一步就要给history_uint的扫描范围申请共享读锁，这时候刚好凑成了闭环的等待链：
  1. 你的第一条统计语句：攥着history表的共享锁，等目标表的排他写锁
  2. 后台写入会话：攥着history_uint表的排他写锁，等history表上的共享锁释放才能完成提交
  3. 你的第二条统计语句：要申请history_uint表的共享读锁，必须等后台写入会话把排他锁放了才能往下走
• InnoDB的死锁检测机制会实时扫描锁等待队列，只要有新的锁申请加入形成等待环，就会立刻挑一个回滚代价最低的事务杀掉。因为第二条语句刚启动、手里攥的锁最少，自然会被优先选为牺牲者，直接抛死锁报错——这也是为啥你每次都能在第二条语句刚启动的阶段就碰到报错，根本等不到后面的逻辑执行。

• 把会话隔离级别临时调整为提交读（READ-COMMITTED）：这个隔离级别下，INSERT ... SELECT不再加Next-Key范围锁，只会对实际查询到的有效行加记录锁，锁的覆盖范围会缩到原来的10%都不到，锁冲突的概率会直接掉一个量级。
• 拆分锁申请链路：别直接用INSERT ... SELECT把源表和目标表串起来，先把聚合查询的结果写到会话级的临时表，等临时表生成完、源表上的锁全释放了，再从临时表把数据插到正式的目标表。这种方法直接把“攥着源表锁的时候同时申请目标表锁”的链路切断了，从根上就破坏了循环等待形成的条件。
• 给源表的查询匹配合适的索引：给history、history_uint表建(itemid, clock)的联合索引，让GROUP BY的聚合查询走索引覆盖扫描，别全表扫给整表加范围锁。
• 跑脚本前显式确认autocommit=1，别在单个事务里把所有INSERT ... SELECT都包起来，不然单事务持锁时间太长，会把锁冲突的概率拉得特别高。

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