Understanding MySQL InnoDB Locks: Types, Isolation, and MVCC Explained

1. Locks

1.1 What is a lock

In the real world a lock is a closed device opened with a key or code. In computers a lock controls concurrent access to shared resources; examples include Java's Lock and synchronized, and database locks that manage concurrent access to data.

2. InnoDB

2.1 MySQL Architecture

MySQL consists of a connection‑pool component, management services and tools, SQL interface component, query parser, optimizer, buffer component, plugin storage engines, and physical files.

Running show engines \G; lists the available storage engines. The default engine can be checked with show variables like '%storage_engine%';, which shows InnoDB as the default.

Comparison of InnoDB and MyISAM:

Transactions: InnoDB supports, MyISAM does not.

Locks: InnoDB supports MVCC row locks, MyISAM uses table locks.

Foreign keys: InnoDB supports, MyISAM does not.

Storage space: InnoDB requires larger space due to caches; MyISAM is compressible.

Use case: InnoDB for workloads with updates/inserts; MyISAM for heavy selects.

2.2 Transaction Isolation

Isolation prevents dirty reads, non‑repeatable reads, and phantom reads.

Dirty read

A transaction reads uncommitted changes from another transaction, breaking isolation.

Non‑repeatable read

The same transaction reads the same row twice and gets different results because another transaction committed an update in between.

Phantom read

A transaction reads a set of rows, another transaction inserts a new matching row, and the first transaction's subsequent read sees the new row.

Isolation levels summary:

Read Uncommitted: dirty read NO, non‑repeatable NO, phantom NO.

Read Committed: dirty read YES, non‑repeatable NO, phantom NO.

Repeatable Read: dirty read YES, non‑repeatable YES, phantom NO.

Serializable: all YES.

2.3 InnoDB Lock Types

S or X locks

S (shared) lock – read lock; other transactions can also acquire shared locks but not exclusive locks.

X (exclusive) lock – write lock; blocks all other lock requests on the row.

Compatibility means a lock can be granted simultaneously; conflict means it cannot.

Intention locks

Intention shared lock – indicates a transaction intends to acquire shared row locks on a table.

Intention exclusive lock – indicates a transaction intends to acquire exclusive row locks on a table.

They allow the storage engine to check table‑level intent without scanning every row.

Auto‑increment lock

Released after the SQL statement finishes, not after the transaction.

Large INSERT ... SELECT operations can be slowed because the lock blocks other transactions.

The auto‑increment algorithm is configurable.

Current mode can be queried with show variables like 'innodb_autoinc_lock_mode';, which returns 0 (traditional), 1 (consecutive), or 2 (interleaved).

2.4 InnoDB Lock Algorithms

Record lock

Locks index records, not the actual data rows. If a non‑primary key index is used, InnoDB locks the index entry and then the primary‑key row. If no index exists, a hidden primary key is used; otherwise a full‑table lock is applied.

Gap lock

Locks a range between index records (the “gap”) to prevent other transactions from inserting into that range, thereby preventing phantom reads.

Next‑key lock

Combines a record lock with a gap lock. In Repeatable Read (default) InnoDB uses next‑key locks for range scans, but degrades to a pure record lock when a unique index is used.

Insert intention lock

Set by INSERT operations before acquiring the exclusive lock on the new row. It allows multiple transactions to insert into different gaps of the same index without waiting for each other.

2.5 MVCC (Multi‑Version Concurrency Control)

InnoDB adds two hidden columns to each row to store the creation and deletion version numbers. This enables non‑blocking snapshot reads and current reads.

Snapshot read: Reads a consistent view of committed data without acquiring locks.

Current read: Requires a lock (e.g., SELECT ... FOR UPDATE, UPDATE, INSERT, DELETE).

In Repeatable Read, the snapshot is taken at the time of the first SELECT in the transaction; in Read Committed, a new snapshot is taken for each SELECT.