现在的位置: 首页 > 关系型数据库 > MySQL数据库 > MySQL故障 > 正文

自增表死锁问题分析及处理

时间:2016年05月30日 | 分类:MySQL故障 | 评论:0 条 | 浏览:2,542 次

今天,研发提交了一个死锁信息,涉及到自增表的死锁,测试,压测产生的死锁信息,并发500,信息如下:
1234

从上面死锁信息来看,lock mode AUTO-INC waiting,应该是表的自增列的问题,初步了解,这个死锁和 innodb_autoinc_lock_mode 的值有一定的关系,但也不因全归咎于mysql的问题。

从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的一致。

innodb_autoinc_lock_mode:

默认值:1,可取值为:0,1,2

在 mysql5.1.22之前,没有这个选项,默认都是0,在并发数大于208以上可能出现很多死锁

下面解释一下innodb_autoinc_lock_mode 几种默认值的含义:

0:traditonal (每次都会产生表锁)

1:consecutive (默认,可预判行数时使用新方式,不可时使用表锁,对于simple insert会获得批量的锁,保证连续插入)

Simple inserts:

直接通过分析语句,获得要插入的数量,然后一次性分配足够的auto_increment id,只会将整个分配的过程锁住。(INSERT, INSERT … VALUES(),VALUES())

Bulk inserts:

因为不能确定插入的数量,因此使用和以前的模式相同的表级锁定。(INSERT … SELECT, REPLACE … SELECT, LOAD DATA)

其中 insert ..... select ..... ,是特殊的select加X锁的情况,原因是为保证数据的一致性(M-S环境)

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)

2:interleaved (不会锁表,来一个处理一个,并发最高)

这种模式是来一个分配一个,而不会锁表,只会锁住分配id的过程,和innodb_autoinc_lock_mode = 1的区别在于,不会预分配多个,这种方式并发性最高。

从上面来看,一般 innodb_autoinc_lock_mode = 1 默认值,基本上满足需要。

虽说 innodb_autoinc_lock_mode = 2 不安全,但是在 binlog_format=ROW,transaction-isolation=READ-COMMITTED , innodb_autoinc_lock_mode = 2 是非常安全的。

至于,针对高并发的表,主键列的设置建议如下:

1.采用DB的自增属性,此时,需要调整DB参数,尽可能提高并发:
binlog_format=ROW,transaction-isolation=READ-COMMITTED , innodb_autoinc_lock_mode = 2

2.采用程序生成全局自增ID,利用redis、memcache集合生成 ,这也是推荐的方式