Waiting Auto-INC LOCK导致死锁【转】
今天下午在看死锁相关的文档，到线上查看一生产数据库的时候，正好发现了show engine innodb status有一个死锁的信息：
LATEST DETECTED DEADLOCK ------------------------ 120626 20:00:30 *** (1) TRANSACTION: TRANSACTION 3 3052385643, ACTIVE 0 sec, process no 3898, OS thread id 1356507456 inserting mysql TABLES IN USE 1, locked 1 LOCK WAIT 16 LOCK struct(s), heap SIZE 3024, undo log entries 56 MySQL thread id 32282264, query id 9170497209 172.24.52.77 product_db UPDATE INSERT INTO occur_dead_lock_table(xx_id1,xx_id2,xx_cloumn1,STATUS,quantity,xxx_id3,price,gmt_create,gmt_modified) VALUES(19273026495,10378,0x313632373230373A36303039323B32303530333A33323637393435,1,1902,723417070,5600,now(),now()) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS SPACE id 879 page no 241 n bits 808 INDEX `ind_occur_dead_lock_table` OF TABLE `product_db/occur_dead_lock_table` trx id 3 3052385643 lock_m ode X locks gap BEFORE rec INSERT intention waiting Record LOCK, heap no 291 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 8; hex 800000000000288b; ASC ( ;; 1: len 8; hex 80000000000c400d; ASC @ ;; *** (2) TRANSACTION: TRANSACTION 3 3052385652, ACTIVE 0 sec, process no 3898, OS thread id 1663498560 setting auto-inc LOCK mysql TABLES IN USE 1, locked 1 15 LOCK struct(s), heap SIZE 3024, undo log entries 43 MySQL thread id 32231290, query id 9170497216 172.24.36.165 product_db UPDATE INSERT INTO occur_dead_lock_table(xx_id1,xx_id2,xx_cloumn1,STATUS,quantity,xxx_id3,price,gmt_create,gmt_modified) VALUES(19400057961,10379,0x313632373230373A333233323438333B32303530333A3235303333333336,1,19713,723417070,5600,now(),now()) *** (2) HOLDS THE LOCK(S): RECORD LOCKS SPACE id 879 page no 241 n bits 808 INDEX `ind_occur_dead_lock_table` OF TABLE `product_db/occur_dead_lock_table` trx id 3 3052385652 lock_m ode X Record LOCK, heap no 251 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 8; hex 800000000000288b; ASC ( ;; 1: len 8; hex 80000000000c4028; ASC @(;; ......省略部分内容。。。。。 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: TABLE LOCK TABLE `product_db/occur_dead_lock_table` trx id 3 3052385652 LOCK mode AUTO-INC waiting *** WE ROLL BACK TRANSACTION (2)
我们首先来分析一下LATEST DETECTED DEADLOCK的信息中,出现死锁的表为occur_dead_lock_table，数据库版本：
$mysql -V mysql Ver 14.12 Distrib 5.0.81, for unknown-linux-gnu (x86_64) using EditLine wrapper
表结构：
CREATE TABLE occur_dead_lock_table ( id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键', xx_id1 BIGINT(20) NOT NULL, xx_id2 BIGINT(20) NOT NULL, xx_cloumn1 VARCHAR(512) NOT, STATUS tinyint(4) NOT NULL , quantity INT(11) NOT NULL , xxx_id3 BIGINT(20) NOT NULL , price BIGINT(20) NOT NULL , gmt_create datetime NOT NULL COMMENT '记录创建时间', gmt_modified datetime NOT NULL COMMENT '记录最后修改时间', PRIMARY KEY (id), KEY ind_occur_dead_lock_table (xx_id2) ) ENGINE=InnoDB AUTO_INCREMENT=842353 DEFAULT CHARSET=gbk ;
死锁出现的场景： (一).通常发出死锁的时候，两个事务通常都会形成一个‘圈’的逻辑，事务1持有事务2所需要的锁，同时事务2又持有事务1所需要的锁，这样就造成了死锁,这是非常常见的一种死锁；
(二).还有一种情况是负责mysql死锁检测的是一个递归函数lock_deadlock_recursive（），当递归的深度depth超过LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK和cost超过LOCK_MAX_N_STEPS_IN_DEADLOCK_CHECK两个内部变量的时候，mysql就会抛出死锁信息；
从show innodb status中dead lock的信息来看：
(1).transaction1显示了正在向occur_dead_lock_table中插入一行数据，它正在等待获得表上的索引ind_occur_dead_lock_table的一个x锁（lock_mode X locks gap before rec insert intention waiting）； (2).transaction2的信息包括两部分：已经持有的锁和正在等待的锁；已持有的锁为一个向occur_dead_lock_table表中插入记录的时候，在其索引上获得的X锁；正在等待的锁为在表occur_dead_lock_table上的一个AUTO-INC锁；
上面的一个信息中很重要的一个锁等待为事务2在等待auto-inc锁，下面我们来分析一下在mysql中auto-increment列的实现：
5.0(5.1.22以下)和5.1(5.1.22以上)在自增列上的实现方式：
a.在5.0(5.1.22以下),auto_increment的实现机制为在innodb存储引擎的内存结构中维护一个自增长计数器，该计数器的值由： Select max(auto_inc_col) from t for update得到，插入操作首先从这个计数器中得到值，然后赋予自增长列（auto_inc locking）,可以看到这个锁本质上是一种表锁，那么其缺点就是必须等待前一个插入完成后,这样在大并发下,其插入性能的并发性不然较差； b.在5.1(5.1.22以上)中增加了另外一种实现机制，在innodb引擎中提供了一种轻量级互斥量（mutex）的自动增长机制，对于普通的insert 操作，innodb用一个mutex去对内存中的计数器进行累加，去掉了对原表的表锁机制，无疑会对部分插入提高较大的性能，该机制在5.1(5.1.22以上)中为自增长值实现的默认方式；
从上面可以看到，在mysql 5.0(5.1.22以下)中自增的id的实现采用的是table lock的方式，这样无疑是加大了死锁出现的概率，该bug已经在5.1.22中修复，新加入了参数就innodb_autoinc_lock_mode(默认为innodb_autoinc_lock_mode = 1 )：
There is a new innodb_autoinc_lock_mode system variable to configure the locking behavior thatInnoDB uses for generating auto-increment values. The default behavior now is slightly different from before,which involves a minor incompatibility for multiple-row inserts that specify anexplicit value for the auto-increment column in some but not all rows.
同时在大并发插入的情况，还会抛出dead lock的（TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH—场景2），该bug已经修复：
“SHOW INNODB STATUS deadlock info incorrect when deadlock detection aborts”. Print the correctlock owner when recursive function lock_deadlock_recursive() exceeds its maximum depth LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK.
综上所述，将版本从5.0升级到5.1的版本（5.1.48或者5.1.61)是不错的选择，同时在业务上可以考虑选择由应用程序产生了一个序列值来插入到数据库中，代替数据库的auto_incement。
