How InnoDB Auto-Increment Locking Works in MySQL

When MySQL’s InnoDB storage engine inserts rows into a table with an AUTO_INCREMENT column, it assigns generated numbers and coordinates concurrent inserts so they receive distinct values. InnoDB uses a special table-level lock, called an AUTO-INC lock, to coordinate number assignment. The configured lock mode determines which inserts use the lock and how much their allocations can overlap.

InnoDB uses row locks to control access to table rows. The AUTO-INC lock controls the assignment of generated identifiers. In MySQL 9.7, innodb_autoinc_lock_mode sets how InnoDB assigns numbers: mode 0 makes inserts take turns, mode 1 allows more overlap than mode 0, and mode 2 allows the most overlap. The InnoDB locking reference identifies AUTO-INC as a special table-level lock; the auto-increment handling rules explain when each mode uses it.

Insert type determines how allocation works

InnoDB distinguishes inserts by whether it knows in advance how many generated values a statement needs. That distinction matters most in mode 1.

A simple insert has a known number of rows when MySQL first processes it. A single-row INSERT or a multiple-row INSERT without a nested subquery qualifies. INSERT ... ON DUPLICATE KEY UPDATE is excluded from this category.

For a bulk insert, InnoDB cannot determine in advance how many generated values it will need. INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA are examples. InnoDB assigns values to incoming rows as it processes them.

A mixed-mode insert explicitly supplies auto-increment values for some rows while leaving others for InnoDB to generate. INSERT ... ON DUPLICATE KEY UPDATE also has mixed-mode behavior in the manual’s discussion. Because the number of generated values can be less predictable, mixed-mode statements can leave unused allocations.

The three lock modes

The innodb_autoinc_lock_mode setting selects how InnoDB coordinates generated values. Here is the practical difference in MySQL 9.7:

When InnoDB uses the table-level lock, it lasts through the statement, not necessarily until the transaction commits. The lock coordinates number assignment; InnoDB still uses row locks and other locks to protect table data.

Consider a bulk INSERT ... SELECT that loads many rows while another connection inserts one row. In mode 0, InnoDB allocates values for the statements in turn. In mode 1, the bulk statement holds the table-level lock, so the single-row insert waits while that lock is held. In mode 2, both statements can allocate concurrently, and the bulk statement’s values need not form one uninterrupted range.

The 9.7 manual identifies mode 2 as the default. The setting is global and not dynamic, so an operator cannot change it for a running server; plan a configuration change for server startup. Check the current value with:

SHOW GLOBAL VARIABLES LIKE 'innodb_autoinc_lock_mode';

Consecutive values do not make a gapless sequence

Modes 0 and 1 can assign consecutive generated values within an individual statement under the documented conditions. The guarantee applies within a statement; the table’s values can still have gaps. In modes 1 and 2, bulk inserts can leave gaps because InnoDB may reserve more values than the statement ultimately needs. Mixed-mode inserts can also consume values that no inserted row uses.

In all three modes, InnoDB does not reuse values generated for a transaction that later rolls back. The next insert advances past them. For example, the manual’s mixed-mode illustration begins after value 100: mode 1 allocates four values even though only two are automatically assigned, so the next available value is 105.

Use auto-increment values as identifiers, not as invoice numbers or other business sequences that must contain no gaps. If a business rule requires gapless numbering, implement and manage that sequence separately.

Choose the mode with replication in mind

Replication records changes on one MySQL server and applies them on another. With statement-based replication, the replica replays SQL statements, so the statements need to generate matching values. The MySQL 9.7 manual recommends mode 0 or 1 for this format and matching lock modes on the source and replicas. Mode 2 does not ensure matching values when concurrent statements are replayed.

With row-based replication, the replica applies the changed rows rather than relying on the same statement execution order. The manual says all three modes are safe for this purpose; it also permits all three with mixed-format replication. MySQL 9.7 uses mode 2 as its default in the context of row-based replication being the default format. Check the configured format before changing the lock mode; the replication format documentation describes the formats and their behavior.

Inspect lock waits when inserts stall

When an insert waits, check the current lock and wait data before concluding that the AUTO-INC lock caused the delay. Performance Schema’s data_locks and data_lock_waits tables expose lock and wait information. For example, this query shows current locks associated with a table:

SELECT
  ENGINE_TRANSACTION_ID,
  OBJECT_SCHEMA,
  OBJECT_NAME,
  LOCK_TYPE,
  LOCK_MODE,
  LOCK_STATUS
FROM performance_schema.data_locks
WHERE OBJECT_SCHEMA = 'app'
  AND OBJECT_NAME = 'orders';

Replace app and orders with the schema and table you are investigating. The InnoDB lock and wait information reference describes these Performance Schema tables. Treat the query as a snapshot: locks change as statements run, and current lock data does not provide a complete history of auto-increment allocations or explain every gap.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com