Innodb的表锁问题_auto_increment

innodb最为大家津津乐道的就是它实现了行锁等高级特性，相比之下，myisam的表锁显得有些弱智。不过很多人都忽视了一点，innodb在MySQL5.0里有时候的行为也是表锁：比如说当表里有一个auto_increment字段的时候，innodb会在内存里保存一个计数器用来记录auto_increment的值，当插入一个新行数据时，就会用一个表锁来锁住这个计数器，直到插入结束。如果一行一行的插入数据则没有什么问题，但是如果大量的并发插入就废了，表锁会引起SQL堵塞，不但影响效率，而且可能会瞬间达到max_connections而崩溃。

在MySQL5.0版本，只要使用了auto_increment字段，那么innodb的表锁问题就无法回避，如果你必须面对大量并发插入的情况，要么听天由命，要么便只能抛弃auto_increment，转而使用自定义主键的方式，但必须小心选择，否则可能会陷入到另一个陷阱当中，比如说，有的人会通过使用UUID主键的方式来回避auto_increment表锁的问题，但是这可能会让应用陷入更严重的IO瓶颈问题之中去，原因可以参考我以前写的文章：

还有一个方法就是使用MySQL5.1，在这个版本里，出现了一个新的配置选项：innodb_autoinc_lock_mode，它是专门用来在使用auto_increment的情况下调整锁策略的，目前有三种选择：

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

innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)

 

CakePHP本身有一个uuid实现，所以一直以来，我都在尝试使用uuid做主键的可能性。虽然MySQL是我最常用的数据库，但是和auto_increment_int主键相比，我对uuid主键更有好感，一方面是因为uuid的数据库无关性，另一方面是当你想把程序分布在多台服务器上时，uuid操作更简单。

不过MySQL还没有原生的uuid支持，在和innodb表类型配合时，可能会出现一些问题：

首先，innodb会对主键进行物理排序，这对auto_increment_int是个好消息，因为后一次插入的主键位置总是在最后。但是对uuid来说，这却是个坏消息，因为uuid是杂乱无章的，每次插入的主键位置是不确定的，可能在开头，也可能在中间，在进行主键物理排序的时候，势必会造成大量的IO操作影响效率。

幸运的是，CakePHP的uuid算法最开始那部分的字符串是基于时间戳的，所以单就CakePHP的uuid而言，不存在这个问题，如果是其他的uuid算法，这个问题一定要仔细考虑。

其次，因为其他的索引要和主键关联，当主键是uuid时，和int相比必然会占用更大的空间，在较大的空间上检索肯定比在较小的空间上检索耗时。

这个问题解决起来办法不多，比较常见的方式是主键仍然用auto_increment_int来做，而另加一个uuid做唯一索引，表外键关联什么的，还用uuid来做，也就是说auto_increment_int只是一个形式上的主键，而uuid才是事实上的主键，这样，一方面int主键不会浪费太多空间，另一方面，还可以继续使用uuid。

还有一个问题是在MySQL里使用uuid，一般是用char(36)来声明字段，如果列编码是gbk/utf8这样的复杂的编码，会拖累主键的效率，这时候，我们那字段编码转换成ascii/latin1这样的简单编码会好一些。

 

 

################################################

 

官方文档已经给出了很好的描述，就不多说了。需要提醒的是MySQL5.1现在还没有Stable，要谨慎使用。

 

innodb_autoinc_lock_mode 下自增id不连续的原因

一、问题复现

文件/tmp/data.sql中两列，每列一个数字1;

 

输入

CREATE TABLE `t` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`k` int(10) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

load data infile '/tmp/data.sql' into table t(k);

show create table t;

 

结果：

CREATE TABLE `t` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`k` int(10) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

 

二、原因分析

我们知道在5.1.22在之后，InnoDB为了解决自增主键锁表的问题，引入了参数innodb_autoinc_lock_mode。这个值为0时，每次申请自增主键时需要锁表。

这个参数的默认值是1，设为此值时，每次会“预申请”多余的id(handler.cc: compute_next_insert_id)，而insert执行完成后，会特别将这些预留的id空出，动作就是特意将预申请后的当前最大id回写到表中(dict0dict.c:dict_table_autoinc_update_if_greater)。

 

三、简单计算预留

注意这个预留的策略是“不够时多申请几个”， 实际执行中是分步申请。至于申请几个，是由当时“已经插入了几条数据N”决定的。当auto_increment_offset＝1时，预申请的个数是 N-1。

所以会发现，当data.sql中只有一行时，你看不到这个现象，并不预申请。

而当有两行时（如文章开头的例子），则需要。多申请的数目为1，因此执行后的自增值为4 (1+2+1)。

若data.sql中有三行呢？由于执行第三行的id已经在执行第二行时预留了，所以直接使用，结果的自增值仍为4。

后续的就类推了，可自行分析下。

实际insert行	自增id增加值
2、3	3
4、5、6、7	7
8～15	15

 

################################################################

 

########################################

 

在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等语句，推荐使用程序将这些语句分成多条语句，一一插入，减少单一时间的锁表时间，荣嬷嬷的superdump就不错。

mysql5.1.22之后mysql进行了改进，引入了参数 innodb_autoinc_lock_mode，通过这个参数控制mysql的锁表逻辑，在介绍这个之前先引入几个术语，方便说明 innodb_autoinc_lock_mode。

1.“INSERT-like”：

INSERT, INSERT … SELECT, REPLACE, REPLACE … SELECT, and LOAD DATA, INSERT … VALUES(),VALUES()

2.“Simple inserts”

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

3.“Bulk inserts”

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

4.“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

一、innodb_autoinc_lock_mode = 0 (“traditional” lock mode)

这种方式就和mysql5.1.22以前一样，为了向后兼容而保留了这种模式，如同前面介绍的一样，这种方式的特点就是“表级锁定”，并发性较差

二、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。

三、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应该是够用了。

下面是官方文档举得几个例子，这里就不翻译的

For example, assume c1 is an AUTO_INCREMENT column of table t1, and that the most recent automatically generated sequence number is 100. Consider the following “mixed-mode insert” statement:

INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');

With innodb_autoinc_lock_mode set to 0 (“traditional”), the four new rows will be:

+—–+——+

| c1 | c2 |

+—–+——+

| 1 | a |

| 101 | b |

| 5 | c |

| 102 | d |

+—–+——+

The next available auto-increment value will be 103 because the auto-increment values are allocated one at a time, not all at once at the beginning of statement execution. This result is true whether or not there are concurrently executing “INSERT-like” statements (of any type).

With innodb_autoinc_lock_mode set to 1 (“consecutive”), the four new rows will also be:

+—–+——+

| c1 | c2 |

+—–+——+

| 1 | a |

| 101 | b |

| 5 | c |

| 102 | d |

+—–+——+

However, in this case, the next available auto-increment value will be 105, not 103 because four auto-increment values are allocated at the time the statement is processed, but only two are used. This result is true whether or not there are concurrently executing “INSERT-like” statements (of any type).

With innodb_autoinc_lock_mode set to mode 2 (“interleaved”), the four new rows will be:

+—–+——+

| c1 | c2 |

+—–+——+

| 1 | a |

| x | b |

| 5 | c |

| y | d |

+—–+——+

The values of x and y will be unique and larger than any previously generated rows. However, the specific values of x and y will depend on the number of auto-increment values generated by concurrently executing statements.

Finally, consider the following statement, issued when the most-recently generated sequence number was the value 4:

INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');

With any innodb_autoinc_lock_mode setting, this statement will generate a duplicate-key error 23000 (Can’t write; duplicate key in table) because 5 will be allocated for the row (NULL, ‘b’) and insertion of the row (5, ‘c’) will fail.