分类： Mysql/postgreSQL
2011-09-30 17:49:08
以前内容主要是对官方文档中的意译，并加入了一些自己的理解（新版本还是很给力的）。侯哥原创，欢迎交流。
详细参照官方文档：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.
