Understanding MySQL InnoDB Locks: Shared, Exclusive, Intention, Gap, Next‑Key, Auto‑Inc, and Predicate Locks
The article presents a detailed interview‑style walkthrough of MySQL InnoDB locking mechanisms, covering table‑ versus row‑level locks, shared and exclusive locks, intention locks, gap and next‑key locks, auto‑increment locks, predicate locks, and related SQL statements, illustrated with tables and code examples.
The author recounts a second interview with the same senior interviewer, focusing on MySQL InnoDB locking concepts.
The interviewer asks about the difference between MyISAM and InnoDB locks. MyISAM only supports table locks, while InnoDB supports both table and row locks, allowing finer‑grained concurrency.
Both table and row locks can be of two types: shared (S) locks, which allow concurrent reads, and exclusive (X) locks, which block other transactions.
Conflict
S
X
S
No conflict
Conflict
X
Conflict
Conflict
Ordinary SELECT statements do not acquire locks, but SELECT ... LOCK IN SHARE MODE acquires an S lock and SELECT ... FOR UPDATE acquires an X lock.
InnoDB also provides table‑level intention locks (IS and IX) that act as markers indicating that the table contains S or X row locks, allowing the engine to decide quickly whether a table‑level lock can be granted without scanning all rows.
Conflict
S
X
IS
IX
S
No conflict
Conflict
No conflict
Conflict
X
Conflict
Conflict
Conflict
Conflict
IS
No conflict
Conflict
No conflict
No conflict
IX
Conflict
Conflict
No conflict
No conflict
Row‑level locks in InnoDB are of three kinds: record locks, gap locks, and next‑key locks (record + gap). Record locks protect existing index entries; gap locks protect the gaps between index records to prevent phantom rows; next‑key locks combine both, using a half‑open interval (prev, curr].
If a column lacks an index, a record lock cannot be applied directly, and the transaction may be blocked when trying to insert a duplicate value.
Gap locks do not conflict with each other because they merely prevent inserts into the same gap. They are active under the REPEATABLE READ isolation level and can be disabled by switching to READ COMMITTED.
Insert intention locks are a special kind of gap lock that indicate a transaction is waiting for a gap lock to be released; they do not block each other.
Auto‑increment locks (Auto‑Inc Locks) are table‑level locks used when inserting rows with an AUTO_INCREMENT column. Since MySQL 5.1.22, a mutex can replace the lock for better performance. The variable innodb_autoinc_lock_mode controls the behavior (0 = only Auto‑Inc Lock, 1 = default mixed mode, 2 = only mutex).
MyISAM does not have Auto‑Inc Locks because it already uses table locks for inserts.
Finally, InnoDB supports predicate locks for spatial indexes, where traditional next‑key locking is unsuitable. Predicate locks are placed on the minimum bounding rectangle (MBR) of spatial data to prevent concurrent modifications that would violate isolation.
The interview concludes with a promise to discuss buffer pools, change buffers, and doublewrite buffers in a future session.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
Wukong Talks Architecture
Explaining distributed systems and architecture through stories. Author of the "JVM Performance Tuning in Practice" column, open-source author of "Spring Cloud in Practice PassJava", and independently developed a PMP practice quiz mini-program.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.