MySQL Lock Types Explained: Row, Gap, Metadata and Deadlock Prevention

Background

When multiple operations modify the same data, dirty reads can occur, so MySQL relies on server and storage engine locking mechanisms. Locks are divided into shared (read) and exclusive (write) locks, and further into row‑level and table‑level locks.

Types of Row Locks

Next‑Key Lock: locks a range and the index record.

Gap Lock: locks a range but not the index record.

Record Lock: locks a single row.

Basic Locking Rules

Some principles never change: snapshot reads are lock‑free, UPDATE/DELETE/INSERT acquire exclusive locks, RC isolation has no gap locks, etc.

Common statements: SELECT ... – snapshot read, no lock. SELECT ... LOCK IN SHARE MODE – shared lock. SELECT ... FOR UPDATE – exclusive lock.

DML (INSERT, DELETE, UPDATE) – exclusive lock.

DDL (ALTER, CREATE) – table‑level lock and implicit commit.

Table locks: S/X locks, intention locks (IS/IX), auto‑increment lock.

Row‑lock analysis: row locks are placed on index entries, ultimately on the clustered index.

Lock conflicts: S vs S compatible, X vs X conflict, X vs S conflict.

Isolation‑level differences: under SERIALIZABLE a SELECT acquires a shared lock; RC has no gap locks; RR adds gap locks.

SQL Lock Analysis

Using a sample students table (primary key id, secondary unique index no, secondary non‑unique indexes name and age, no index on score), we examine several simple UPDATE statements.

Clustered index hit: UPDATE students SET score = 100 WHERE id = 15 – X lock on the row.

Clustered index miss: UPDATE students SET score = 100 WHERE id = 16 – RC: no lock; RR: X lock on the row plus a GAP/Next‑Key lock.

Secondary non‑unique index hit: UPDATE students SET score = 100 WHERE name = 'Tom' – RR adds a GAP lock, forming a Next‑Key lock.

Secondary non‑unique index miss: UPDATE students SET score = 100 WHERE name = 'John' – RR adds a GAP lock only.

No index: UPDATE students SET score = 100 WHERE score = 22 – full‑table scan; RC locks every row, RR locks every row plus GAP locks.

Range query on clustered index: UPDATE students SET score = 100 WHERE id <= 20 – Next‑Key lock covers matching rows and the gap up to the next row.

Range query on secondary index: UPDATE students SET score = 100 WHERE age <= 23 – similar Next‑Key behavior; only the first matching row receives the lock.

Images in the original article illustrate the lock distribution for each case.

Metadata Lock (MDL)

MDL is automatically acquired when a table is accessed. A read MDL is taken for DML, and a write MDL for DDL. Read MDLs are shared, allowing concurrent reads; write MDLs are exclusive and block other operations.

Example session sequence shows how a long‑running SELECT holds a read MDL, blocking a concurrent ALTER that needs a write MDL, which can cascade to block further reads.

Online DDL workflow: acquire write MDL, downgrade to read MDL, perform the DDL, upgrade to write MDL, release.

Deadlock Cases

Case 1: Two sessions update different rows (id 20 and id 30) in opposite order, causing a classic deadlock.

Case 2: One session updates rows with id < 30, another updates rows with age > 23. Different index lock orders lead to deadlock.

How to Avoid Deadlocks

Keep SELECT statements fast and split complex queries.

Create appropriate indexes to avoid full‑table row locking.

Use EXPLAIN to verify index usage.

Ensure consistent lock acquisition order, e.g., sort data before batch processing.

Break large transactions into smaller ones.

Avoid running multiple heavy write scripts simultaneously.

Set innodb_lock_wait_timeout to a sensible value.