MySQL Locking Mechanisms

Hierarchy of Locks

By Granularity

InnoDB supports fine-grained row-level locking, whereas MyISAM is restricted to table-level locking. This is why InnoDB scales vastly better in write-heavy environments.

By Intent/Mode

• Shared Lock (S): A "Read Lock." Multiple transactions can hold S-locks on the same row simultaneously.
• Exclusive Lock (X): A "Write Lock." Only one transaction can hold an X-lock. It blocks both S and other X locks.

-- Acquire a Shared Lock
SELECT * FROM users WHERE id=1 LOCK IN SHARE MODE;

-- Acquire an Exclusive Lock
SELECT * FROM users WHERE id=1 FOR UPDATE;

The Three Algorithms of InnoDB Row Locks

InnoDB doesn't just lock a row; it locks the Index Record.

1. Record Lock

Locks exactly one index record.

• Example: WHERE id = 5 locks only the slot for ID 5.

2. Gap Lock

Locks the "gap" between index records (the space where no data exists).

• Purpose: To prevent other transactions from inserting "Phantom" rows in that range.
• Example: If index has IDs 3 and 7, a Gap Lock on (3, 7) prevents anyone from inserting ID 4, 5, or 6.

3. Next-Key Lock (The Default)

A combination of a Record Lock and a Gap Lock. It locks a record AND the gap preceding it.

• InnoDB RR Level: Uses Next-Key locking by default to effectively eliminate the Phantom Read problem.
• Example: A Next-Key lock on 7 in the sequence {3, 7, 10} locks the range (3, 7].

Locking Logic (A Simplification)

• Exact match on Unique Index: Usually "downgrades" to a simple Record Lock.
• Exact match on Non-Unique Index: Adds Gap Locks around the record to prevent identical values from being inserted nearby.
• Range queries: Acquire Next-Key Locks on all traversed records and gaps.

Deep Technical Insights

Deadlock Detection

In a multi-user database, T1 might wait for T2, while T2 waits for T1. This is a Deadlock. InnoDB has a background monitor that builds a "Wait-for Graph." If it detects a cycle, it automatically kills the "cheapest" transaction (the one with the fewest changes) to break the cycle.

Intent Locks (IS / IX)

To prevent the overhead of checking 1 million rows before granting a Table Lock, InnoDB uses Intent Locks. Before a transaction locks a row, it must first acquire an Intent Lock at the table level. This acts as a flag: "Hey, someone is currently holding a row lock in this table." This allows the database to reject table-level lock requests instantly without scanning every row.