Understanding MySQL Table Locks, Row Locks, and Gap Locks

In MySQL, a solid grasp of table locks versus row locks is essential for interview preparation; this article offers a concise overview of the concepts, engine behavior, and practical usage.

MySQL primarily uses two storage engines: MyISAM, which does not support row‑level locking, and InnoDB, the default engine that supports both row and table locks.

MyISAM automatically acquires a read lock on all involved tables before a SELECT and a write lock before UPDATE , DELETE , or INSERT , so users rarely need to issue explicit LOCK TABLE statements.

Explicit locking can be done with LOCK IN SHARE MODE for a shared (read) lock and FOR UPDATE for an exclusive (write) lock. Example queries: SELECT math FROM zje WHERE math>60 LOCK IN SHARE MODE; SELECT math FROM zje WHERE math>60 FOR UPDATE;

Table‑level locks in MyISAM: a shared read lock blocks only writers, while an exclusive write lock blocks both readers and writers, making MyISAM unsuitable for write‑heavy tables.

Row‑level locks in InnoDB rely on indexes; without an index the operation falls back to a table lock. Transaction examples illustrate that two transactions locking different rows (by primary key) can proceed concurrently, whereas locking the same index causes blocking.

Using SELECT ... FOR UPDATE acquires an exclusive lock on the selected rows, preventing other transactions from updating them until the lock is released. Example: SELECT * FROM user WHERE id=1 FOR UPDATE;

Key points for row‑lock implementation: an index is required, two transactions cannot lock the same index simultaneously, and DML statements automatically acquire exclusive locks.

Gap locks: when a range condition is used, InnoDB locks the index gaps that satisfy the range, preventing other transactions from inserting into those gaps. Example demonstrates that an uncommitted update on a range blocks another transaction from modifying rows within that range.

Recommendations: always use indexes for data retrieval to avoid lock escalation, design indexes to narrow the lock scope, minimize gap‑lock scenarios, and keep transactions short to reduce lock duration and resource consumption.

Further reading: Step‑by‑step guide to MySQL indexes and locks , Understanding MySQL index structures , and other related articles.