MySQL/InnoDB处理AUTO_INCREMENT（二）

configurable innodb auto-increment locking

as described in the previous section, innodb uses a special lock called the table-level auto-inc lock for inserts into tables with auto_increment columns. this lock is normally held to the end of the statement (not to the end of the transaction), to ensure that auto-increment numbers are assigned in a predictable（可预言的，可预料的） and repeatable（反复的） order for a given sequence of insert statements.

in the case of statement-based replication（主从复制，同步）, this means that when an sql statement is replicated（复制） on a slave server, the same values are used for the auto-increment column as on the master server. the result of execution of multiple insert statements is deterministic（确定性的）, and the slave reproduces the same data as on the master. if auto-increment values generated by multiple insert statements were interleaved（交叉存取的，隔行扫描的）, the result of two concurrent insert statements would be nondeterministic（不确定性的）, and could not reliably（依靠依赖） be propagated（传播） to a slave server using statement-based replication.

to make this clear, consider an example that uses this table:

create table t1 (
  c1 int(11) not null auto_increment,
  c2 varchar(10) default null,
  primary key (c1)
) engine=innodb;

suppose that there are two transactions running, each inserting rows into a table with an auto_increment column. one transaction is using an insert ... select statement that inserts 1000 rows, and another is using a simple insert statement that inserts one row:

tx1: insert into t1 (c2) select 1000 rows from another table ...

tx2: insert into t1 (c2) values ('xxx');

?innodb cannot tell in advance（提前） how many rows will be retrieved（取回） from the select in the insert statement in tx1, and it assigns（分配） the auto-increment values one at a time as the statement proceeds（前进）. with a table-level lock, held to the end of the statement, only one insert statement referring to table t1 can execute at a time, and the generation of auto-increment numbers by different statements is not interleaved（交叉存储的）. the auto-increment value generated by the tx1 insert ... select statement will be consecutive（连续的，连贯的）, and the (single) auto-increment value used by the insert statement in tx2 will either be smaller or larger than all those used for tx1, depending on which statement executes first.

as long as the sql statements execute in the same order when replayed（重放） from the binary log (when using statement-based replication, or in recovery scenarios（场景）), the results will be the same as they were when tx1 and tx2 first ran. thus, ?table-level locks held until the end of a statement make insert statements using auto-increment safe for use with statement-based replication. ?however, those locks limit concurrency and scalability（可扩展性，可伸缩性） when multiple transactions are executing insert statements at the same time.

in the preceding example, if there were no table-level lock, the value of the auto-increment column used for the insert in tx2 depends on precisely（精确的，严谨的） when the statement executes. if the insert of tx2 executes while the insert of tx1 is running (rather than before it starts or after it completes), the specific auto-increment values assigned by the two insert statements are nondeterministic（不确定性的）, and may vary（不同） from run to run.

innodb can avoid using the table-level auto-inc lock for a class of insert statements where the number of rows is known in advance, and still preserve（保持，保存） deterministic（确定性的） execution and safety for statement-based replication. further, if you are not using the binary log to replay sql statements as part of recovery or replication, you can entirely（完全地） eliminate（排除） use of the table-level auto-inc lock for even greater concurrency and performance, at the cost of permitting（允许） gaps in auto-increment numbers assigned by a statement and potentially（潜在的可能的） having the numbers assigned by concurrently executing statements interleaved.

for insert statements where the number of rows to be inserted is known at the beginning of processing the statement, innodb quickly allocates the required number of auto-increment values without taking any lock, but only if there is no concurrent session already holding the table-level auto-inc lock (because that other statement will be allocating auto-increment values one-by-one as it proceeds). more precisely（更准确的说）, such an insert statement obtains（获得） auto-increment values under the control of a mutex (a light-weight lock) that is not held until the statement completes, but only for the duration of the allocation process.

this new locking scheme enables much greater scalability, but it does introduce some subtle（微妙的） differences in how auto-increment values are assigned compared to the original mechanism. to describe the way auto-increment works in innodb, the following discussion defines some terms, and ?explains how innodb behaves using different settings of the innodb_autoinc_lock_mode configuration parameter?, which you can set at server startup. additional considerations are described following the explanation of auto-increment locking behavior.

first, some definitions:

“insert-like” statements

all statements that generate new rows in a table, including insert, insert ... select, replace,replace ... select, and load data.

“simple inserts”

statements for which the number of rows to be inserted can be determined in advance (when the statement is initially processed). this includes single-row and multiple-row insert and replace statements that do not have a nested subquery, but not insert ... on duplicate key update.

“bulk inserts”

statements for which the number of rows to be inserted (and the number of required auto-increment values) is not known in advance. this includes insert ... select, replace ... select, and load data statements, but not plain insert. innodb will assign new values for the auto_increment column one at a time as each row is processed.

“mixed-mode inserts”

these are “simple insert” statements that specify the auto-increment value for some (but not all) of the new rows. an example follows, where c1 is an auto_increment column of table t1:

insert into t1 (c1,c2) values (1,'a'), (null,'b'), (5,'c'), (null,'d');

another type of “mixed-mode insert” is insert ... on duplicate key update, which in the worst case is in effect an insert followed by a update, where the allocated value for the auto_increment column may or may not be used during the update phase.

there are three possible settings for the innodb_autoinc_lock_mode parameter:

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

?this lock mode provides the same behavior as before innodb_autoinc_lock_mode existed. for all “insert-like” statements, a special table-level auto-inc lock is obtained and held to the end of the statement. this assures（确保） that the auto-increment values assigned by any given statement are consecutive（连续的）.this lock mode is provided for:

backward compatibility.

performance testing.

working around issues with “mixed-mode inserts”, due to the possible differences in semantics described later.

2.innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)

default lock mode

this is the default lock mode. in this mode, “bulk inserts” use the special auto-inc table-level lock and hold it until the end of the statement. this applies to all insert ... select, replace ... select, and load data statements. only one statement holding the auto-inc lock can execute at a time.

with this lock mode, “simple inserts” (only) use a new locking model where a light-weight mutex is used during the allocation of auto-increment values, and no table-level auto-inc lock is used, unless an auto-inc lock is held by another transaction. if another transaction does hold an auto-inc lock, a “simple insert” waits for the auto-inc lock, as if it too were a “bulk insert”.

this lock mode ensures that, in the presence of insert statements where the number of rows is not known in advance (and where auto-increment numbers are assigned as the statement progresses), all auto-increment values assigned by any “insert-like” statement are consecutive, and operations are safe for statement-based replication.

simply put, the important impact of this lock mode is significantly better scalability. this mode is safe for use with statement-based replication. further, as with “traditional” lock mode, auto-increment numbers assigned by any given statement are consecutive. in this mode, there is no change in semantics compared to “traditional” mode for any statement that uses auto-increment, with one important exception.

the exception is for “mixed-mode inserts”, where the user provides explicit values for an auto_increment column for some, but not all, rows in a multiple-row “simple insert”. for such inserts, innodb will allocate more auto-increment values than the number of rows to be inserted. however, all values automatically assigned are consecutively generated (and thus higher than) the auto-increment value generated by the most recently executed previous statement. “excess” numbers are lost.

3.innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)

in this lock mode, no “insert-like” statements use the table-level auto-inc lock, and multiple statements can execute at the same time. ?this is the fastest and most scalable lock mode, but it is not safe when using statement-based replication or recovery scenarios when sql statements are replayed from the binary log.?

in this lock mode, auto-increment values are guaranteed（有保证的） to be unique and monotonically increasing across all concurrently executing “insert-like” statements. however, because multiple statements can be generating numbers at the same time (that is, allocation of numbers is interleaved across statements), the values generated for the rows inserted by any given statement may not be consecutive.

if the only statements executing are “simple inserts” where the number of rows to be inserted is known ahead of time, there will be no gaps in the numbers generated for a single statement, except for “mixed-mode inserts”. however, when “bulk inserts” are executed, there may be gaps in the auto-increment values assigned by any given statement.