Understanding MySQL Isolation Levels, Locks, and Gap Locking

All database operations follow logical rules; isolation levels were introduced to provide predictable results when concurrent transactions modify data.

Purpose of Isolation Levels – they balance concurrency access and data consistency.

Four Isolation Levels

READ UNCOMMITTED – can read uncommitted data.
READ COMMITTED – reads only committed data.
REPEATABLE READ – reads a consistent snapshot.
SERIALIZABLE – fully serial execution.

Understanding the composition of the full data range helps explain lock behavior. A data range may be fully filled, partially filled, or empty, affecting whether gap locks are needed.

Problems caused by uncontrolled concurrency

Lost updates – multiple transactions overwrite each other's changes.

Dirty reads – a transaction reads uncommitted changes.

Non‑repeatable reads – data changes between reads.

Phantom reads – new rows appear that match a previous query.

To achieve the desired results, MySQL uses locking mechanisms.

MySQL Isolation Level Implementation

Record Locks – lock individual index records.

Gap Locks – lock the gaps between index records.

Next‑Key Locks – a combination of record and gap locks.

By default, REPEATABLE READ locks both the data and the gaps; READ COMMITTED releases record locks after the statement.

Scenario Demonstrations

Scenario 1 – REPEATABLE READ with a unique index

START TRANSACTION;
UPDATE t SET a=11 WHERE a=3;   -- holds Record Lock on a=3
-- Session B tries to update the same row and waits for the lock
COMMIT;

When the range is partially filled, Next‑Key locks are also acquired, causing insert statements to wait.

Scenario 2 – READ COMMITTED with a unique index

START TRANSACTION;
UPDATE t SET a=11 WHERE a=3;   -- holds Record Lock on a=3
-- Session B waits for the same lock
COMMIT;

Because READ COMMITTED releases record locks after each statement, other sessions can proceed sooner.

Scenario 3 – REPEATABLE READ with a non‑unique index

START TRANSACTION;
UPDATE t SET e=7 WHERE b=2 AND e=4;   -- acquires Record Locks on multiple rows
-- Session B attempting to update the same non‑unique index must wait.
COMMIT;

Gap locks are also taken for the range (5,7), blocking inserts that would fill the gap.

Scenario 4 – READ COMMITTED with a non‑unique index

START TRANSACTION;
UPDATE t SET d=6 WHERE b>=5 AND b<=7;   -- only Record Locks, no Next‑Key Locks
-- Inserts succeed because no gap locks are held.
COMMIT;

Scenario 5 – REPEATABLE READ without an index

START TRANSACTION;
UPDATE t SET c=100 WHERE d=2;   -- full table scan, acquires Record and Gap Locks
-- Other sessions wait for the X lock on the affected rows.
COMMIT;

When the WHERE clause does not use an index, the engine locks the clustered index rows and the gaps between them.

Summary & Extensions

Unique indexes enforce uniqueness; violations cause errors.

Using a secondary index locks both the index entries and the corresponding clustered rows.

Row‑by‑row locking can lead to deadlocks.

In READ COMMITTED, gap locks are only used for foreign‑key and duplicate‑key checks.

In REPEATABLE READ, gap locks prevent phantom reads.

Lock acquisition occurs at the start of each SQL statement and is released at transaction end.

Optimizations such as lock escalation or using table‑level locks may be considered for non‑indexed scans.