InnoDB AUTO-INC Lock Modes

The idea: who may take the next id, and when

A table with an AUTO_INCREMENT column has one counter, kept in memory in the table's dictionary object (and, since MariaDB 10.2.4, saved in the table so it survives a restart). Every insert takes the next value. When two sessions insert at the same time, InnoDB has to decide whether one waits for the other and whether each statement's ids come out in one unbroken run. The setting innodb_autoinc_lock_mode chooses that rule. It can only be set at server start, and it has three values: 0 traditional, 1 consecutive and 2 interleaved. In MariaDB the default is 1, in 10.x and 11.x alike. MySQL 8.0 changed its default to 2, so the same table can behave differently on the two servers.

The canvas shows table t (id INT AUTO_INCREMENT PRIMARY KEY, v CHAR(1)), empty, on a primary and on a replica. The top right box is the counter, the next id to hand out. Below it is the AUTO-INC lock, a special table-level lock, and the short autoinc mutex. The strip in the middle shows ids 1 to 24 of the primary: whose row each id became, or whether it was lost. Pick a statement for S1 and S2 and press Run; both start at the same moment and insert one row per tick.

Simple inserts and bulk inserts

The modes treat statements by one question: does InnoDB know in advance how many rows the statement inserts?

Kind	Examples	Row count
simple insert	INSERT … VALUES (…),(…), REPLACE … VALUES	known when the statement starts
bulk insert	INSERT … SELECT, REPLACE … SELECT, LOAD DATA	not known until the last row

A simple insert can reserve exactly the ids it needs and be done with the counter. A bulk insert can't; it either keeps the counter to itself until it ends, or keeps coming back for more.

Mode 0: the table lock for every insert

In traditional mode every insert into the table takes the AUTO-INC lock and keeps it until the statement ends. It does not wait for COMMIT; the lock is released as soon as the last row is in. While it is held, every other insert into that table waits, even a one-row INSERT … VALUES. Each statement's ids are consecutive, and the counter moves one row at a time, so no value is lost except by rollback. The price is that inserts into one table run one at a time (Demo: mode 0, the table lock serialises inserts: S2 waits three ticks for S1's three rows, then gets id 4).

Mode 1, the MariaDB default: lock only for bulk inserts

In consecutive mode a simple insert takes only the autoinc mutex, reserves as many ids as it has rows, and lets go at once. Two INSERT … VALUES run side by side: S1 reserves 1–3, S2 reserves 4–6, and neither waits (Demo: mode 1, simple inserts don't wait). A bulk insert still takes the AUTO-INC lock for the whole statement, so its ids are one unbroken run. A simple insert that arrives while a bulk insert holds the lock waits for it, as in mode 0 (Demo: mode 1, INSERT … SELECT holds the lock).

That demo also shows a gap with no rollback. Not knowing its row count, the INSERT … SELECT asks for ids in batches of 1, 2, 4, … (handler::get_auto_increment). Its four rows use id 1, then the batch 2–3, then id 4 out of the batch 4–7. InnoDB moved the counter past the whole batch when it was reserved, so 5, 6 and 7 are lost and S2's row gets 8.

Mode 2: interleaved, no table lock

In interleaved mode the AUTO-INC lock is never taken. Every statement goes to the counter under the mutex only, and a bulk insert does that once per batch, as its rows go in. Two concurrent INSERT … SELECT statements therefore get mixed ids: S1 gets 1, 3, 4, 7 and S2 gets 2, 5, 6, 11, and the unused ends of their last batches (8–10 and 12–14) are lost (Demo: mode 2, interleaved ids, ROW binlog). Ids are still unique and still grow within a statement; they are just not one run. This is the fastest mode for many concurrent loads into one table.

Where the gaps come from

An id that has been handed out is never given back, in any mode. Rolling the counter back would be unsafe, because another session may already have taken the next value. So:

• ROLLBACK deletes the rows but not the ids: S1 inserts 1–3, S2 inserts 4, S1 rolls back, and S1's next row gets 5. Ids 1–3 are a gap forever (Demo: ROLLBACK leaves a gap). A failed statement, for example a duplicate key on another unique column, loses its ids the same way.
• Batch tails: in modes 1 and 2 a bulk insert can reserve more ids than it uses.
• Interleaving in mode 2 spreads one statement's ids across another's.

So an AUTO_INCREMENT column is a unique, increasing key, not a row count and not a gap-free sequence. Invoice numbers that must have no gaps need their own counter, updated in the same transaction.

Statement-based replication

The binary log is written at COMMIT. With binlog_format=STATEMENT it holds the SQL text, and for an insert into an AUTO_INCREMENT table an extra SET INSERT_ID=<first id> event. The replica replays the statement with ids starting from that value, one after another. That only gives the same rows if the primary's ids were one unbroken run, which modes 0 and 1 promise for every statement, simple or bulk. In mode 2 they are not: the replica gives S1's rows 1–4 instead of 1, 3, 4, 7, and then S2's SET INSERT_ID=2 collides with them, ERROR 1062: Duplicate entry '2' for key 'PRIMARY', and the replica's SQL thread stops (Demo: mode 2 + STATEMENT binlog breaks the replica).

With binlog_format=ROW the binlog holds Write_rows events with every row's real id, and the replica inserts exactly those (Demo: mode 2, interleaved ids, ROW binlog). MariaDB's default binlog_format is MIXED, which still logs most statements as statements, so pair mode 2 with ROW. The same holds for rebuilding a server from its binlog with mariadb-binlog (point-in-time recovery).

Choosing a mode

Mode	AUTO-INC table lock	Ids of one statement	Safe with STATEMENT binlog
0 traditional	every insert, to statement end	consecutive	yes ✓
1 consecutive (MariaDB default)	bulk inserts only	consecutive	yes ✓
2 interleaved	never	may be mixed with others'	no ✗, use ROW

Mode 1 suits most servers. Mode 2 helps when many sessions run INSERT … SELECT or LOAD DATA into one table at once and the binlog is row-based (Galera Cluster requires mode 2 for this reason). Mode 0 is only for old setups that depend on its exact behaviour.

See also InnoDB record, gap and next-key locks for the row locks the same inserts take, the InnoDB lock system for how table and record locks are stored, MySQL replication and binlog group commit for how the binlog reaches the replica, inside an InnoDB index page for why increasing keys insert at the right edge of the B+ tree, and Snowflake ids and UUIDs for keys made without a central counter.

What the page leaves out

Mixed-mode inserts (VALUES (NULL), (5), (NULL)), which in mode 1 reserve ids for every row; INSERT … ON DUPLICATE KEY UPDATE using up an id when it updates; auto_increment_increment and auto_increment_offset; explicit ids larger than the counter, which move it forward; the counter after a restart (persistent since MariaDB 10.2.4, before that rebuilt as MAX(id) + 1); MIXED binlog format; the AUTO-INC lock being a real table lock in the lock system that can take part in deadlocks; and the batch size cap (65 536). Here a statement inserts one row per tick, S1 acts first within a tick, and every transaction is short.

References

AUTO_INCREMENT Handling in InnoDB (MariaDB Knowledge Base)

innodb_autoinc_lock_mode (MariaDB InnoDB System Variables)

AUTO_INCREMENT Handling in InnoDB (MySQL Reference Manual)

Binary Log Formats (MariaDB Knowledge Base)