Understanding MySQL Locks: Which Queries Actually Acquire Locks?

Interview Question Overview

The article starts with a common interview question about whether three simple SELECT statements on an InnoDB table acquire locks and, if so, what type of locks they are.

1. Types of MySQL Locks

MySQL defines at least twelve lock types, which can be grouped by granularity into global, table, and row locks. The most relevant for developers are:

Global lock : locks the entire instance (e.g., FLUSH TABLES WITH READ LOCK), released with UNLOCK TABLES.

Table lock : explicit shared (S) or exclusive (X) locks via LOCK TABLES … READ/WRITE, released automatically on connection loss.

Metadata lock (MDL) : protects schema changes; shared read/write for DML, exclusive for DDL.

Intention locks (IS/IX) : table‑level locks maintained by the storage engine to coordinate row‑level locking.

Row locks : include record locks, gap locks, next‑key locks, and insert‑intention locks, each with shared (S) or exclusive (X) modes.

2. Lock Compatibility

After introducing intention locks, the compatibility matrix shows that intention locks are compatible with each other, while X and IX locks conflict with most other locks.

3. Viewing Lock Information

For MySQL 5.6 and earlier, create the innodb_lock_monitor table and run SHOW ENGINE INNODB STATUS. From MySQL 5.6.16 onward, enable innodb_status_output and innodb_status_output_locks to get periodic lock reports, or query performance_schema.data_locks (MySQL 8.0) for real‑time lock details.

4. Test Environment Setup

A test table t is created with a primary key id, a unique key a, and a non‑unique key b. Sample data is inserted:

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` varchar(10),
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

INSERT INTO t VALUES
  (1,10,100,'a'),
  (3,30,300,'c'),
  (5,50,500,'e');

5. Locking When the Record Exists

Two factors decide the lock type: the current transaction isolation level and whether the target row exists. The article summarizes lock behavior for the three statements under READ‑UNCOMMITTED, READ‑COMMITTED, REPEATABLE‑READ, and SERIALIZABLE isolation levels, showing when metadata locks, intention locks, and row locks are taken.

6. Locking When the Record Does Not Exist

When the row is absent, READ‑UNCOMMITTED and READ‑COMMITTED acquire only intention locks. REPEATABLE‑READ and SERIALIZABLE acquire gap locks to prevent phantom reads, locking the interval surrounding the missing key.

7. Primary‑Key Range Reads

In REPEATABLE‑READ, a SELECT … FOR UPDATE on a primary‑key range creates three X locks covering the ranges (1,3], (3,5], and (5,+∞]. The article explains the principles behind index‑page locking, lock escalation, and next‑key lock intervals.

8. Unique Index Equality Queries

For a unique index, a SELECT … FOR UPDATE on a=30 results in a record lock on the matching row and an additional lock on the primary key row, because the next‑key lock collapses to a simple row lock.

9. Non‑Unique Index Equality Queries

When querying a non‑unique index b, the lock type depends on isolation level. In REPEATABLE‑READ, the next‑key lock degrades to a gap lock after the last matching entry; in READ‑COMMITTED, only row locks are taken.

10. Covering Index Locks

A covering index (e.g., b includes id) allows SELECT id FROM t WHERE b=300 LOCK IN SHARE MODE to lock only the secondary index with shared and intention‑shared locks, avoiding any lock on the primary key.

11. Queries Without an Index

Full‑table scans without an index cause the entire table to be locked in intervals (-∞,1], (1,3], (3,5], (5,+supremum], effectively locking the whole table and should be avoided in real‑time workloads.

12. Visual Aids

Throughout the article, diagrams illustrate lock hierarchies, compatibility matrices, and lock ranges for each scenario.