mysql中insert...select引发的死锁

参考文章:

http://blog.chinaunix.net/uid-9950859-id-181376.html

http://blog.163.com/itjin45@126/blog/static/105107513201442102534166/

mysql5.6用户手册

http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-handling.html

 

作为mysql的新手,被最近在项目中碰到的死锁问题吓了一跳,赶紧记下来备忘.

使用show engine innodb status命令查看到的死锁相关信息如下:

(在navicat中执行该命令时会看到status列的值为空,此时别忘了右键全选-复制)

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-11-30 09:16:01 7f1e2c554700TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION

*** TRANSACTION:
TRANSACTION 649212, ACTIVE 0 sec setting auto-inc lock
mysql tables in use 2, locked 2
4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 138630, OS thread handle 0x7f1e2c554700, query id 7715687 10.132.43.55 iapproxy Sending data
INSERT INTO tsp_agt_msg_send (
     c1,

     c2,

     c3,

       ... 

) 

SELECT 

          'c1',

          'c2', 
          'c3', 
          ...
*** WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `diap_proxy`.`tsp_agt_msg_send` trx id 649212 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 695842
Purge done for trx's n:o < 695818 undo n:o < 0 state: running but idle
History list length 3160
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 695841, not started
MySQL thread id 138912, OS thread handle 0x7f1e1f5d7700, query id 7846296 192.168.180.54 root cleaning up
---TRANSACTION 0, not started
MySQL thread id 138901, OS thread handle 0x7f1e17a62700, query id 7844770 10.132.137.206 root cleaning up
---TRANSACTION 0, not started
MySQL thread id 138875, OS thread handle 0x7f1e1feba700, query id 7846415 10.132.137.206 root init
show engine innodb status
---TRANSACTION 695828, not started
MySQL thread id 138874, OS thread handle 0x7f1e1dd77700, query id 7846253 192.168.180.54 root cleaning up
---TRANSACTION 692142, not started
MySQL thread id 138843, OS thread handle 0x7f1e1ee39700, query id 7846251 192.168.180.54 root cleaning up
---TRANSACTION 673389, not started

 

首先来看下这一行

2015-11-30 09:16:01 7f1e2c554700TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION

这里涉及到一个死锁判定的规则:

在innodb源代码lock/lock0lock.c文件中,定义了两个常量:/* Restricts the length of search we will do in the waits-for     graph of transactions */#define LOCK_MAX_N_STEPS_IN_DEADLOCK_CHECK 1000000 /* Restricts the recursion depth of the search we will do in the waits-for    graph of transactions */#define LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK 200然后在检查是否产生死锁的函数lock_deadlock_occurs()中有如下代码: ret = lock_deadlock_recursive(trx, trx, lock, &cost, 0);switch (ret) {case LOCK_EXCEED_MAX_DEPTH:        产生死锁        ...        break;}其中的lock_deadlock_recursive()函数是递归函数,它会检查自身递归深度,其中有如下代码: ibool   too_far   = depth > LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK|| *cost > LOCK_MAX_N_STEPS_IN_DEADLOCK_CHECK;...if (too_far) {                return(LOCK_EXCEED_MAX_DEPTH);            }因此innodb在检查是否产生死锁时调用lock_deadlock_occurs()检查,这个函数再会调用lock_deadlock_recursive()递归检查锁的数目(不知道这么说是否确切?)，当递归的深度depth大于了一开始介绍的常量LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK,或者cost(不清楚这个代表什么)大于一开始介绍的常量LOCK_MAX_N_STEPS_IN_DEADLOCK_CHECK时,就认为发生了死锁.

如上所述这个死锁并非数据库真正发生了死锁,mysql主观的认为发生了死锁.

下面继续分析死锁日志,注意如下两行,

*** WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `diap_proxy`.`tsp_agt_msg_send` trx id 649212 lock mode AUTO-INC waiting
从5.6的用户手册中查找到AUTO-INC的相关信息:

 InnoDB uses a special lock called the table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns. This lock is normally held to the end of the statement (not to the end of the transaction), to ensure that auto-increment numbers are assigned in a predictable and repeatable order for a given sequence of INSERT statements

InnoDB在为自增列产生值的时候,使用一种叫做AUTO_INC的表级锁来做控制.这种锁是作用于语句的而不是事务(即语句执行完了锁就会被释放).使用这种锁是为了确保自增列的值的可预见性和可重复性.可预见性是说当一条insert语句作用于多行时,这些行的自增列基于第一行来说是可预见的;可重复执行是指基于语句的复制在slave重放时自增列的值与master的一致.

mysql提供参数innodb_autoinc_lock_mode来控制在产生自增列时锁的行为,可取值为0,1,2默认为1

0:对于每一个insert操作,都加AUTO_INC锁来为自增列分配值.

1:对于简单的insert操作,不加AUTO_INC锁,而使用一个轻量级的mutex,分配完毕后立即释放,不需要等到语句结束.

对于批量的insert操作,加AUTO_INC锁.当简单的insert操作检测到其他的事务持有AUTO_INC锁时,也需要等待AUTO_INC锁.

2.从不加AUTO_INC锁,并发性最好,但基于语句的复制和恢复将有问题.

 

insert操作有INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA.(凡是能产生新的一行的语句都是)

简单的insert语句:

在语句正式执行前,所作用的行数是确定的.比如insert子句,不包含子查询的replace子句.

 

批量插入的sql语句:
包括INSERT ... SELECT, REPLACE ... SELECT和LOAD DATA

混合型的sql语句:

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');即包含指定的又包含需要mysql帮忙自增的.

从以上分析可以得出,在innodb_autoinc_lock_mode=1的情况下insert...select语句会导致自增列的赋值需要加AUTO_INC锁.当在并发作insert的情况下会导致上述死锁.

总结: 在高并发的情况下对有自增列的表做插入操作应避免使用批量insert语句.