AUTO-INC锁与innodb_autoinc_lock_mode
引言
AUTO-INC锁其实是innodb对于auto_increment的一种处理方法,它是在对有auto_increment关键字的索引进行插入的时候获取的一种特殊的表级锁.为什么需要AUTO-INC锁呢?原因是为了当数据插入到具有AUTO_INCREMENT列的表中时,给定序列的插入 statements的可预测且可重复的语句中分配auto-increment 值,并确保任何 auto-increment值分配的auto-increment是连续的,什么意思呢?
Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table …
Tx2: INSERT INTO t1 (c2) VALUES (‘xxx’);
上面有两条语句,AUTO-INC锁可以保证Tx1中插入的那1000条数据的auto_increment值要么全部大于Tx2中的值,要么全小于Tx2.
由此不难看出这种表的锁定粒度是一个语句,可以保证语句内的auto_increment值是连续的,也正因为此,其并发性显然并不高,当我们向一个有AUTO_INCREMENT关键字的主键插入值的时候,每条语句都要对这个表锁进行竞争,这样的并发潜力其实是很低下的,所以innodb通过innodb_autoinc_lock_mode的不同取值来提供不同的锁定机制,来显著提高SQL语句的可伸缩性和性能.
insert 类型
插入可以分为三类
- simple insert 即我们可以预先知道插入行数的插入
- bulk insert 我们提前不知道行数的插入 比如 insert into tablename from (select …),显然子查询中的行数我们是提前不知道的
- mixed-mode insert 就是简单插入的集合,但是其中有一些不是我们需要重新指定的auto_increment,举一个官方文档中的例子
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');,虽然看上去像简单插入,但是其中有些值是不需要我们重新生成auto_increment的,所以我们无法预知插入的行数.
innodb_autoinc_lock_mode
innodb_autoinc_lock_mode有三种取值,分别对应与不同锁定模式
- innodb_autoinc_lock_mode = 0(“传统”锁定模式)
- innodb_autoinc_lock_mode = 1(“连续”锁定模式)
- innodb_autoinc_lock_mode = 2(“交错”锁定模式)
innodb_autoinc_lock_mode = 0
在这种模式下,就用到了我们的主题AUTO-INC锁.这种锁定模式提供了与 MySQL 5.1 中引入innodb_autoinc_lock_mode configuration 参数之前相同的行为.其实MySQL默认的设置是后面要说的innodb_autoinc_lock_mode = 1,那么为什么还要保留传统锁定模式呢,答案就是为了向前兼容与性能测试.
这种模式其实就如我们上面的例子,即每当执行insert的时候,都会得到一个表级锁(AUTO-INC锁),使得语句中生成的auto_increment为顺序,且在binlog中重放的时候,可以保证master与slive中数据的auto_increment是相同的.但毕竟是表级锁,当在同一时间多个事务中执行insert的时候,对于AUTO-INC锁的争夺会限制并发潜力.
但有些情况我们也可思考我们是否需要维护这一限制,即同一语句中保证auto_increment的值为顺序,设置innodb_autoinc_lock_mode = 0.如果我们不使用binlog来进行主从复制的话,其实设置innodb_autoinc_lock_mode为2可能是一个更好的选择,其会放弃使用表级锁,带来更高的并发潜力与效率,但是也有坏处,就是在两处执行同一语句(主从复制),数据中的auto_increment值大概率是不同的.
innodb_autoinc_lock_mode = 1
这也是MySQL的默认模式,其在保证了innodb_autoinc_lock_mode = 0中每个语句中的值为顺序的情况下又增加了并发性,确实是一种很优秀的选择.
我们上面提到了三种插入方式,innodb_autoinc_lock_mode = 1的情况下对于三种插入方式的做法也不相同.
bulk insert
在最差的情况下,也就是bulk_insert时,我们无法确定插入项数的上界,举个例子
insert table test_incre from (select id from other);
子查询中我们显然不知道结果集是多少,也就没办法进行优化了,这种情况下与innodb_autoinc_lock_mode = 0时一样,使用AUTO-INC锁来保证每个语句中的auto_increment值顺序,同时保证主从复制中数据的一致.
在此模式下，“批量插入”使用特殊的AUTO-INC table-level 锁并保持它直到语句结束。这适用于所有INSERT … SELECT，更换… SELECT和负载数据 statements。只有一个持有AUTO-INC锁的语句可以在 time 执行。如果 bulk insert 操作的 source table 与目标 table 不同，则在从源 table 中选择的第一行上执行共享锁定后，将对目标 table 执行AUTO-INC锁定。如果 bulk insert 操作的源和目标是相同的 table，则在对所有选定行执行共享锁之后将执行AUTO-INC锁定。
上面这段话太重要了,它说明了两种情况,即bulk insert 操作的 source table 与目标 table 不同与bulk insert 操作的源和目标是相同的 table的两种情况,这样说起来好像有点绕,用大白话来说就是当bulk insert后面的select操作的表和插入的表如果是一张表,此时使用AUTO-INC lock锁住这张表,如果不是的话,则锁定select中的表,要插入的表使用AUTO-INC锁定.我们来做一个小实验验证下.
测试AUTO-INC锁
执行下述语句
create table test_increment
(id int primary key auto_increment, name varchar(20));
mysql root@(none):test_mysql1> begin;
Query OK, 0 rows affected
Time: 0.000s
mysql root@(none):test_mysql1> insert into test_increment(name)
-> select name from test_increment;
此时这个会话应该是正在执行这个语句的,但是我们还没有end,也就是此时仍旧加着锁
我们开启会话2,执行下述语句
mysql root@(none):test_mysql1> begin;
Query OK, 0 rows affected
Time: 0.000s
mysql root@(none):test_mysql1> insert into test_increment
-> values(1, 'lizhaolong');
此时我们可以发现会话2是阻塞的
这是上面所说的第二种情况,我们再来看第一种情况,即bulk insert 的源表和目标表不一样时会发生什么,测试代码如下
//会话1
mysql root@(none):test_mysql1> insert into test_increment
-> values(2,'lizhaolong');
Query OK, 1 row affected
Time: 0.005s
mysql root@(none):test_mysql1> begin;
Query OK, 0 rows affected
Time: 0.000s
mysql root@(none):test_mysql1> insert into test_increment(name)
-> select name from test_increment;
//会话2
mysql root@(none):test_mysql1> commit;
Query OK, 0 rows affected
Time: 0.000s
mysql root@(none):test_mysql1> insert into test_increment
-> values(1, 'lizhaolong');
Query OK, 1 row affected
Time: 0.003s
我们可以看到这个时候是直接插入成功的.并没有出现阻塞
simple insert
在简单插入中,因为我们是可以提前确定在此语句中我们需要的auto_increment值的,所以innodb做出优化,即通过在mutex的控制下获得所需数量的 auto-increment 值来避免AUTO-INC锁.这样的话扩展性和并发度就会大大增加了,我们来看一个直观的例子.
//会话1
mysql root@(none):test_mysql1> create table test_incre(id int primary key auto_increment);
Query OK, 0 rows affected
Time: 0.020s
mysql root@(none):test_mysql1> begin;
Query OK, 0 rows affected
Time: 0.000s
mysql root@(none):test_mysql1> insert into test_incre values(1),(3),(5),(7);
我们看到会话2可以直接执行成功.这也是innodb_autoinc_lock_mode=1,所作出的优化,避免了标记锁,只使用mutex进行分配期望的auto_increment值,且不必锁定到语句末尾.
mixed-mode insert
其实mixed-mode insert就是一个特殊的simple插入,只不过有些值是不用innodb帮我们分配的,这个时候innodb的策略是不使用AUTO-INC锁,使用mutex直接分配足够的auto_increment值,不使用的直接丢弃.我们来看一个例子.
假设我们有一张这样的表
mysql root@(none):test_mysql1> select * from test_incre;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 7 |
| 8 |
| 9 |
| 10 |
+----+
执行下述语句
insert into test_incre values(11),(null),(null),(14);
猜猜看值会是什么
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 14 |
| 16 |
| 17 |
+----+
这应该就是从14开始分配四个值,11是我们提供的,把15挤掉,剩下两个null赋值为16,17.剩下的值就丢弃了.
这样做可以使我们在mixed_mode_insert的情况下任保证高并发度,但是会丢弃掉一些auto_increment且不连续,不过既然我们已经使用mixed_mode_insert了,应该也不在乎auto_increment的值是否为顺序了.
innodb_autoinc_lock_mode = 2
在这个锁定模式下,不会使用AUTO-INC锁,多个语句可以同时执行,这是最快且最具扩展性的锁定模式,但是带来的缺点就是仅保证auto_increment唯一,但是不保证执行同样的语句的数据是相同的,也就是任何给定语句插入的行生成的值可能不是连续的,因为是并发执行,所以当然没办法保证.
总结
其实正如引言中所说,AUTO-INC锁其实就是一种保证一个语句内auto_increment连续的一种手段,默认情况下只有在bulk_insert中才会出现,给我们的启发时当我们在不在乎auto_increment是否顺序的时候(master与slave键值不同)的时候可以选择innodb_autoinc_lock_mode=2,增加并发度.其余时候选择最具扩展性的1模式即可.
