AUTO_INCREMENT handling in the XtraDB and InnoDB storage engines have significant performance improvements. The innodb_autoinc_lock_mode server system variable determines the lock mode for auto-increments.

Traditional Lock Mode

Set by innodb_autoinc_lock_mode=0, the traditional lock mode holds a table-level lock for all INSERTs until the end of the statement.

Consecutive Lock Mode

Set by innodb_autoinc_lock_mode=1, the consecutive lock mode 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

Set by innodb_autoinc_lock_mode=2, no table-level locks are held at all. This is the fastest and most scalable mode, but is not safe for statement-based replication.

Initializing

Until MariaDB 10.2.3, InnoDB and XtraDB used an auto-increment counter that is stored in memory. When the server restarts, the counter is re-initialized, which cancels the effects of any AUTO_INCREMENT = N option in the table statements. From MariaDB 10.2.4, this restriction has been lifted and AUTO_INCREMENT is persistent. Persistent, however, does not mean transactional, and gaps may still occur such as after a failed INSERT IGNORE, or after a user-initiated 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

Comments

Comments loading...