Understanding InnoDB Table Locks, Intent Locks, and AUTO‑INC Locks in MySQL 8.0

1. Overview

MySQL uses a plug‑in storage‑engine architecture that can be viewed as two layers: the server layer and the storage‑engine layer. Accordingly, MySQL locks are also divided into two major categories: server‑level metadata locks (MDL) and storage‑engine locks implemented by each engine.

InnoDB supports table locks, row locks, and predicate locks (the latter used for spatial indexes and not covered here). Table locks include Shared (S), Exclusive (X), Intent Shared (IS), Intent Exclusive (IX), and AUTO‑INC locks. Row locks include Shared (S) and Exclusive (X), with a special Insert‑Intention lock ( LOCK_INSERT_INTENTION ). Row‑level shared and exclusive locks can be further classified as:

Record lock ( LOCK_REC_NOT_GAP )

Gap lock ( LOCK_GAP )

Next‑Key lock ( LOCK_ORDINARY )

The article then focuses on InnoDB table‑level locks.

2. Shared Lock & Exclusive Lock

A shared lock allows multiple transactions to read a table simultaneously, while an exclusive lock permits only one transaction to modify the table.

Example of acquiring a shared lock on table t1 :

lock tables t1 read;

Checking the lock result with Performance Schema:

select * from performance_schema.data_locks where object_name = 't1'\G

When autocommit is ON (default), the above statement does **not** acquire a table‑level shared lock. The lock behavior depends on two system variables: innodb_table_locks and autocommit . Both must be ON and OFF respectively for lock tables to grant a shared lock.

Setting autocommit = OFF :

set autocommit = OFF;
show variables like 'autocommit';

Now the same lock tables t1 read statement grants a shared lock, visible in performance_schema.data_locks with LOCK_TYPE = TABLE and LOCK_MODE = S .

Even when a table‑level shared lock is not granted, MySQL still creates a metadata lock ( SHARED_READ_ONLY ) that prevents other transactions from modifying the table.

3. Intent Shared Lock & Intent Exclusive Lock

Intent locks are not related to table‑level shared/exclusive locks; they cooperate with row‑level locks. When a transaction acquires a row‑level shared lock (e.g., SELECT ... LOCK IN SHARE MODE ) or an exclusive lock (e.g., SELECT ... FOR UPDATE , UPDATE , DELETE , INSERT ), it first records an intent lock on the table.

Example of a row‑level shared lock and its corresponding intent shared lock:

BEGIN;
SELECT * FROM t1 WHERE id = 10 LOCK IN SHARE MODE;
SELECT object_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE object_name = 't1'\G

The result shows a table‑level intent shared lock ( IS ) and a record‑level shared lock ( S,REC_NOT_GAP ).

Intent locks act as a registration system: before a transaction can place row‑level locks, it registers its intention at the table level, allowing InnoDB to quickly determine whether any row‑level exclusive locks exist without scanning all record locks.

4. AUTO‑INC Lock

When a table has an AUTO_INCREMENT column, MySQL must guarantee unique generated values. This is achieved with an AUTO‑INC lock, whose behavior is controlled by the system variable innodb_autoinc_lock_mode (values 0, 1, 2).

4.1 Traditional Mode (0)

The original mode used before MySQL 8.0. The table receives a full AUTO‑INC lock for the entire INSERT statement, ensuring consecutive values and safe statement‑based replication, but limiting concurrency to one transaction per table.

4.2 Consecutive Mode (1)

Default before MySQL 8.0. For simple INSERT ... VALUES statements, MySQL acquires a lightweight lock that is released immediately after generating the values, allowing higher concurrency while still producing consecutive values for a single statement. For INSERT ... SELECT , a full AUTO‑INC lock is still taken.

4.3 Interleaved Mode (2)

MySQL 8.0 default. All INSERT statements use only lightweight locks; the lock is taken and released for each generated value. This maximizes concurrency but may produce non‑consecutive values across concurrent statements, making statement‑based replication unsafe (though row‑based replication is unaffected).

5. Summary

InnoDB table‑level shared and exclusive locks are rarely needed because server‑level metadata locks cover most scenarios. Intent shared and intent exclusive locks exist to coordinate with row‑level locks, providing a fast registration mechanism. AUTO‑INC locks come in three modes, each balancing value continuity and concurrency differently.