If you specify an
AUTO_INCREMENT column for
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
InnoDB executes the equivalent of this
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
If a user invokes a
STATUS statement that displays output for the table
t and the auto-increment counter has not been
InnoDB initializes but does not
increment the value and stores it for use by later inserts. 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
After the auto-increment counter has been initialized, if a user
does not explicitly specify a value for an
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.
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 that
inserts all rows from one table into another.
InnoDB uses the in-memory auto-increment
counter as long as the server runs. When the server is stopped
InnoDB reinitializes the
counter for each table for the first
INSERT to the table, as described
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
0 for the
column in an
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.
AUTO_INCREMENT column must appear as the
first column in an index on an
Beginning with MySQL 4.1.12,
table option in
ALTER TABLE statements, to set
the initial counter value or alter the current counter value.
The same is true as of MySQL 4.1.14 for
CREATE TABLE. The effect of this
option is canceled by a server restart, for reasons discussed
earlier in this section.