Innodb的表锁问题_auto_increment

时间:2022-06-28 09:13:57
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.