How Does MySQL Lock Rows for DELETE? A Deep Dive into MVCC and Isolation Levels

Related Knowledge Introduction

Multi-Version Concurrency Control

In MySQL's default InnoDB storage engine, concurrency control is based on MVCC (Multi-Version Concurrency Control), which allows reads without locking and eliminates read‑write conflicts, greatly improving performance for OLTP workloads. MVCC can be summarized as storing multiple temporary versions of the same data to achieve concurrency control.

Current Read and Snapshot Read

MVCC distinguishes two types of reads:

Snapshot Read

(Simple SELECT) reads the visible version of a record (which may be a historical version) without acquiring a lock.

Current Read

(Special SELECT, INSERT, DELETE, UPDATE) reads the latest version of a record and acquires a lock on the returned rows to prevent concurrent modifications.

Clustered Index

In InnoDB, the table data is organized as a clustered index: the full record is stored in the primary key index, and all column values can be retrieved via the primary key.

Leftmost Prefix Principle

For composite and prefix indexes, MySQL matches conditions from left to right until a range condition (>, <, BETWEEN, LIKE) appears. Index order matters for range queries.

Two-Phase Lock (2PL)

InnoDB follows the two‑phase locking protocol: a lock acquisition phase followed by a lock release phase after the transaction commits, ensuring that all modifications are locked before commit.

Isolation Levels

MySQL/InnoDB defines four isolation levels:

Read Uncommitted : can read uncommitted rows; rarely used.

Read Committed (RC) : locks rows for current reads; may suffer phantom reads.

Repeatable Read (RR) : locks rows and gaps to prevent phantom reads.

Serializable : degrades MVCC to lock‑based concurrency; all reads acquire locks.

Gap Lock and Next‑Key Lock

In RR, InnoDB adds gap locks (locking the space between index records) and next‑key locks (record lock + preceding gap lock) to prevent phantom inserts between existing rows.

Analysis of DELETE Statement Locking

# table T (id int, name varchar(20))
delete from T where id = 10;

select * from T where id = 10;

To answer how MySQL locks the DELETE statement, we must consider several prerequisites:

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 it has a secondary index, is it unique?

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

Combination 1: Primary Key + RC

The simplest case: MySQL adds an exclusive (X) lock on the row where id = 10 in the primary key index.

Conclusion: When id is the primary key, only the matching row receives an X lock.

Combination 2: Unique Secondary Index + RC

When id is a unique secondary index, MySQL first locks the matching entry in the secondary index, then follows the pointer to the clustered primary key row and locks that as well.

Conclusion: Two X locks are required: one on the unique secondary index entry and one on the corresponding primary key row.

Combination 3: Non‑Unique Secondary 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

Without an index, MySQL performs a full table scan using the clustered index. It initially locks all rows, then unlocks those that do not satisfy the condition (semi‑consistent read).

Conclusion: All rows are examined; matching rows stay locked, non‑matching rows are unlocked during scanning.

Combination 5: Primary Key + RR

The locking process is identical to Combination 1.

Combination 6: Unique Secondary Index + RR

The locking process is identical to Combination 2.

Combination 7: Non‑Unique Secondary Index + RR

RR adds gap locks to the non‑unique index scan, preventing phantom rows from being inserted between existing keys.

Conclusion: X locks on matching rows plus gap locks on surrounding intervals ensure repeatable reads without phantoms.

Combination 8: No Index + RR

Full table scan with X locks on every row and gap locks on all gaps, effectively locking the whole table.

Conclusion: In RR, a full‑table current read locks every record and all gaps, which can cause a table‑wide lock; semi‑consistent read can mitigate the cost.

Combination 9: Serializable

All reads become current reads and acquire locks; MVCC degrades to lock‑based concurrency control.

Conclusion: In Serializable isolation, every operation, including simple SELECTs, acquires locks, eliminating lock‑free reads.

Final Remarks

A simple DELETE statement can involve various locking strategies depending on index presence and isolation level. Understanding MVCC, gap locks, and isolation levels is essential for performance tuning and avoiding deadlocks in MySQL/InnoDB.