Master MySQL Locks: Row, Table, and Gap Lock Basics, Pitfalls & Live Code

What: MySQL lock fundamentals

Locks act as resource tokens that MySQL uses to serialize concurrent modifications and prevent data corruption, overselling, or lost updates.

InnoDB provides three lock types that together cover virtually all production lock‑related incidents:

Table lock – locks the entire table; acquisition is cheap but concurrency drops to zero, causing global blocking.

Row lock – locks a single row; low overhead, high concurrency, essential for internet‑scale workloads.

Gap lock – exclusive to the REPEATABLE READ isolation level; prevents phantom reads but is a frequent source of deadlocks.

Key conclusion : High‑traffic services aim for precise row locks, avoid table locks, and control gap locks. Most lock timeouts, deadlocks, and blocked APIs stem from misuse of these mechanisms.

Why: Impact of lock mis‑use

Updating a single row can unintentionally lock the whole table, halting the entire site.

Normal updates may trigger gap locks, leading to unexpected deadlocks.

Lock loss allows concurrent overwrites, causing order‑status chaos and inventory oversell.

Long‑running transactions hold row locks, causing request pile‑up and service collapse.

Confusing read/write lock semantics can block read traffic unnecessarily.

Core value of locks : Preserve data safety and consistency while maximizing database concurrency, forming the foundation of high‑throughput architectures.

Where: Applicable and prohibited scenarios for each lock type

Table lock (MyISAM default, rarely used in InnoDB)

Use case : Low‑frequency full‑table updates, batch archiving, data migration, offline analytics.

Avoid : High‑concurrency single‑row updates such as orders, inventory, or payments.

Characteristics : Fast acquisition, no deadlocks, but concurrency is extremely poor; once locked, all reads and writes block.

Row lock (InnoDB default, core for internet services)

Use case : High‑concurrency single‑row updates like order status changes, inventory deduction, user profile updates.

Caution : Massive batch updates or updates without an effective index may degrade to a table lock.

Characteristics : Precise row locking, independent, very high concurrency; relies on effective indexes—without an index the lock degrades to a table lock.

Gap lock (exclusive to REPEATABLE READ)

Use case : Financial reconciliation, inventory phantom‑read prevention, batch data consistency checks.

Caution : Ordinary high‑concurrency updates; easy to cause deadlocks and lock waiting.

Characteristics : Locks index gaps, forbids new rows, solves phantom reads but expands lock scope.

How: Hands‑on walkthrough to reproduce lock phenomena

Preparation – manual transaction commit

Disable autocommit to control transaction boundaries and observe lock acquisition and release.

-- Disable autocommit
SET autocommit = 0;

Demo 1 – Precise row lock (normal high‑concurrency scenario)

Scenario : Updating a single row using an indexed column triggers an InnoDB row lock.

Session 1 (Transaction A) runs but does not commit:

-- user_id has an index, precise row lock
UPDATE user_order SET status = 1 WHERE user_id = 1001;

Session 2 (Transaction B) updates a different row – proceeds without blocking.

Session 2 updates the same row – blocks until Transaction A commits or rolls back.

Conclusion : Updating an indexed field acquires a row lock; different rows do not interfere, allowing maximum concurrency.

Demo 2 – Row‑to‑table lock degradation (top online pitfall)

Scenario : Updating without an index or with an ineffective index causes the row lock to degrade into a full‑table lock.

Session 1 runs an update on the status column that lacks a useful index:

UPDATE user_order SET status = 2 WHERE status = 0;

Session 2 attempts to modify any unrelated row and is completely blocked.

Conclusion : Index‑less or index‑ineffective updates force row locks to become table locks, crippling site‑wide concurrency – the primary cause of online stalls.

Demo 3 – Gap lock and phantom‑read reproduction

Under REPEATABLE READ, a range update triggers next‑key and gap locks, locking all existing rows in the range and the gaps between them, preventing new inserts.

Session 1 performs a range update without committing:

UPDATE account SET balance = balance - 100 WHERE id > 10;

Session 2 attempts to insert a new row with id > 10 and is blocked.

Interpretation : Gap locks lock both data and empty spaces, eliminating phantom reads but enlarging the lock footprint, which can cause unnecessary blocking.

Demo 4 – Standard read/write locks (shared vs exclusive)

Shared lock (S‑lock): multiple sessions can read simultaneously; writes are prohibited during the lock.

SELECT * FROM user_order WHERE order_no = 'xxx' LOCK IN SHARE MODE;

Exclusive lock (X‑lock): write lock, blocks all reads and writes; update statements acquire it automatically.

SELECT * FROM user_order WHERE order_no = 'xxx' FOR UPDATE;

Demo 5 – Classic deadlock scenario (fully reproducible)

Deadlock occurs when two transactions hold locks the other needs, creating a circular wait.

Transaction A locks row 1 then requests row 2.

Transaction B locks row 2 then requests row 1.

MySQL detects the deadlock and aborts one of the transactions.

Frequent online lock issues and remediation checklist

Missing or ineffective indexes cause lock degradation to table locks.

Range queries trigger gap locks that enlarge lock scope.

Overly long transactions keep locks held, piling up requests and causing timeouts.

Inconsistent lock acquisition order across tables/rows leads to deadlocks.