MySQL5.0.X自增列引发Deadlock报错

TomLeng

最近在上午高峰期间，APP有如下较多的报错：
Deadlock found when trying to get lock; try restarting transaction

报错的SQL是对InnoDB表的insert语句，且插入auto_increment值

MySQL 5.0.X的InnoDB的auto-incrment列上的 auto-inc lock 是 X lock（排他锁/互斥锁），而且是个表级锁，且在insert完成之前不会释放 auto-inc lock，因此可以说无法真正并发的同时执行需要auto-incrment取值的insert语句

从 mysql> show innodb status\G 得到的信息大致意思是：
1. trx id 1412498935 得到 AUTO-INC lock，等待 InnoDB queue（每个表的auto-increment列在内存中都有一个计数器）给出自增值
2. trx id 1412498945 请求 AUTO-INC lock，但得到的是 AUTO-INC waiting lock，等待 AUTO-INC lock 被给予
3. trx id 1412515772 又来请求 AUTO-INC lock，这时MySQL认为“TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH”，意思就是说“等待 AUTO-INC lock 的事务太多了”，于是对该事务进行了回滚，但是MySQL对于这种情况给出的是“Deadlock found when trying to get lock; try restarting transaction”的报错

在InnoDB引擎中，在一条记录上同一时间有超过 200 个 X lock 时，MySQL会给出“TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH”信息，并放弃所有的锁检测，直接报“Deadlock found when trying to get lock”错误

官方说法，在InnoDB引擎上，当同一条记录上的锁深度(个数)超过LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK（代码中这个常量值为200）或锁开销大于LOCK_MAX_N_STEPS_IN_DEADLOCK_CHECK（代码中这个常量值为1000000）时，MySQL会给出“TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH”信息，并放弃所有的锁检测，直接报“Deadlock found when trying to get lock”错误

MySQL 5.1.X中，提供了新的锁模式，对于简单的取自增值insert语句，只在获得auto_increment值的时候对自增计数器加上一个轻量级的 X lock，而非表级锁,并且在得到auto_increment值后就立即释放了，而不是等到insert语句执行完才释放
设置 innodb_autoinc_lock_mode=0 沿用表级auto-inc锁
设置 innodb_autoinc_lock_mode=1 使用新的计数器级auto-inc锁