How MySQL Locks DELETE Rows: MVCC, Isolation Levels, and Index Types Explained

MySQL's locking behavior during the execution of DELETE and SELECT statements is explored, focusing on how InnoDB uses Multi-Version Concurrency Control (MVCC) and different isolation levels.

# table T (id int, name varchar(20))
DELETE FROM T WHERE id = 10;

The question is how MySQL locks rows when this statement runs. SELECT * FROM T WHERE id = 10; To answer, several key concepts are introduced.

Related Knowledge Introduction

Multi-Version Concurrency Control (MVCC)

InnoDB implements MVCC, allowing reads without locks and eliminating read‑write conflicts in OLTP workloads. MVCC keeps multiple versions of a row to achieve concurrency control.

Current Read and Snapshot Read

MVCC defines two read types: snapshot read (simple SELECT, reads a visible version without locking) and current read (special SELECT, INSERT, DELETE, UPDATE, reads the latest version and acquires locks).

Clustered Index

In InnoDB, the primary key forms a clustered index where the full row is stored.

Leftmost Prefix Principle

For composite indexes, MySQL can use the index from left to right until a range condition appears.

Two‑Phase Locking (2PL)

Traditional RDBMS use 2PL: a lock phase followed by an unlock phase after commit.

Isolation Levels

MySQL defines four isolation levels: Read Uncommitted, Read Committed (RC), Repeatable Read (RR), and Serializable.

Gap Lock and Next‑Key Lock

InnoDB row locks consist of record lock, gap lock (locks the interval between index records), and next‑key lock (record lock + preceding gap lock).

Analysis of Different Scenarios

Before analyzing lock behavior, several prerequisites are needed:

Is the id column a primary key?

What is the current isolation level?

If id is not a primary key, does it have an index?

If id has a secondary index, is it unique?

What is the execution plan (index scan or full table scan)?

Based on these, nine combinations are considered.

Combination 1: Primary Key + RC

The row with id = 10 receives an X‑lock.

Conclusion: Only the matching primary‑key row is locked.

Combination 2: Unique Index + RC

The DELETE uses the unique secondary index to locate id = 10, locks that index entry, then follows the primary (clustered) index to lock the corresponding row.

Conclusion: Two X‑locks are acquired – one on the unique index entry and one on the clustered index row.

Combination 3: Non‑Unique Index + RC

All rows matching id = 10 in the secondary index are locked, and their corresponding primary‑key rows are also locked.

Conclusion: Every matching row and its primary‑key counterpart receive X‑locks.

Combination 4: No Index + RC

MySQL performs a full‑table scan via the clustered index. All rows are initially X‑locked; rows that do not satisfy the condition are unlocked during scanning.

Conclusion: Full‑table scans lock every row, but MySQL releases locks on non‑matching rows to improve efficiency.

Combination 5 & 6: Primary Key / Unique Index + RR

The locking process is identical to Combination 1 and 2 respectively.

Combination 7: Non‑Unique Index + RR

In addition to X‑locks on matching rows, a gap lock is added between index entries to prevent phantom inserts, eliminating phantom reads.

Conclusion: X‑locks on rows plus gap locks on surrounding intervals guarantee repeatable reads.

Combination 8: No Index + RR

A full‑table scan locks every row and every gap, effectively locking the whole table. MySQL can mitigate this with semi‑consistent reads under certain settings.

Conclusion: RR with full‑table scans leads to table‑wide X‑locks and gap locks, severely limiting concurrency.

Combination 9: Serializable

All operations, including SELECT, acquire locks; MVCC degrades to lock‑based concurrency control.

Conclusion: In Serializable isolation, every statement locks rows, eliminating MVCC's lock‑free reads.

Understanding these locking behaviors helps diagnose performance issues and design schemas that minimize unnecessary locking.