Master MySQL Locks: Table, Row, Gap & Deadlock Explained with Real Tests

Introduction

A job candidate recently struggled with MySQL lock‑mechanism questions during a high‑traffic interview, prompting the author to compile a thorough reference on how MySQL ensures data consistency and transaction isolation under heavy concurrency.

Lock Types Overview

MySQL locks can be classified by granularity (table, page, row), by mode (shared/read vs. exclusive/write), and by philosophy (optimistic vs. pessimistic). Each classification is explained with its advantages and trade‑offs.

Table Locks

Largest granularity, low overhead, fast acquisition, but high contention. MyISAM supports table‑level shared read locks and exclusive write locks.

Row Locks

Smallest granularity, higher overhead, possible deadlocks, but low contention and high concurrency. InnoDB uses row locks by default.

Optimistic vs. Pessimistic Locks

Pessimistic locks rely on MySQL's built‑in mechanisms; optimistic locks are typically implemented by developers using version numbers or CAS operations.

MyISAM Lock Demonstrations

Explicit lock commands:

// Add a table‑level read lock
LOCK TABLE employee READ;
// Add a table‑level write lock
LOCK TABLE employee WRITE;
// Release locks (also released on COMMIT)
UNLOCK TABLES;

Creating a test table and inserting sample data:

CREATE TABLE IF NOT EXISTS employee (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(40),
    money INT
) ENGINE MyISAM;
INSERT INTO employee(name, money) VALUES('Li Du', 1000);
INSERT INTO employee(name, money) VALUES('Non‑Tech Graduate', 2000);

When a session acquires a write lock, all other sessions block on any operation (SELECT, INSERT, UPDATE). When a session acquires a read lock, other sessions can also read but cannot write.

Lock‑competition metrics can be inspected with: SHOW STATUS LIKE 'table%'; Key variables: Table_locks_immediate – number of lock requests granted immediately. Table_locks_waited – number of lock requests that had to wait (higher values indicate more contention).

MyISAM prioritises write requests over reads, which can cause read starvation under heavy write load. The low_priority_updates system variable can be tuned to give reads higher priority.

InnoDB Lock Demonstrations

InnoDB supports row locks, gap locks, next‑key locks, and full‑table locks when queries bypass indexes.

Explicit row‑level lock syntax:

// Shared read lock on a SELECT
SELECT ... LOCK IN SHARE MODE;
// Exclusive write lock on a SELECT
SELECT ... FOR UPDATE;

Test scenario:

DROP TABLE IF EXISTS employee;
CREATE TABLE employee (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(40),
    money INT,
    num INT
) ENGINE INNODB;
INSERT INTO employee(name, money) VALUES('Li Du', 1000);
INSERT INTO employee(name, money) VALUES('Non‑Tech Graduate', 2000);

Session 1 begins a transaction and locks a row using a non‑indexed condition, which forces a table lock. Session 2’s UPDATE on a different row blocks until Session 1 releases the lock, illustrating that non‑index queries fall back to table‑level locking.

When the query uses the primary‑key index, InnoDB acquires row‑level locks, allowing concurrent updates on other rows. Adding a normal index on name and querying by that index can still cause lock contention on rows sharing the same indexed value.

Gap Locks

Gap locks protect ranges between index entries to prevent phantom rows during repeatable‑read isolation. Example:

SELECT * FROM employee WHERE id > 100 FOR UPDATE;

After adding a numeric column num and populating values 1, 1, 3, the following test shows that inserts into the locked gap (e.g., num = 2 or num = 4) wait, while inserts outside the gap succeed.

INSERT INTO employee VALUES(5,'test',5000,2); -- waits (gap locked)
INSERT INTO employee VALUES(6,'test',5000,6); -- succeeds

Primary‑key (unique) indexes do not require gap locks because the index guarantees a single row. Range queries on non‑unique indexes do acquire gap locks, and even queries with non‑existent values (e.g., num >= 8) lock the surrounding gap to prevent phantom inserts.

Deadlock Scenarios

Deadlocks occur only with InnoDB’s row‑level locking. Two classic examples are presented:

Two sessions update rows that share a non‑unique index value in opposite order, causing circular wait.

Two sessions each lock a different row and then attempt to lock the other’s row, also resulting in a circular wait.

Resolution strategies include:

Designing application logic to acquire locks in a consistent order or serialising high‑contention operations.

Setting innodb_lock_wait_timeout to limit wait time.

Enabling innodb_deadlock_detect so MySQL automatically rolls back one victim transaction.

Conclusion

The article summarises the key takeaways:

MyISAM provides simple table‑level shared read and exclusive write locks; writes are prioritised, making it unsuitable for write‑heavy workloads.

InnoDB’s row‑level locks offer finer granularity and better concurrency, but can lead to deadlocks that must be managed.

Gap locks solve phantom‑read problems in repeatable‑read isolation by locking index ranges.

Understanding lock types, lock‑wait metrics, and proper transaction design is essential for building high‑performance, concurrent MySQL applications.