Master MySQL Locks: From Row to Intention Locks Explained with Real Examples

1. Row Lock

In the InnoDB storage engine, row‑level locks lock the rows accessed via index entries, offering the smallest granularity, lowest conflict probability, and highest concurrency. Two variants exist: shared (S) and exclusive (X) locks.

Shared lock (S)

Allows a transaction to read a row while preventing other transactions from acquiring exclusive locks on the same data. Example:

<code>BEGIN;
SELECT * FROM stock WHERE id = 8 FOR SHARE;</code>

Querying performance_schema.data_locks shows S,REC_NOT_GAP for id=8 , meaning a read lock on that row only.

Exclusive lock (X)

Allows a transaction to modify a row and blocks both shared and exclusive locks from other transactions. Example:

<code>BEGIN;
UPDATE stock SET num = 81 WHERE id = 8;</code>

Data_locks reports X,REC_NOT_GAP for id=8 , indicating an exclusive lock on that row.

FOR UPDATE

Adding FOR UPDATE to a SELECT creates an exclusive lock:

<code>BEGIN;
SELECT * FROM stock WHERE id = 8 FOR UPDATE;</code>

Data_locks shows the same X lock information.

2. Record Lock

A record lock protects an existing row. Using the same shared‑lock example on id=8 demonstrates the lock state in data_locks with a visual result.

3. Gap Lock

Gap locks protect the interval between index entries. When querying a non‑existent id=5 with FOR SHARE , InnoDB creates a gap lock covering the range 1‑8, preventing inserts into that interval while allowing modifications of existing rows.

4. Next‑Key (or “临键”) Lock

Combines a record lock and a gap lock. For a range query WHERE id > 5 AND id < 14 FOR SHARE , InnoDB locks the existing row id=8 (S) and the gap before it (1‑8). The row id=14 is only gap‑locked.

5. Intention Locks

Intention locks are a coarse‑grained mechanism that signals a transaction’s intent to acquire row‑level locks, avoiding full‑table scans. Two types exist: intention shared (IS) and intention exclusive (IX). They are compatible with each other but conflict with exclusive (X) locks.

Example workflow:

<code>BEGIN;
SELECT * FROM stock WHERE id = 8 FOR SHARE;  -- transaction A obtains IS lock then S lock
BEGIN;
UPDATE stock SET num = 140 WHERE id = 14;      -- transaction B obtains IX lock then X lock</code>

Because IS and IX are compatible, both transactions can acquire their table‑level intention locks, but the X lock will wait if a conflicting row‑level S lock exists.

Compatibility tables (illustrated in the original images) summarize which lock types can coexist.