关于mysql auto_increment所带来的锁表操作

以前内容主要是对官方文档中的意译，并加入了一些自己的理解（新版本还是很给力的）。侯哥原创，欢迎交流。

详细参照官方文档：http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html#innodb-auto-increment-configurable

在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.