How Many Locks Does a Single UPDATE Acquire in MySQL 8.0?

Lock Layer Design

MySQL’s lock architecture mirrors its layered design: the Server layer acquires a metadata lock (MDL), InnoDB then acquires an intention lock (IS or IX), and finally row‑level locks are taken. Each layer works at a different granularity.

Locks in Each Layer

Server Layer – Metadata Locks (MDL)

Metadata locks protect the table definition, not the row data. Before a statement executes, MySQL checks that the table’s structure can be used and acquires an MDL on the table.

Shared Read (SR) : multiple sessions can hold it simultaneously; required for reading table definitions.

Shared Write (SW) : required for statements that read the definition and modify rows (INSERT, UPDATE, DELETE, SELECT FOR UPDATE).

Exclusive (X) : required for DDL that changes the table structure (CREATE, DROP, ALTER); only one session can hold it.

SELECT statements still acquire an SR lock to read column names and types. ALTER/DROP must obtain an X lock; if a long‑running SELECT holds an SR lock, the DDL will wait indefinitely.

MDL duration levels:

Statement‑level – released when the statement finishes.

Transaction‑level – released on transaction commit.

Explicit – held until an UNLOCK TABLES command.

In a transaction, a SELECT holds a transaction‑level SR lock until commit. MDLs are not governed by innodb_lock_wait_timeout but are affected by lock_wait_timeout.

Intention Locks – IS and IX

Intention locks are table‑level locks that declare a transaction’s intent to acquire row‑level locks.

Intention Shared (IS) : the transaction plans to acquire shared row locks (S) for reading rows.

Intention Exclusive (IX) : the transaction plans to acquire exclusive row locks (X) for modifying rows.

Compatibility:

IS can coexist with a shared table lock (S).

IX conflicts with a shared table lock (S).

Neither IS nor IX can coexist with an exclusive table lock (X).

IS and IX are compatible with each other.

Typical acquisition:

Plain SELECT (snapshot read) usually does not acquire IS/IX.

SELECT FOR SHARE acquires IS.

UPDATE, INSERT, DELETE, and SELECT FOR UPDATE acquire IX.

When LOCK TABLES WRITE is used, MySQL adds a server‑level “no‑read‑write” shared lock and an InnoDB exclusive table lock, which is not an intention lock.

Row‑Level Locks – Precise Semantics

InnoDB defines three lock types to control the exact range of locked data:

Next‑Key lock : locks the index record and the gap before it; default under REPEATABLE READ.

Gap lock : locks only the gap between index records, preventing other transactions from inserting into the gap.

Record lock : locks only the record itself; used for READ COMMITTED or unique‑index point queries.

Isolation level impact (summarized from the original comparison):

READ‑COMMITTED (RC)

Ordinary SELECT: snapshot read, no row lock.

Current‑read lock range: usually locks only matching records.

Lock release: at statement end.

Phantom reads: possible.

REPEATABLE‑READ (RR)

Ordinary SELECT: snapshot read, no row lock.

Current‑read lock range: range scans usually include gaps (Next‑Key).

Lock release: gap locks released at transaction end.

Phantom reads: prevented by gap locks in current reads.

Insert Intention Lock vs. Intention Lock

Insert Intention Lock (IIL) is distinct from table‑level IS/IX. When an INSERT targets a gap that already has a Gap lock, the transaction first acquires an IIL and then waits for the Gap lock to be released. Multiple INSERTs can hold IILs on the same gap simultaneously, but they all wait on the same Gap lock.

Implicit Locks

InnoDB creates an explicit lock object only when a conflict occurs. An uncommitted UPDATE leaves a trx_id on the clustered record; another transaction reading that record checks the trx_id and creates an explicit lock only if the owning transaction is still active. This design saves memory.

Other Lock Types

Additional lock types exist, such as auto‑increment locks, predicate locks, and predicate‑page locks, but the core concepts are covered by the three‑layer architecture.

Applying the Architecture to Interview Questions

What locks exist in MySQL? Server layer: MDL. InnoDB table‑level: IS, IX, auto‑increment lock. InnoDB row‑level: shared, exclusive, Next‑Key, Gap, Record, insert intention lock, implicit lock.

What is a gap lock and how does it relate to Next‑Key? A gap lock protects only the empty space between index records. A Next‑Key lock combines a record lock with the preceding gap; it is the default under REPEATABLE READ.

How does REPEATABLE READ prevent phantom reads? Current reads acquire Next‑Key and Gap locks, blocking other transactions from inserting into the scanned range, thereby eliminating phantom rows.

What is the purpose of IS/IX? They announce a transaction’s intent to acquire row‑level S or X locks, allowing explicit table locks and row locks to coexist without scanning every row.

What is a metadata lock and why does ALTER TABLE wait? MDL protects the table definition. ALTER requires an exclusive MDL; if another session holds a shared MDL (e.g., a long‑running SELECT), the ALTER will wait until that lock is released or times out.

How do deadlocks arise and how are they detected? Two transactions each hold locks the other needs, forming a cycle. Check SHOW ENGINE INNODB STATUS for the “LATEST DETECTED DEADLOCK” section or query performance_schema.data_locks and performance_schema.data_lock_waits. InnoDB rolls back the transaction with lower weight.

What does SELECT FOR UPDATE lock? Server layer acquires a shared‑write MDL, InnoDB acquires IX, and matching rows receive exclusive locks; under REPEATABLE READ the lock may be a Next‑Key or Gap lock depending on the query range.

References

MySQL 8.0 Reference Manual: InnoDB Locking

MySQL 8.0 Reference Manual: Metadata Locking

MySQL 8.0 Reference Manual: InnoDB Backup and Recovery