How MySQL Locks DELETE and SELECT: MVCC, Isolation Levels & Lock Types

Background

When executing a simple DELETE FROM T WHERE id = 10 or SELECT * FROM T WHERE id = 10 statement, MySQL must decide which rows to lock and how, depending on the table's index structure and the current transaction isolation level.

Key Concepts

Multi‑Version Concurrency Control (MVCC) : InnoDB uses MVCC so that snapshot reads (simple SELECT) do not acquire locks, while current reads (SELECT with locking, INSERT, UPDATE, DELETE) do lock rows.

Current Read vs Snapshot Read : Snapshot reads retrieve a visible version of a row without locking; current reads lock the latest version of the row.

Clustered Index : InnoDB stores the full row in the primary‑key index; secondary indexes contain the primary‑key value.

Left‑most Prefix Principle : Composite indexes are used from the leftmost column until a range condition stops the match.

Two‑Phase Locking (2PL) : Locks are acquired during the execution phase and released only at COMMIT.

Isolation Levels :

Read Uncommitted – reads uncommitted rows (rarely used).

Read Committed (RC) – current reads lock rows; may suffer phantom reads.

Repeatable Read (RR) – adds gap locks to prevent phantom reads.

Serializable – degrades MVCC to lock‑based concurrency; all reads lock.

Lock Types :

Record Lock – locks a specific index record.

Gap Lock – locks the gap between index records.

Next‑Key Lock – combination of a record lock and the preceding gap lock.

Lock Analysis for Different Scenarios

Combination 1: Primary Key + RC

If id is the primary key, InnoDB acquires an exclusive (X) lock on the single matching row.

Conclusion : Only one X lock on the primary‑key row is needed.

Combination 2: Unique Secondary Index + RC

When id is a unique secondary index, InnoDB first locks the matching entry in the secondary index, then follows the primary‑key (clustered) entry and locks that row 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

If id has a non‑unique secondary index, all matching secondary‑index entries are locked, and each corresponding primary‑key row is also locked.

Conclusion : Every row satisfying the condition receives an X lock, as does its primary‑key entry.

Combination 4: No Index + RC

Without an index on id, InnoDB performs a full‑table scan using the clustered index. It initially locks every scanned row, then unlocks rows that do not satisfy the predicate (semi‑consistent read optimization).

Conclusion : All rows are locked during scanning, but non‑matching rows are unlocked early to reduce overhead.

Combination 5: Primary Key + RR

The locking behavior is identical to Combination 1 because the primary key guarantees a single matching row.

Conclusion : One X lock on the primary‑key row.

Combination 6: Unique Secondary Index + RR

Same as Combination 2; the unique index ensures only one matching row.

Conclusion : Two X locks (secondary unique index entry + primary‑key row).

Combination 7: Non‑Unique Secondary Index + RR

RR adds gap locks to the non‑unique index scan. After locking matching rows, InnoDB also locks the gaps between index entries to prevent phantom inserts.

Conclusion : X locks on matching rows plus gap locks on surrounding index gaps.

Combination 8: No Index + RR

A full‑table scan locks every row and every gap, effectively blocking all concurrent DML. MySQL can mitigate this with semi‑consistent read (enabled under RC or RR with innodb_locks_unsafe_for_binlog).

Conclusion : All rows and gaps are locked, leading to a table‑wide lock unless semi‑consistent read is used.

Combination 9: Serializable

Under Serializable, even simple SELECT statements acquire locks, effectively degrading MVCC to lock‑based concurrency control.

Conclusion : All operations lock rows; phantom reads are eliminated but concurrency suffers.

Takeaways

Locking behavior in MySQL depends heavily on index presence, index uniqueness, and the transaction isolation level. Primary‑key and unique‑index queries lock few rows, while non‑unique or missing indexes can cause extensive row and gap locking, especially under Repeatable Read or Serializable isolation.