Comprehensive Guide to MySQL Lock Mechanisms: Table, Row, Gap Locks and Deadlock Handling

The article begins with a motivation: interview candidates often struggle with MySQL lock concepts, especially under high concurrency, so a thorough review is presented.

Lock Types Overview : MySQL locks are classified by granularity (table, page, row), mode (shared/read vs. exclusive/write), and philosophy (optimistic vs. pessimistic). Table locks have the largest granularity, row locks the smallest, and gap locks are used to prevent phantom reads.

MyISAM Lock Mechanism : MyISAM supports only table‑level locks—shared read locks and exclusive write locks. Explicit locking can be done with:

LOCK TABLE table_name READ;
LOCK TABLE table_name WRITE;
UNLOCK TABLES;

A sample table is created:

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('黎杜', 1000);
INSERT INTO employee(name, money) VALUES('非科班的科班', 2000);

Testing shows that a write lock blocks all other sessions from reading or writing, while a read lock allows concurrent reads but blocks writes.

The article also explains how to monitor lock contention with SHOW STATUS LIKE 'table%' , focusing on Table_locks_waited and Table_locks_immediate .

InnoDB Lock Mechanism : InnoDB supports row‑level locks and transactions. Row locks can be shared (S) or exclusive (X). Example of explicit row locking:

SELECT ... LOCK IN SHARE MODE;   -- shared read lock
SELECT ... FOR UPDATE;          -- exclusive write lock

A test table is created with the InnoDB engine, and various scenarios demonstrate when MySQL uses table locks (non‑indexed queries) versus row locks (indexed queries). The article also covers gap locks, which lock the range between index values to prevent phantom reads, illustrated by:

SELECT * FROM employee WHERE id > 100 FOR UPDATE;

Additional statements add a num column, create gaps, and show how inserts within locked gaps are blocked while inserts outside the gaps succeed.

Deadlock Scenarios : Two deadlock cases are described—one involving updates on rows with the same non‑unique index value, and another where two transactions lock different rows and then attempt to lock each other's rows. The article suggests design strategies (serializing access, lock escalation) and MySQL parameters ( innodb_lock_wait_timeout , innodb_deadlock_detect ) to mitigate deadlocks.

Conclusion : MyISAM’s table locks are simple but unsuitable for heavy read/write workloads, while InnoDB’s row and gap locks provide finer granularity and better concurrency at the cost of possible deadlocks, which can be managed with proper transaction design and configuration.