7点微信公众号:请点这里。7点测试网QQ总群:277957570。

7点测试网

 找回密码
 注册7点

QQ登录

只需一步,快速开始

查看: 915|回复: 0

mysql innodb 死锁

[复制链接]
发表于 2014-11-28 23:37:55 | 显示全部楼层 |阅读模式

innodb大量AUTO-INC LOCK 导致的DeadLOCK 1.现象
今天发现数据库报检查到死锁, show innodb status \G但从STATUS看,都是 lock mode AUTO-INC waiting.而且从应用开发了解,都是简单的INSERT和select。 没有交叉更新的事务。
如果是等待AUTO-INC锁,也不应该会产生死锁。因为AUTO-INC锁在某一时刻只会被一个事务/INSERT操作占有。INSERT一旦完成就会释放AUTO-INC锁。
那怎么会有死锁呢?
2.原因
今天查了一下资料,大致了解了当innodb_autoinc_lock_mode=0(mysql5.1.22之前连这个选项都没添加,所以默认都是0)时,在并发数大于208以上可能出现很多死锁的原因,如下:

在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时,就认为发生了死锁.
当并发数超过208的时候,发生了很多死锁,基于上面的分析我们可以得知,因为每一个并发做的是一个insert操作,需要加一个锁(暂且不管是s锁还是x锁),200多个线程并发执行,第n个insert正在执行,后来的线程无法获得锁,于是加入到lock queue中排队等待,这个时候lock queue长度超过了常量LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK所默认定义的200,然后Innodb在检查是否产生死锁的时候发现在递归检查lock_queue的时候深度超过了200,于是就认为发生了死锁.
废话了这么多就是当并发数超过208的时候mysql认为发生了死锁,因为AUTO-INC锁在mysql 5.1.22前,这个是表级锁,如果并发高、数据量大的话,很容易就到达208的
3.防止
找到原因后,下面就是怎么防止这个死锁的产生呢?
上面的测试也说了,在mysql 5.1.22中,已经改良了Innodb的auto_increment的锁机制,增加了一个innodb_autoinc_lock_mode选项,我们只需要使用其默认值1就可以很好的避免这个问题.
3.1 INSERT-like
在mysql5.1.22之前,mysql的“INSERT-like”语句(包INSERT, INSERT…SELECT, REPLACE,REPLACE…SELECT, and LOAD DATA)会在执行整个语句的过程中使用一个AUTO-INC锁将表锁住,直到整个语句结束(而不是事务结束)。因此在使用INSERT…SELECT、INSERT…values(…),values(…)时,LOAD DATA等耗费时间较长的操作时,会将整个表锁住,而阻塞其他的“INSERT-like”、Update等语句,推荐使用程序将这些语句分成多条语句,一一插入,减少单一时间的锁表时间。
mysql5.1.22之后mysql进行了改进,引入了参数 innodb_autoinc_lock_mode,通过这个参数控制mysql的锁表逻辑,在介绍这个之前先引入几个术语,方便说明 innodb_autoinc_lock_mode。
INSERT-like:
INSERT, INSERT … SELECT, REPLACE, REPLACE … SELECT, and LOAD DATA, INSERT … VALUES(),VALUES()

Simple inserts
就是通过分析insert语句可以确定插入数量的insert语句, INSERT, INSERT … VALUES(),VALUES()

Bulk inserts
就是通过分析insert语句不能确定插入数量的insert语句, INSERT … SELECT, REPLACE … SELECT, LOAD DATA

Mixed-mode inserts
下面两种,不确定是否需要分配auto_increment id
INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');
INSERT … ON DUPLICATE KEY UPDATE
3.2 innodb_autoinc_lock_mode选项 3.2.1 innodb_autoinc_lock_mode = 0 (“traditional” lock mode)
这种方式就和mysql5.1.22以前一样,为了向后兼容而保留了这种模式,如同前面介绍的一样,这种方式的特点就是“表级锁定”,并发性较差
3.2.2 innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
这种方式是新版本中的默认方式,推荐使用,并发性相对较高,特点是“consecutive”,即保证同一条insert语句中新插入的auto_increment id都是连续的。

这种模式下:
“Simple inserts”:直接通过分析语句,获得要插入的数量,然后一次性分配足够的auto_increment id,只会将整个分配的过程锁住。

“Bulk inserts”:因为不能确定插入的数量,因此使用和以前的模式相同的表级锁定。

“Mixed-mode inserts”:直接分析语句,获得最坏情况下需要插入的数量,然后一次性分配足够的auto_increment id,只会将整个分配的过程锁住。需要注意的是,这种方式下,会分配过多的id,而导致”浪费“。比如INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');会一次性的分配5个id,而不管用户是否指定了部分id;INSERT … ON DUPLICATE KEY UPDATE一次性分配,而不管将来插入过程中是否会因为duplicate key而仅仅执行update操作。
注意:当master mysql版本<5.1.22,slave mysql版本>=5.1.22时,slave需要将innodb_autoinc_lock_mode设置为0,因为默认的innodb_autoinc_lock_mode为1,对于INSERT … ON DUPLICATE KEY UPDATE和INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');的执行结果不同,现实环境一般会使用INSERT … ON DUPLICATE KEY UPDATE。
3.2.3 innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)
这种模式是来一个分配一个,而不会锁表,只会锁住分配id的过程,和innodb_autoinc_lock_mode = 1的区别在于,不会预分配多个,这种方式并发性最高。但是在replication中当binlog_format为statement-based时(简称SBR statement-based replication)存在问题,因为是来一个分配一个,这样当并发执行时,“Bulk inserts”在分配的时会同时向其他的INSERT分配,会出现主从不一致(从库执行结果和主库执行结果不一样),因为binlog只会记录开始的insert id。

测试SBR,执行begin;insert values(),();insert values(),();commit;会在binlog中每条insert values(),();前增加SET INSERT_ID=18/*!*/;。

但是row-based replication RBR时不会存在问题。

另外RBR的主要缺点是日志数量在包括语句中包含大量的update delete(update多条语句,delete多条语句)时,日志会比SBR大很多;假如实际语句中这样语句不是很多的时候(现实中存在很多这样的情况),推荐使用RBR配合innodb_autoinc_lock_mode,不过话说回来,现实生产中“Bulk inserts”本来就很少,因此innodb_autoinc_lock_mode = 1应该是够用了。
您需要登录后才可以回帖 登录 | 注册7点

本版积分规则

QQ|Archiver|手机版|小黑屋|7点测试网 ( 京ICP备09084002号

GMT+8, 2018-11-17 16:41 , Processed in 0.147115 second(s), 22 queries .

Powered by Discuz! X3.1

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表