Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Traditional InnoDB Auto-Increment Locking Traditional InnoDB Auto-Increment Locking

The original implementation of auto-increment handling in InnoDB uses the following strategy to prevent problems when using the binary log for statement-based replication or for certain recovery scenarios.

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 server startup or after opening a table that was evicted from the table cache, InnoDB executes the equivalent of this statement for the first insert into the table:

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

InnoDB increments the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. By default, the value is incremented by 1. This default can be overridden by the auto_increment_increment configuration setting.

If the table is empty, InnoDB uses the value 1. This default can be overridden by the auto_increment_offset configuration setting.

If a SHOW TABLE STATUS statement examines the table t before the auto-increment counter is initialized, 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 table.

After the auto-increment counter has been initialized, if you do not explicitly specify a value for an AUTO_INCREMENT column, InnoDB increments the counter and assigns the new value to the column. If you insert 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 a user specifies NULL or 0 for the AUTO_INCREMENT column in an INSERT, InnoDB treats the row as if the value was not specified and generates a new value for it.

The behavior of the auto-increment mechanism is not defined if you assign 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 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 and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.

A server restart also cancels the effect of the AUTO_INCREMENT = N table option in CREATE TABLE and ALTER TABLE statements, which you can use with InnoDB tables to set the initial counter value or alter the current counter 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.

Download this Manual
User Comments
  Posted by Wagner Bianchi on June 25, 2015
If for some sort of functionality one need to update the PK's value using the LAST_INSERT_ID(), take care to run the SHOW TABLE STATUS to update the dictionary ai_col to avoid a PK violation.

mysql> truncate table tb01;
Query OK, 0 rows affected (0.22 sec)

mysql> insert into tb01 (a,b) values (0,'wbjr');
Query OK, 1 row affected (0.01 sec)

mysql> select a,b from tb01;
| a | b |
| 1 | wbjr |
1 row in set (0.00 sec)

mysql> update tb01 set a=LAST_INSERT_ID(a+1) where a=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> insert into tb01 (a,b) values (0,'wbjr');
ERROR 1062 (23000): Duplicate entry '2' for key 'a'

#: here it seems that the LAST_INSERT_ID() was updated with the last one
#: but not the internal ai_col

mysql> select last_insert_id();
| last_insert_id() |
| 2 |
1 row in set (0.00 sec)

mysql> show table status like 'tb01'\G
*************************** 1. row ***************************
Name: tb01
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 3
Create_time: 2015-06-25 11:47:29
Update_time: 2015-06-25 11:48:20
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
1 row in set (0.00 sec)

#: after running the SHOW TABLE STATUS, the ai_col get the table's MAX(ai_col)

mysql> insert into tb01 (a,b) values (0,'wbjr');
Query OK, 1 row affected (0.04 sec)

mysql> select a,b from tb01;
| a | b |
| 2 | wbjr |
| 3 | wbjr |
2 rows in set (0.00 sec)
Sign Up Login You must be logged in to post a comment.