Mastering MySQL InnoDB Locks: From Shared to Optimistic

1 Review

Previously we covered high‑concurrency control in InnoDB and the InnoDB lock mechanism.

In MySQL's InnoDB storage engine, the following lock types can be used to protect concurrent operations:

Shared Lock (S lock) : Allows multiple transactions to read the same row simultaneously but blocks any transaction that tries to acquire an exclusive lock on that row. It enables read‑read parallelism.

Exclusive Lock (X lock) : Also called a write lock; a transaction holds it exclusively on a row, preventing other transactions from reading or writing that row. It enforces write‑read and write‑write blocking.

Intent Lock : A low‑level lock that signals a transaction's intention to acquire shared or exclusive locks on a range of rows, helping the engine avoid conflicting range‑level modifications.

Pessimistic Lock : Assumes the worst case and acquires an exclusive lock before modifying data, ensuring no other transaction can modify the same row concurrently.

Optimistic Lock : Assumes conflicts are rare; it does not lock rows during updates but checks a version number at commit time, rolling back if a conflict is detected. Suitable for read‑heavy, write‑light workloads.

# Lock statement
SELECT ... FOR SHARE

# Lock statement
SELECT ... FOR UPDATE

2 DML and SELECT Lock Usage

2.1 Ordinary SELECT

Ordinary SELECT queries are fast and support concurrent execution because they use snapshot reads. This behavior is valid under the Read Uncommitted, Read Committed, and Repeatable Read isolation levels.

Under the highest isolation level, Serializable, an ordinary SELECT is upgraded to SELECT ... IN SHARE MODE.

2.2 SELECT with LOCK

Locking SELECT statements use exclusive (X) locks. The main scenarios are:

# Exclusive scenario
SELECT ... FOR UPDATE

# Shared scenario
SELECT ... FOR SHARE MODE

When the query uses a primary key or a unique index with a unique search condition, InnoDB acquires a record lock (row lock). If the condition specifies a range, InnoDB uses gap locks and next‑key locks to protect the interval.

2.2.1 Record Lock

Example:

# Table definition
table (Id PK, Name, Company);

# Sample rows
5, Gates, Microsoft
7, Bezos, Amazon
11, Jobs, Apple
14, Ellison, Oracle

Query: SELECT * FROM table WHERE id=5 FOR UPDATE; The lock is placed on the index record with id=5, preventing other transactions from inserting, updating, or deleting that row.

2.2.2 Gap/Next‑Key Lock

For range queries, InnoDB locks the scanned index interval using gap and next‑key locks to prevent inserts into the gap.

SELECT * FROM table WHERE id BETWEEN 7 AND 13 FOR UPDATE;

This locks the interval so that no other transaction can insert, modify, or delete rows with ids 8‑12.

2.3 UPDATE / DELETE

When a unique index is used with a unique search condition, a record lock (row lock) is acquired, similar to a locking SELECT.

For range conditions, InnoDB acquires an exclusive next‑key lock on the matching index range, preventing other transactions from inserting, updating, or deleting rows within that interval.

If the UPDATE targets a clustered index record, the corresponding secondary index records are implicitly locked as well.

Example of a row‑level lock:

# Only locks the row with id=5
UPDATE table SET name='Brand' WHERE id=5;

InnoDB performs a primary‑key lookup first; using a secondary index requires an additional lookup step, which is slower.

2.4 INSERT

INSERT acquires an exclusive lock on the newly inserted index record but does not lock the preceding range. An insert intention lock is set on the interval, yet it does not block other transactions from inserting different keys into the same range.

3 Summary

This article introduced the various lock types in MySQL's InnoDB engine and explained which SQL statements trigger each lock. Understanding these mechanisms helps analyze concurrency, mutual exclusion, and potential deadlocks across multiple transactions.