How MVCC and Dead Tuples Trigger PostgreSQL VACUUM
In PostgreSQL, an UPDATE creates a new row version, and a DELETE marks a row version as deleted. The old versions stay…
Categories
How InnoDB Handles AUTO_INCREMENT Locking
When multiple clients insert rows into an InnoDB table with an AUTO_INCREMENT column, InnoDB assigns each row a generated ID and makes sure no two rows get the same value. In some configurations, InnoDB controls ID allocation with an internal table-level lock called an AUTO-INC lock. An insert that needs the lock waits while another statement holds it. This wait is especially relevant when a statement inserts an unknown number of rows.
The innodb_autoinc_lock_mode setting determines when InnoDB uses the lock. Lower modes keep each statement's generated IDs consecutive. Higher modes allow more inserts to run at the same time, but IDs from different statements can interleave. The MySQL 8.4 manual describes three modes, numbered 0, 1, and 2.
Why bulk inserts behave differently
InnoDB allocates IDs differently depending on whether it knows how many rows a statement will add. A simple insert tells InnoDB the number of rows, as in INSERT ... VALUES, including a multi-row VALUES statement. A bulk insert does not tell InnoDB the number of rows in advance. Examples include INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA.
For a simple insert, InnoDB can reserve all the needed IDs while processing the statement. For a bulk insert, InnoDB needs IDs as it reads and adds rows. When a bulk insert runs alongside another insert, InnoDB either makes one statement wait or lets the statements' ID allocations interleave. The distinction and statement examples appear in the manual’s section on InnoDB AUTO_INCREMENT handling.
An AUTO-INC lock controls ID allocation for inserts into the same table. While a statement holds the lock, another insert that needs it waits. In modes that use the lock, InnoDB normally releases it when the statement ends, not when the transaction ends. The lock’s scope and behavior are described in InnoDB’s locking reference.
What each lock mode changes
| Mode | Name | Behavior during concurrent inserts |
|---|---|---|
0 |
Traditional | Every insert-like statement holds the table-level AUTO-INC lock until the statement ends. This serializes ID allocation and gives each statement consecutive generated values. |
1 |
Consecutive | Bulk inserts hold the AUTO-INC lock until the statement ends. Simple inserts allocate their known number of IDs using a short-lived mutex, unless a bulk insert already holds the table-level lock. |
2 |
Interleaved | Insert-like statements do not use the table-level AUTO-INC lock. They can allocate IDs concurrently, so values assigned to one statement need not form a consecutive range. |
Mode 0 makes insert statements wait for one another when they use the same table. Choose it when you need each statement to receive consecutive IDs and can accept fewer concurrent inserts.
In mode 1, InnoDB does not hold the table-level lock for the full duration of a simple insert. A bulk insert still holds the lock, so a simple insert into the same table waits until the bulk statement ends. This mode supports statement-based replication, where replaying statements requires predictable ID allocation.
Mode 2 prevents insert statements from waiting for this lock. Inserts can still slow one another while InnoDB works on indexes or storage. When bulk inserts overlap, InnoDB can assign one statement IDs between IDs assigned to another. The manual’s mode descriptions document these trade-offs.
In MySQL 8.4, mode 2 is the default. The manual recommends mode 0 or 1 for statement-based replication and requires matching settings on the source and replicas; all three modes are safe with row-based or mixed-format replication. Check the replication format before changing the setting.
What to expect from IDs under concurrency
Generated IDs identify rows, but they do not form a gap-free sequence. In every mode, when a transaction rolls back, InnoDB does not reuse the IDs it already allocated. Bulk inserts can leave gaps because InnoDB may reserve more IDs than the statement uses. Mixed-mode inserts combine IDs supplied in the statement with IDs generated by InnoDB, and can also leave unused values.
The MySQL manual describes a mixed-mode insert starting after ID 100. When the statement supplies IDs for some rows and asks InnoDB to generate IDs for others, modes 0 and 1 can assign generated IDs 101 and 102. In mode 1, the next available value can still be 105 because InnoDB reserved more values than the statement used. In mode 2, concurrent statements affect which IDs the statement receives.
For a multi-row insert, LAST_INSERT_ID() returns the first generated ID for that statement, not a list of all generated IDs. MySQL keeps this value separately for each connection, so inserts from another client do not change it. See the MySQL manual’s documentation for LAST_INSERT_ID(). Do not infer that a row was deleted or an insert failed simply because the IDs have a gap.
Check the running mode before changing it
Run this query to see the active setting:
SELECT @@innodb_autoinc_lock_mode;In MySQL 8.4, innodb_autoinc_lock_mode is a global setting that you cannot change while the server is running. Set a different value at startup, then restart the server. The MySQL 8.4 system-variable reference documents the setting and its default.
When inserts stall, first identify the kind of statement and check the active lock mode. A mode 1 simple insert can wait behind a bulk insert holding the AUTO-INC lock. Mode 2 removes that lock-based wait, but a slowdown by itself does not prove that AUTO-INC locking caused it.
What I'm buildingDelegate 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