(mysql.info.gz) InnoDB auto-increment column
Info Catalog
(mysql.info.gz) Converting tables to InnoDB
(mysql.info.gz) Using InnoDB tables
(mysql.info.gz) InnoDB foreign key constraints
15.7.3 How an `AUTO_INCREMENT' Column Works in `InnoDB'
-------------------------------------------------------
If you specify an `AUTO_INCREMENT' column for a table, the `InnoDB'
table handle in the data dictionary will contain a special counter
called the auto-increment counter that is used in assigning new values
for the column. The auto-increment 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, when a user first does an insert to a
table `T', `InnoDB' executes the equivalent of this statement:
SELECT MAX(ai_col) FROM T FOR UPDATE;
The value retrieved by the statement is incremented by one and assigned
to the column and the auto-increment counter of the table. If the table
is empty, the value `1' is assigned. If the auto-increment counter is
not initialized and the user invokes a `SHOW TABLE STATUS' statement
that displays output for the table `T', the counter is initialized (but
not incremented) and stored for use by later inserts. Note that in
this initialization we do 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.
Note that if the 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.
After the auto-increment counter has been initialized, if a 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. If the user does not explicitly specify a
value, `InnoDB' increments the counter by one and assigns the new value
to the column.
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. Two transactions cannot have the
`AUTO-INC' lock on the same table simultaneously.
Note that you may see gaps in the sequence of values assigned to the
`AUTO_INCREMENT' column if you roll back transactions that have gotten
numbers from the counter.
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.
Info Catalog
(mysql.info.gz) Converting tables to InnoDB
(mysql.info.gz) Using InnoDB tables
(mysql.info.gz) InnoDB foreign key constraints
automatically generated byinfo2html