Understanding MySQL InnoDB Locking Mechanisms and Transaction Isolation Levels

Preface

Databases use locking to preserve transaction properties such as consistency and isolation, but excessive locking harms concurrency. This article analyses MySQL InnoDB's locking mechanisms to help readers understand what the database does during transaction processing.

One‑Lock vs Two‑Phase Locking

Because of high concurrency, many applications adopt a "one‑lock" approach—locking all required data at the start of a method to avoid deadlocks. This does not work for databases, which instead follow the two‑phase locking (2PL) protocol, dividing a transaction into a locking phase and an unlocking phase.

Locking phase: Before reading data, a shared (S) lock is required; before writing, an exclusive (X) lock is required. If locking fails, the transaction waits.

Unlocking phase: After the transaction releases a lock, it may only unlock and cannot acquire new locks.

Locking Methods in Transactions

Four Isolation Levels

Isolation levels define how concurrent reads are handled. The article lists:

Read Uncommitted : Allows dirty reads.

Read Committed : Only reads committed data.

Repeatable Read (default in InnoDB): Guarantees that repeated reads within the same transaction see the same data, though phantom reads may still occur.

Serializable : Enforces full serializability using shared and exclusive locks.

Lock Types in MySQL

MySQL provides table locks, row locks, and metadata locks. Row locks are preferred for high concurrency.

Read Committed Example

MySQL> show create table class_teacher \G
Table: class_teacher
Create Table: CREATE TABLE `class_teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_teacher_id` (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

To use RC level, set the session isolation and binlog format:

SET session transaction isolation level read committed;
SET SESSION binlog_format = 'ROW';

If an indexed column is used in a WHERE clause, MySQL can lock only matching rows; without an index, it may lock the whole table.

Repeatable Read (RR)

RR is InnoDB's default. It provides snapshot reads for SELECT statements, allowing consistent reads without locking, while write operations still acquire locks.

Phantom vs Non‑Repeatable Reads

Non‑repeatable reads involve changes to existing rows (UPDATE/DELETE), while phantom reads involve newly inserted rows (INSERT). Serializable isolation prevents both by using shared and exclusive locks.

Pessimistic vs Optimistic Locks

Pessimistic locks rely on database‑level locking to ensure exclusive access, whereas optimistic locks use version numbers (MVCC) to detect conflicts.

MVCC in InnoDB

Each row stores a creation and deletion version. SELECT reads rows whose creation version ≤ current transaction version and whose deletion version is either NULL or > current version. INSERT, DELETE, and UPDATE manipulate these version fields accordingly.

Current Read vs Snapshot Read

Snapshot read (SELECT) does not acquire locks; current read (SELECT … FOR UPDATE, INSERT, UPDATE, DELETE) does acquire locks.

Next‑Key Lock

InnoDB combines row locks with gap locks (Next‑Key lock) to prevent phantom inserts in RR. Gap locks protect index intervals; when an indexed column is updated, both the affected rows and the surrounding gaps are locked.

When no index is available, MySQL may lock the entire table, and gap locks cannot be released until the transaction commits.

Serializable

At this level, reads acquire shared locks and writes acquire exclusive locks, ensuring full serializability but greatly reducing concurrency.

References

MySQL Reference Manual

High Performance MySQL (3rd Edition)