Master MySQL InnoDB Locks: Types, Compatibility, and Isolation Level Effects

01 MySQL Lock Types

MySQL provides at least twelve lock types, divided into three granularity levels: global, table, and row.

Global locks lock the entire database instance (e.g., FLUSH TABLES WITH READ LOCK ) and are released with UNLOCK TABLES . They are mainly used for logical backups or setting the instance to read‑only.

Table locks lock an entire table. They include explicit shared (S) and exclusive (X) locks ( LOCK TABLES … READ/WRITE ), metadata locks (MDL) introduced in MySQL 5.5 to protect schema changes, and intention locks (IS, IX) maintained by the storage engine.

Row locks are supported by InnoDB and include record locks, gap locks, next‑key locks, and insert‑intention locks. Row locks are applied to index pages; the clustered primary key index is also locked when rows are accessed.

02 Lock Compatibility

After introducing intention locks, the compatibility matrix between table locks is as follows (illustrated in the image below): intention locks are compatible with each other, while exclusive (X, IX) locks are not compatible with shared (S, IS) locks.

03 Viewing Lock Information

Before MySQL 5.6.16, create a special table innodb_lock_monitor and run SHOW ENGINE INNODB STATUS . From MySQL 5.6.16 onward, enable innodb_status_output and innodb_status_output_locks to have the engine output lock information every 15 seconds.

CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;</code>
<code>DROP TABLE innodb_lock_monitor;

SET GLOBAL innodb_status_output=ON;</code>
<code>SET GLOBAL innodb_status_output_locks=ON;

From MySQL 5.7 you can query information_schema.innodb_locks (shows only blocked transactions). From MySQL 8.0 use performance_schema.data_locks, performance_schema.table_handles, and performance_schema.metadata_locks for comprehensive lock visibility.

04 Test Environment Setup

4.1 Create Test Table

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;

4.2 Insert Test Data

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

05 Locking When Record Exists

The two factors that decide locking in InnoDB are the current transaction isolation level and whether the target record exists.

Assuming a record with id=3 exists, the following table summarizes lock behavior of three statements under the four isolation levels (READ‑UNCOMMITTED, READ‑COMMITTED, REPEATABLE‑READ, SERIALIZABLE).

Analysis

Switch isolation levels with:

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

With autocommit=1 and no explicit BEGIN, a plain SELECT acquires no metadata or row locks – it truly “reads without locking”.

When a transaction starts with BEGIN and executes SELECT … WHERE id=3, a shared metadata lock (SHARED_READ) is taken to prevent schema changes. In REPEATABLE‑READ and READ‑COMMITTED the statement uses MVCC snapshot reads and does not acquire row locks; in SERIALIZABLE it also acquires an intention shared table lock (IS) and a shared record lock (S). SELECT … WHERE id=3 FOR UPDATE acquires a shared‑write metadata lock (SHARED_WRITE). SELECT … WHERE id=3 OR UPDATE acquires an intention exclusive table lock (IX) and an exclusive record lock (X) in all isolation levels.

06 Locking When Record Does Not Exist

If the target record is absent, READ‑UNCOMMITTED and READ‑COMMITTED acquire only intention locks (no row locks). REPEATABLE‑READ and SERIALIZABLE acquire gap locks to prevent phantom reads.

07 Primary‑Key Range Reads

In REPEATABLE‑READ, a range query SELECT * FROM t WHERE id>1 AND id<7 FOR UPDATE acquires next‑key locks on the index pages, resulting in three X locks covering the intervals (1,3], (3,5], (5,+∞].

08 Unique Index Equality Queries

For a unique index a, the query SELECT * FROM t WHERE a=30 FOR UPDATE in REPEATABLE‑READ (and READ‑COMMITTED) locks two records: the matching row in the unique index and the corresponding primary‑key row.

09 Non‑Unique Index Equality Queries

For a non‑unique index b, SELECT * FROM t WHERE b=300 FOR UPDATE in REPEATABLE‑READ locks the index range ((b=100,id=1),(b=300,id=3)] and ((b=300,id=3),(b=500,id=5)] plus the primary‑key row id=3. In READ‑COMMITTED only the exact row lock and the intention exclusive lock are taken.

10 Covering Index Queries

When a secondary index contains all requested columns (e.g., SELECT id FROM t WHERE b=300 LOCK IN SHARE MODE), the primary‑key index is not locked. The statement acquires a shared lock (S) and a shared intention lock (IS) on the secondary index.

11 Queries Without Index

Full‑table scans without an index lock the entire table range: (-∞,1], (1,3], (3,5], (5,+supremum]. This demonstrates why non‑indexed queries should be avoided in high‑concurrency workloads.

12 Summary

The article provides a comprehensive view of MySQL InnoDB locking mechanisms, covering lock types, compatibility, inspection methods, and detailed lock behavior for various SQL statements across isolation levels, supported by practical test cases and visual diagrams.