INNODB 自增锁的问题 innodb_autoinc_lock_mode 【转】

自增锁引发的悲剧 

http://www.mamicode.com/info-detail-1713555.html

背景

先描述下故障吧

• step0: 环境介绍

1.MySQL5.6.272.InnoDB3.Centos基本介绍完毕，应该跟大部分公司的实例一样

CREATETABLE`new_table`(`id`int(11) NOT NULL AUTO_INCREMENT,`x` varchar(200) DEFAULT ‘‘,
  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5908151 DEFAULT CHARSET=utf8 



CREATE TABLE `old_table`(`id`int(11)NOT NULL AUTO_INCREMENT,`xx`varchar(200)DEFAULT ‘‘,
  PRIMARY KEY (`id`))ENGINE=InnoDB AUTO_INCREMENT=5908151DEFAULT CHARSET=utf8

• step1: 业务需要导入历史数据到新表，新表有写入

1. insertintonew_table(x)selectxxfromold_table

2.批量插入在new_table上

• step2: 结果

showprocesslist;看到好多语句都处于executing阶段，DB假死，任何语句都非常慢，too many connection

• step3: 查看innoDB状况

showengineinnodbstatu\G

结果：==lock==---TRANSACTION 7509250, ACTIVE 0 sec setting auto-inc lock--一堆TABLE LOCK table `xx`.`y‘y` trx id 7498948lockmode AUTO-INC waiting  --一堆

模拟问题，场景复现

让问题再次发生才好定位解决问题

• 表结构

| t_inc | 
CREATE TABLE`t_inc`(
`id`int(11) NOT NULL AUTO_INCREMENT,
`x` varchar(199) DEFAULT ‘‘,
PRIMARY KEY (`id`)) 
ENGINE=InnoDB AUTO_INCREMENT=5908151 DEFAULT CHARSET=utf8 |

CREATE TABLE `t_inc_template`(
`id` bigint(20) unsigned  NOT NULL AUTO_INCREMENT,
`cookie_unique`varchar(255) NOT NULL DEFAULT ‘‘COMMENT ‘‘,
PRIMARY KEY (`id`),)
ENGINE=InnoDB AUTO_INCREMENT=5857489DEFAULT CHARSET=utf8

• step1

session1：insert into t_inc(x) select  cookie_unique    from  t_inc_template;


session2：mysqlslap -hxx -ulc_rx -plc_rx -P3306  --concurrency=10 --iterations=1000 --create-schema=‘lc‘ 
--query="insert into t_inc(x) select ‘lanchun‘;"  --number-of-queries=10   产生并发,然其自动分配自增id。

• step2：观察

|260126| lc_rx       | x:22833| NULL |Sleep|8|| NULL              |

|260127| lc_rx       |x:22834 | lc   |Query|8| executing             | insert  into  t_inc(x) select ‘lanchun‘|

|260128| lc_rx       | x:22835| lc   |Query|8| executing             | insert into t_inc(x)  select ‘lanchun‘|

|260129| lc_rx       |x:22836| lc   |Query|8| executing              | insert into t_inc(x)  select  ‘lanchun‘|

|260130| lc_rx       | x:22837| lc   |Query|8| executing             | insert into t_inc(x)  select ‘lanchun‘|

|260131| lc_rx       |x:22838| lc   |Query|8| executing               | insert into t_inc(x)  select  ‘lanchun‘|

|260132| lc_rx       | x:22840| lc   |Query|8| executing             | insert into t_inc(x)  select  ‘lanchun‘|

|260133| lc_rx       |x:22839| lc   |Query|8| executing              | insert into t_inc(x)  select  ‘lanchun‘|

|260134| lc_rx       | x:22842| lc   |Query|8| executing              | insert into t_inc(x)  select ‘lanchun‘|

|260135| lc_rx       |x:22841| lc   |Query|8| executing                | insert into t_inc(x)  select ‘lanchun‘|

|260136| lc_rx       | x:22843| lc   |Query|8| executing                | insert into t_inc(x)  select ‘lanchun‘|

• step3 show engine innodb status

TABLE LOCK table  `lc`.`t_inc`  trx id  113776506   lock  mode  AUTO-INC waiting

一堆这样的waiting

然后卡死

好了问题已经复现，大概也知道是什么原因造成了，那就是：AUTO-INC lock

自增锁

接下来聊聊自增锁

和auto_increment相关的insert种类

1. INSERT-like

解释：任何会产生新记录的语句，都叫上INSERT-like，
比如：INSERT,INSERT ...SELECT,REPLACE,REPLACE ...SELECT,and  LOAD DATA
 
 
总之包括：“simple-inserts”,“bulk-inserts”,and  “mixed-mode” inserts.

1. simple insert

插入的记录行数是确定的：
比如：insert into  values，replace
但是不包括：INSERT ...ON DUPLICATE KEY UPDATE.

1. Bulk inserts

插入的记录行数不能马上确定的，比如：INSERT ...SELECT,REPLACE ...SELECT,and  LOAD DATA

1. Mixed-mode inserts

这些都是simple-insert，但是部分auto  increment值给定或者不给定

1.INSERT INTO t1 (c1,c2)  VALUES (1,‘a‘),(NULL,‘b‘),(5,‘c‘),(NULL,‘d‘);  2. INSERT...ON   DUPLICATEKEY  UPDATE

以上都是Mixed-mode inserts

锁模式

1. innodb_autoinc_lock_mode = 0 (“traditional” lock mode)

优点：极其安全缺点：对于这种模式，写入性能最差，因为任何一种insert-like语句，都会产生一个table-level   AUTO-INC  lock

1. innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)

原理：这是默认锁模式，当发生bulk inserts的时候，会产生一个特殊的AUTO-INC table-level lock直到语句结束，
注意：（这里是语句结束就释放锁，并不是事务结束哦，因为一个事务可能包含很多语句）对于Simple inserts，则使用的是一种轻量级锁，只要获取了相应的auto  increment就释放锁，并不会等到语句结束。

PS：当发生AUTO-INC table-level lock的时候，这种轻量级的锁也不会加锁成功，会等待。。。。

优点：非常安全，性能与innodb_autoinc_lock_mode =0 相比要好很多。

缺点：innodb_autoinc_lock_mode = 1 发生bulk inserts的时候还是会产生表级别的自增锁

深入思考：为什么这个模式要产生表级别的锁呢？因为：他要保证bulk insert自增id的连续性，防止在bulk insert的时候，被其他的insert语句抢走auto  increment值。

1. innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)

原理：当进行bulk insert的时候，不会产生table级别的自增锁，因为它是允许其他insert插入的。来一个记录，插入分配一个auto值，不会预分配。

优点：性能非常好，提高并发，SBR不安全

缺点：一条bulk insert，得到的自增id可能不连续
    SBR模式下：会导致复制出错，不一致

延伸

当innodb_autoinc_lock_mode = 2 ，SBR为什么不安全

• master 插入逻辑和结果

表结构：a primary key auto_increment,b varchar(3)

time_logic_clock	session1：bulk insert（）	session2： insert like
0	1,A
1		2,AA
2	3,B
3	4,C
4		5,CC
5	6,D

最终的结果是：

a	b
1	A
2	AA
3	B
4	C
5	CC
6	D

• slave的最终结果

因为binlog中session2的语句先执行完，导致结果为

a	b
1	AA
2	CC
3	A
4	B
5	C
6	D

RBR为什么就安全呢？

因为RBR都是根据row image来的，跟语句没关系的。

好了，通过以上对比分析，相信大家都知道该如何抉择了吧？

总结

• 如果你的binlog-format是row模式，而且不关心一条bulk-insert的auto值连续（一般不用关心），那么设置innodb_autoinc_lock_mode = 2 可以提高更好的写入性能。