This page explains how InnoDB manages AUTO_INCREMENT columns, including initialization behavior, gap handling, and potential restart effects.
The innodb_autoinc_lock_mode system variable determines the lock mode when generating AUTO_INCREMENT values for InnoDB tables. These modes allow InnoDB to make significant performance optimizations in certain circumstances.
The innodb_autoinc_lock_mode system variable may be removed in a future release. See MDEV-19577 for more information.
When is set to 0, uses the traditional lock mode.
In this mode, holds a table-level lock for all statements until the statement completes.
When is set to 1, uses the consecutive lock mode.
In this mode, holds a table-level lock for all bulk statements (such as or ) until the end of the statement. For simple statements, no table-level lock is held. Instead, a lightweight mutex is used which scales significantly better. This is the default setting.
When is set to 2, uses the interleaved lock mode.
In this mode, does not hold any table-level locks at all. This is the fastest and most scalable mode, but is not safe for replication.
The value for an table can be set for a table by executing the statement and specifying the table option:
However, in and before, stores the table's counter in memory. In these versions, when the server restarts, the counter is re-initialized to the highest value found in the table. This means that the above operation can be undone if the server is restarted before any rows are written to the table.
In and later, the counter is persistent, so this restriction is no longer present. Persistent, however, does not mean transactional. Gaps may still occur in some cases, such as if a statement fails, or if a user executes or .
For example:
If the server is restarted at this point, then the counter will revert to 101, which is the persistent value set as part of the failed .
- an alternative to auto_increment available from
This page is licensed: CC BY-SA / Gnu FDL
ALTER TABLE tab AUTO_INCREMENT=100;CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY, i INT, UNIQUE (i)) ENGINE=InnoDB;
INSERT INTO t1 (i) VALUES (1),(2),(3);
INSERT IGNORE INTO t1 (pk, i) VALUES (100,1);
Query OK, 0 rows affected, 1 warning (0.099 sec)
SELECT * FROM t1;
+----+------+
| pk | i |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+------+
SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`i` int(11) DEFAULT NULL,
PRIMARY KEY (`pk`),
UNIQUE KEY `i` (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1# Restart server
SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`i` int(11) DEFAULT NULL,
PRIMARY KEY (`pk`),
UNIQUE KEY `i` (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1