Inside MySQL 8.0: How Table, Page, and Row Locks Manage Concurrency

Introduction

When learning databases, a common question is why database systems outperform file systems, especially regarding concurrent access control. Using MySQL 8.0.35 code as an example, this article explains MySQL's overall concurrency control.

Overall Overview

MySQL follows a storage‑compute separation architecture: the MySQL Server is the compute layer and the Storage Engine is the storage layer. Concurrency control therefore occurs both in the Server layer and the Engine layer. Modern MySQL primarily uses the InnoDB engine.

Table‑Level Concurrency Control

Table‑level control includes:

Server‑layer and Engine‑layer table locks.

Page‑level index and page locks.

Row‑level locks.

These three parts are discussed separately.

Does DDL Lock the Table?

Early MySQL versions (≤5.6) blocked DML during DDL, causing long‑running schema changes to halt business writes. Since 5.6, MySQL supports Online DDL; 5.7 expanded its scope, and 8.0 introduced Instant DDL, which generally avoids business impact.

CREATE TABLE `t1` (</code>
<code>  `id` int NOT NULL,</code>
<code>  `c1` int DEFAULT NULL,</code>
<code>  PRIMARY KEY (`id`)</code>
<code>) ENGINE=InnoDB;</code>
<code>INSERT INTO t1 VALUES (1,10);</code>
<code>INSERT INTO t1 VALUES (2,20);</code>
<code>INSERT INTO t1 VALUES (3,30);

In this example, session 1 runs a slow query, session 2 attempts a DDL and is blocked, and session 3’s query is also blocked, illustrating the “lock table” effect.

Metadata locks (MDL) are the core mechanism. The lock acquisition path is open_and_process_table, which eventually calls thd->mdl_context.acquire_lock.

|--> open_and_process_table</code>
<code>|   |--> open_table</code>
<code>|   |   |--> mdl_request.is_write_lock_request</code>
<code>|   |   |--> thd->mdl_context.acquire_lock // request global MDL lock</code>
<code>|   |--> open_table_get_mdl_lock</code>
<code>|       |--> thd->mdl_context.acquire_lock // request table MDL lock

Server‑Layer Table Locks

The Server layer uses lock_tables as the entry point:

|--> lock_tables</code>
<code>|   |--> mysql_lock_tables</code>
<code>|   |   |--> lock_tables_check // decide if lock needed</code>
<code>|   |   |--> get_lock_data // init MYSQL_LOCK structures</code>
<code>|   |   |--> lock_external // call engine handler</code>
<code>|   |   |--> thr_multi_lock // sort and apply locks</code>
<code>|   |   |--> thr_lock // lock or wait

InnoDB’s lock_count returns 0, meaning no additional Server‑layer lock is required; the Engine handles locking via external_lock. Non‑InnoDB engines (e.g., CSV) rely on Server‑layer locks.

Page‑Level Concurrency Control

InnoDB stores data in B+Tree structures. A typical three‑level B+Tree consists of a root node, intermediate non‑leaf nodes, and leaf nodes (pages). Each node corresponds to a 16 KB page.

Page‑level locking works during B+Tree traversal. For a read, an S lock is taken on the index, root, intermediate nodes, and leaf. After reading, the index and non‑leaf locks are released.

|--> row_ins_clust_index_entry</code>
<code>|   |--> row_ins_clust_index_entry_low(..., BTR_MODIFY_LEAF, ...)</code>
<code>|   |   |--> mtr_s_lock(dict_index_get_lock(index), ... ) // index S lock</code>
<code>|   |   |--> btr_cur_latch_for_root_leaf</code>
<code>|   |   |--> buf_page_get_gen(..., rw_latch, ... ) // page lock

For optimistic (leaf‑only) updates, only the leaf node receives an X lock. For pessimistic (Structure Modify Operation, SMO) updates, the index and root acquire SX locks, and any potentially modified non‑leaf nodes receive X locks.

|--> row_ins_clust_index_entry_low(..., BTR_MODIFY_TREE, ...)</code>
<code>|   |--> mtr_sx_lock(dict_index_get_lock(index), ... ) // index SX lock</code>
<code>|   |--> lock_table(..., LOCK_X) // leaf X lock

Row‑Level Concurrency Control

Row locks in InnoDB are not limited to a single record. Types include:

Record lock (Rec Lock) – LOCK_REC_NOT_GAP, S or X.

Gap lock – LOCK_GAP, protects gaps between records.

Next‑Key lock – combination of record and gap lock (LOCK_ORDINARY).

Insert Intention lock – LOCK_INSERT_INTENTION, created when a transaction wants to insert into a gap already locked by another transaction.

Example of a deadlock caused by primary‑key inserts under READ‑COMMITTED:

CREATE TABLE `t1` (</code>
<code>  `id` int NOT NULL,</code>
<code>  `c1` int DEFAULT NULL,</code>
<code>  PRIMARY KEY (`id`)</code>
<code>) ENGINE=InnoDB;</code>
<code>INSERT INTO t1 VALUES (1,10);</code>
<code>INSERT INTO t1 VALUES (2,20);</code>
<code>INSERT INTO t1 VALUES (3,30);

Session 1 inserts a row and does not commit. Session 2 and Session 3 attempt the same insert and block. After Session 1 rolls back, both Session 2 and Session 3 wait on insert‑intention locks, leading to a deadlock.

The rollback converts the waiting Rec locks into Gap locks ( lock_rec_inherit_to_gap), causing each transaction to see the other's Gap lock and generate an insert‑intention lock, which forms a circular wait.

Typical Deadlock Scenarios

Four representative cases are described, each illustrating how MDL, gap, and insert‑intention locks interact to produce deadlocks. The analysis shows that even simple primary‑key inserts can create complex lock dependencies.

Deadlock Diagnosis

MySQL 8.0 enables deadlock detection by default ( innodb_deadlock_detect). When a deadlock occurs, the error log prints the wait‑for graph. If performance_schema is enabled, the data_locks table can be queried to view lock wait relationships. Administrators can kill involved sessions or adjust transaction logic to reduce lock hold time.

Summary

MySQL’s concurrency control operates at three levels:

Table‑level locks protect schema changes; MDL locks handle most cases, while non‑InnoDB engines still need Server‑layer locks.

Page‑level locks safeguard B+Tree structure; S, SX, and X locks are used depending on optimistic or pessimistic updates.

Row‑level locks ensure record consistency; they include record, gap, next‑key, and insert‑intention locks, and are created on demand.

All locks are released at transaction commit, so keeping transactions short helps avoid deadlocks.