14.2.6.3. How AUTO_INCREMENT Columns Work in InnoDB
If you specify an AUTO_INCREMENT column for
an InnoDB table, the table handle in the
InnoDB data dictionary contains a special
counter called the auto-increment counter that is used in
assigning new values for the column. This counter is stored only
in main memory, not on disk.
InnoDB uses the following algorithm to
initialize the auto-increment counter for a table
T that contains an
AUTO_INCREMENT column named
ai_col: After a server startup, for the first
insert into a table T,
InnoDB executes the equivalent of this
statement:
SELECT MAX(ai_col) FROM T FOR UPDATE;
InnoDB increments by one the value retrieved
by the statement and assigns it to the column and to the
auto-increment counter for the table. If the table is empty,
InnoDB uses the value 1.
If a user invokes a SHOW TABLE STATUS
statement that displays output for the table
T and the auto-increment counter has not been
initialized, InnoDB initializes but does not
increment the value and stores it for use by later inserts. Note
that this initialization uses a normal exclusive-locking read on
the table and the lock lasts to the end of the transaction.
InnoDB follows the same procedure for
initializing the auto-increment counter for a freshly created
table.
After the auto-increment counter has been initialized, if a user
does not explicitly specify a value for an
AUTO_INCREMENT column,
InnoDB increments the counter by one and
assigns the new value to the column. If the user inserts a row
that explicitly specifies the column value, and the value is
bigger than the current counter value, the counter is set to the
specified column value.
You may see gaps in the sequence of values assigned to the
AUTO_INCREMENT column if you roll back
transactions that have generated numbers using the counter.
If a user specifies NULL or
0 for the AUTO_INCREMENT
column in an INSERT,
InnoDB treats the row as if the value had not
been specified and generates a new value for it.
The behavior of the auto-increment mechanism is not defined if a
user assigns a negative value to the column or if the value
becomes bigger than the maximum integer that can be stored in
the specified integer type.
When accessing the auto-increment counter,
InnoDB uses a special table-level
AUTO-INC lock that it keeps to the end of the
current SQL statement, not to the end of the transaction. The
special lock release strategy was introduced to improve
concurrency for inserts into a table containing an
AUTO_INCREMENT column. Nevertheless, two
transactions cannot have the AUTO-INC lock on
the same table simultaneously, which can have a performance
impact if the AUTO-INC lock is held for a
long time. That might be the case for a statement such as
INSERT INTO t1 ... SELECT ... FROM t2.
InnoDB uses the in-memory auto-increment
counter as long as he server runs. When the server is stopped
and restarted, InnoDB reinitializes the
counter for each table for the first INSERT
to the table, as described earlier.
InnoDB supports the AUTO_INCREMENT =
N table option in
CREATE TABLE and ALTER
TABLE statements, to set the initial counter value or
alter the current counter value. The effect of this option is
canceled by a server restart, for reasons discussed earlier in
this section.
