Understanding MySQL Lock Mechanisms and Their Behavior Across Isolation Levels

This article provides a comprehensive analysis of MySQL locking mechanisms, motivated by a Tencent interview question that asks which locks are taken by three simple SELECT statements on an InnoDB table with a primary key.

Types of locks : MySQL uses several lock categories – global locks (e.g., FLUSH TABLES WITH READ LOCK ), table locks (explicit LOCK TABLES ... READ/WRITE ), metadata locks (MDL, introduced in 5.5 to protect schema changes), intention locks (IS, IX) that indicate a transaction’s intent to acquire row‑level locks, and row‑level locks including record locks, gap locks, next‑key locks, and insert‑intention locks.

Compatibility : A compatibility matrix shows that intention locks (IS/IX) are compatible with each other, while exclusive locks (X, IX) are not. This matrix is essential for understanding lock conflicts.

Viewing lock information : For MySQL versions before 5.6.16, a special table innodb_lock_monitor is created and SHOW ENGINE INNODB STATUS is used. From 5.6.16 onward, setting innodb_status_output=ON and innodb_status_output_locks=ON enables lock output. In MySQL 5.7+ the information_schema.innodb_locks view shows blocked locks, while MySQL 8.0 provides performance_schema.data_locks , performance_schema.metadata_locks , and performance_schema.table_handles for full lock visibility.

Test environment setup :

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');

SET GLOBAL transaction_isolation='READ-UNCOMMITTED';
SET GLOBAL transaction_isolation='READ-COMMITTED';
SET GLOBAL transaction_isolation='REPEATABLE-READ';
SET GLOBAL transaction_isolation='SERIALIZABLE';

Lock behavior when the target row exists : The three statements – SELECT * FROM t WHERE id=X , BEGIN; SELECT * FROM t WHERE id=X , and BEGIN; SELECT * FROM t WHERE id=X FOR UPDATE – acquire different locks depending on the isolation level. In auto‑commit mode (first statement) no locks are taken. In a transaction (second statement) a shared metadata lock (SHARED_READ) is acquired. The FOR UPDATE statement acquires a shared metadata write lock (SHARED_WRITE), an intention exclusive lock (IX), and a record lock (X or REC_NOT_GAP) on the existing row. The article provides a detailed table summarizing these locks for RU, RC, RR, and SERIAL isolation levels.

Lock behavior when the target row does not exist : For non‑existent rows, RU and RC isolation levels only acquire intention locks (IS/IX) without row locks. RR and SERIAL levels add gap locks to prevent phantom reads, e.g., X,GAP on the interval where the row would appear.

Primary‑key range scans : A SELECT * FROM t WHERE id>1 AND id<7 FOR UPDATE under REPEATABLE‑READ creates next‑key locks on the intervals (1,3], (3,5], and (5,+∞], effectively locking three index ranges.

Unique index equality query : SELECT * FROM t WHERE a=30 FOR UPDATE under RR acquires two row locks – one on the unique secondary index entry (a=30) and one on the corresponding primary key record – because next‑key locks on a unique index degenerate to record locks.

Non‑unique index equality query : Under RR, a query like SELECT * FROM t WHERE b=300 FOR UPDATE locks a next‑key interval that includes the matching row and the gap to the next index value, resulting in a combination of row and gap locks. Under RC only the matching row lock is taken; when the value does not exist (e.g., b=400 ) only intention exclusive locks are present.

Covering index query : SELECT id FROM t WHERE b=300 LOCK IN SHARE MODE uses the secondary index b as a covering index, so only a shared lock (S) and a shared intention lock (IS) are placed on that index, with no lock on the primary key.

Queries without any index : A full‑table scan such as SELECT * FROM t WHERE c='aa' FOR UPDATE locks the entire table range, producing interval locks like (-∞,1], (1,3], (3,5], (5,+supremum]. This demonstrates why non‑indexed queries should be avoided in high‑concurrency workloads.

Summary : Understanding the hierarchy of MySQL locks, their compatibility, and how isolation levels and row existence affect lock acquisition is crucial for preventing deadlocks, phantom reads, and performance bottlenecks in InnoDB‑based applications.