AUTO_INCREMENT Handling in XtraDB/InnoDB
Contents
AUTO_INCREMENT
Lock Modes
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.
Traditional Lock Mode
When innodb_autoinc_lock_mode
is set to 0
, InnoDB uses the traditional lock mode.
In this mode, InnoDB holds a table-level lock for all INSERTs until the end of the statement.
Consecutive Lock Mode
When innodb_autoinc_lock_mode
is set to 1
, InnoDB uses the consecutive lock mode.
In this mode, InnoDB holds a table-level lock for all bulk INSERTs (such as LOAD DATA or INSERT ... SELECT) until the end of the statement. For simple INSERTs, no table-level lock is held. Instead, a lightweight mutex is used which scales significantly better. This is the default setting.
Interleaved Lock Mode
When innodb_autoinc_lock_mode
is set to 2
, InnoDB uses the traditional lock mode.
In this mode, InnoDB does not hold any table-level locks at all. This is the fastest and most scalable mode, but is not safe for statement-based replication.
Setting AUTO_INCREMENT
Values
The AUTO_INCREMENT
value for an InnoDB table can be set for a table by executing the ALTER TABLE
statement and specifying the AUTO_INCREMENT
table option. For example:
ALTER TABLE tab AUTO_INCREMENT=100;
However, in MariaDB 10.2.3 and before, InnoDB stores the table's AUTO_INCREMENT
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 MariaDB 10.2.4 and later, the AUTO_INCREMENT
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 INSERT IGNORE
statement fails, or if a user executes ROLLBACK
or ROLLBACK TO SAVEPOINT
.
For example:
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
If the server restarts at this point, the auto_increment will revert to 101, the persistent value set as part of the failed INSERT IGNORE.
# 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
See Also
- AUTO_INCREMENT
- AUTO_INCREMENT FAQ
- LAST_INSERT_ID
- Sequences - an alternative to auto_increment available from MariaDB 10.3