Beyond Row and Table Locks: Uncovering MySQL’s Hidden Lock Types

Lock Classification Overview

MySQL uses a variety of lock types beyond the commonly mentioned row, table, optimistic, and pessimistic locks. The following sections describe several less‑known locks that affect real‑world usage.

Global Lock (FTWRL)

The FLUSH TABLES WITH READ LOCK command acquires a global read‑only lock on the entire server. FLUSH TABLES WITH READ LOCK; While the lock is held, the following statements are blocked:

DML statements ( INSERT, UPDATE, DELETE)

All DDL statements

Transaction commit statements

Release the lock with: UNLOCK TABLES; Typical use case: logical backup of the whole database. Because the lock stops writes on the primary and can cause replication lag on replicas, more sophisticated backup methods (e.g., mysqldump --single-transaction) are preferred.

Intention Locks

Intention locks are table‑level locks that indicate the type of row‑level locks a transaction intends to acquire. They allow the storage engine to decide quickly whether a table lock can be granted.

Example (e‑commerce inventory update):

UPDATE product SET quantity = quantity - 1 WHERE id = 10;

This statement acquires a row lock on the row with id=10 and an exclusive intention lock on the product table. UPDATE product SET quantity = quantity + 10; The second statement has no WHERE clause, so it requires a table lock. The engine checks the intention lock to see whether any rows are already locked; if not, the table lock can be granted. Intention locks are compatible with each other, which speeds up table‑lock acquisition.

Auto‑Increment Lock

When a table contains an AUTO_INCREMENT column, MySQL must guarantee unique, sequential values for each insert. The process is:

Transaction obtains the auto‑increment lock.

It reads the current counter, e.g. SELECT MAX(auto_inc_col) FROM t FOR UPDATE; The counter is incremented and assigned to the new row.

The lock is released immediately after the statement finishes, making it faster than a transaction‑wide lock, but it remains a table‑level lock and can become a bottleneck under high concurrency.

InnoDB provides three lock‑mode options controlled by the innodb_autoinc_lock_mode system variable:

Mode 0 (Traditional) : Every insert acquires a table‑level auto‑increment lock that is released after the statement. Guarantees consecutive IDs within a single INSERT and across concurrent inserts.

Mode 1 (Consecutive) : Simple and mixed‑mode inserts use an in‑memory mutex; bulk inserts still use a table‑level lock. If a transaction already holds a table‑level lock, subsequent simple inserts must wait.

Mode 2 (Interleaved) : No table‑level lock for any insert type. Multiple statements can run concurrently, improving performance in high‑throughput scenarios, but gaps (holes) may appear in the sequence.

MySQL 5.1.22 introduced a lightweight in‑memory mutex for auto‑increment handling, reducing lock duration. The default mode changed from 1 to 2 in MySQL 8.0.

Insert Types and Their Interaction with Auto‑Increment Lock

Simple inserts : Fixed number of rows, e.g. INSERT INTO t VALUES (...), (...) (excluding INSERT … ON DUPLICATE KEY UPDATE).

Bulk inserts : Row count not known upfront, e.g. INSERT … SELECT or REPLACE … SELECT.

Mixed‑mode inserts : Some rows specify explicit IDs while others rely on auto‑increment, e.g.

INSERT INTO t (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d')

or INSERT … ON DUPLICATE KEY UPDATE.

Gaps can arise in mode 2 or when using mixed‑mode inserts because the mutex may allocate more IDs than are actually used. Example:

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

In this statement, the auto‑increment lock (or mutex) may reserve IDs 2 and 3 for the NULL placeholders, but if the transaction rolls back or the placeholders are not inserted, those IDs become gaps.

Conclusion

MySQL lock mechanisms include global locks, intention locks, and auto‑increment locks, each serving a specific purpose and having distinct performance characteristics. Understanding when and how these locks are acquired helps avoid unexpected contention and gaps in auto‑increment values. Further topics such as page locks, insert intention locks, and latch mechanisms are left for future discussion.