> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/auto_increment-handling-in-innodb.md).

# AUTO\_INCREMENT Handling in InnoDB

This page explains how InnoDB manages AUTO\_INCREMENT columns, including initialization behavior, gap handling, and potential restart effects.

## AUTO\_INCREMENT Lock Modes

The [innodb\_autoinc\_lock\_mode](/docs/server/server-usage/storage-engines/innodb/innodb-system-variables.md#innodb_autoinc_lock_mode) system variable determines the lock mode when generating [AUTO\_INCREMENT](/docs/server/reference/data-types/auto_increment.md) values for [InnoDB](/docs/server/server-usage/storage-engines/innodb.md) tables. These modes allow [InnoDB](/docs/server/server-usage/storage-engines/innodb.md) to make significant performance optimizations in certain circumstances.

The [innodb\_autoinc\_lock\_mode](/docs/server/server-usage/storage-engines/innodb/innodb-system-variables.md#innodb_autoinc_lock_mode) system variable may be removed in a future release. See [MDEV-19577](https://jira.mariadb.org/browse/MDEV-19577) for more information.

### Traditional Lock Mode

When [innodb\_autoinc\_lock\_mode](/docs/server/server-usage/storage-engines/innodb/innodb-system-variables.md#innodb_autoinc_lock_mode) is set to `0`, [InnoDB](/docs/server/server-usage/storage-engines/innodb.md) uses the traditional lock mode.

In this mode, [InnoDB](/docs/server/server-usage/storage-engines/innodb.md) holds a table-level lock for all [INSERT](/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert.md) statements until the statement completes.

### Consecutive Lock Mode

When [innodb\_autoinc\_lock\_mode](/docs/server/server-usage/storage-engines/innodb/innodb-system-variables.md#innodb_autoinc_lock_mode) is set to `1`, [InnoDB](/docs/server/server-usage/storage-engines/innodb.md) uses the consecutive lock mode.

In this mode, [InnoDB](/docs/server/server-usage/storage-engines/innodb.md) holds a table-level lock for all bulk [INSERT](/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert.md) statements (such as [LOAD DATA](/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/load-data-into-tables-or-index/load-data-infile.md) or [INSERT ... SELECT](/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-select.md)) until the end of the statement. For simple [INSERT](/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert.md) statements, 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](/docs/server/server-usage/storage-engines/innodb/innodb-system-variables.md#innodb_autoinc_lock_mode) is set to `2`, [InnoDB](/docs/server/server-usage/storage-engines/innodb.md) uses the interleaved lock mode.

In this mode, [InnoDB](/docs/server/server-usage/storage-engines/innodb.md) does not hold any table-level locks at all. This is the fastest and most scalable mode, but is not safe for [statement-based](/docs/server/server-management/server-monitoring-logs/binary-log/binary-log-formats.md#statement-based-logging) replication.

## Setting AUTO\_INCREMENT Values

The [AUTO\_INCREMENT](/docs/server/reference/data-types/auto_increment.md) value for an [InnoDB](/docs/server/server-usage/storage-engines/innodb.md) table can be set for a table by executing the [ALTER TABLE](/docs/server/reference/sql-statements/data-definition/alter/alter-table.md) statement and specifying the [AUTO\_INCREMENT](/docs/server/server-usage/tables/create-table.md#auto_increment) table option:

```
ALTER TABLE tab AUTO_INCREMENT=100;
```

The [AUTO\_INCREMENT](/docs/server/reference/data-types/auto_increment.md) counter is persistent, so it survives a server restart. Persistent, however, does not mean transactional. Gaps may still occur in some cases, such as if a [INSERT IGNORE](/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-ignore.md) statement fails, or if a user executes [ROLLBACK](/docs/server/reference/sql-statements/transactions/rollback.md) or [ROLLBACK TO SAVEPOINT](/docs/server/reference/sql-statements/transactions/savepoint.md).

For example:

```sql
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 is restarted at this point, then the [AUTO\_INCREMENT](/docs/server/reference/data-types/auto_increment.md) counter will revert to `101`, which is the persistent value set as part of the failed [INSERT IGNORE](/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert-ignore.md).

```sql
# 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](/docs/server/reference/data-types/auto_increment.md)
* [AUTO\_INCREMENT FAQ](/docs/server/reference/data-types/auto_increment-faq.md)
* [LAST\_INSERT\_ID](/docs/server/reference/sql-functions/secondary-functions/information-functions/last_insert_id.md)
* [Sequences](/docs/server/reference/sql-structure/sequences.md) - an alternative to auto\_increment

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}