MySQL Auto Increment列引发的死锁报错

某日线上操作 insert xxx select * from yyy 命令时触发应用大量报错，居然是一个死锁信息的报错:
"TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION"

排查原因，跟MySQL 自增特性相关设置有关。

先看看这个报错的含义，参考https://dev.mysql.com/doc/refman/5.6/en/innodb-deadlock-detection.html

this indicates that the number of transactions on the wait-for list has reached a limit of 200. A wait-for list that exceeds 200 transactions is treated as a deadlock and the transaction attempting to check the wait-for list is rolled back. The same error may also occur if the locking thread must look at more than 1,000,000 locks owned by transactions on the wait-for list

根据这个解释，某个特定锁资源的等待队列超过200时会触发这样的报错，那这个操作命令占用了什么锁资源呢？



环境交代:
隔离级别:RC
自动提交:ON
存储引擎:InnoDB

这样的环境，对yyy表的查询动作，不会有加锁，而insert xxx表，存在获取自增列，为控制写入无重复，肯定有锁存在，无其他表／行级别锁存在。

那么看看自增锁相关的信息:

表的自增值需要保障全表的唯一性以及顺序性，访问上在表级别进行控制。

如果作为表级锁进行控制，可想而知并发性能差。所以MySQL 5.1引入了innodb_autoinc_lock_mode，加入优化选项。

• 如果当前insert数量可预测，而且也没用别的语句持有Auto-Increment Locking表锁，那么这个insert不会持有表锁，而是在语句结束的时候，持mutex来修改每个自增值。
• 如果是批量导入操作，例如load data， insert select 这种记录数不可预先知晓的情况，在没有别的语句持有Auto-Increment Locking表锁时，直接持mutex修改每个自增值。

对应选项含义:

• innodb_autoinc_lock_mode = 0 全部用传统的表锁。持续语句执行(不是整个事务)的时间
• innodb_autoinc_lock_mode = 1 简单插入用mutex，但是如果当前已经有语句持有表锁，那简单插入也得等着了。对于语句级别的复制是安全的。
• innodb_autoinc_lock_mode = 2 所有语句都用mutex。会导致交叉增加。这样对于非row模式的复制，会导致主备数据不一致。

对于mixed复制模式来说1，2模式下，计数器简单的增加插入数量，不管有多少是特殊值。

所以，此类问题统一解法是设置innodb_autoinc_lock_mode=2, 要求复制模式为row。

关于自增列，待进一步探索!