Understanding MySQL Table and Row Locks: Mechanisms, Modes, and Types

Table Lock vs. Row Lock

Table locks lock an entire table and are usually used for DDL operations; row locks lock specific rows or gaps and are implemented by the storage engine. In MySQL, the server provides table locks, while InnoDB supports row locks and MyISAM only uses table locks.

Table Lock Usage

Table locks are acquired with LOCK TABLE ... READ|WRITE and released with UNLOCK TABLES. The lock is held for the whole session until explicitly released or a new LOCK TABLE or transaction start overrides it.

mysql> LOCK TABLE user READ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM user WHERE id = 100;
# success

mysql> SELECT * FROM role WHERE id = 100;
# fails, no read lock on role

mysql> UPDATE user SET name = 'Tom' WHERE id = 100;
# fails, no write lock on user

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

Row Lock Fundamentals

InnoDB stores data in a clustered B+‑tree where leaf nodes contain both primary key values and row data. Secondary indexes store only the primary key, so a lookup via a secondary index requires an additional primary‑key lookup. Row locks are applied at the index level.

When updating a row by primary key, only the primary‑key index record is locked. When using a secondary index, InnoDB first locks the secondary index entry, then the corresponding primary‑key record.

UPDATE user SET age = 10 WHERE id = 49;          -- lock on primary key id=49
UPDATE user SET age = 10 WHERE name = 'Tom';    -- lock on secondary index name='Tom' and then lock on primary key id=49

Multi‑Row Updates

For statements like UPDATE user SET age = 10 WHERE id > 49, MySQL reads matching rows one by one, InnoDB locks each row, the server updates it, then proceeds to the next row until no more matches are found.

Lock Modes

MySQL defines several lock modes:

Read lock (S) : shared, multiple transactions can hold it.

Write lock (X) : exclusive, only one transaction can hold it.

Intention locks – table‑level locks that indicate a transaction intends to acquire row locks: read intention (IS) and write intention (IX).

Auto‑increment lock (AI) : a special table lock used when inserting into a table with an AUTO_INCREMENT column.

Lock Compatibility Matrix

Intention locks never conflict with each other. S locks are compatible only with IS locks; X locks conflict with all other locks; AI locks are compatible only with intention locks.

Row‑Lock Types

Based on granularity, row locks are classified as:

Record lock – locks a single index record.

Gap lock – locks the gap between index records.

Next‑key lock – a combination of a record lock and the preceding gap.

Insert‑intention lock – a special gap lock used during INSERT.

Record locks are the simplest; they lock only the matching rows. Gap locks prevent other transactions from inserting into the locked interval, eliminating phantom reads. Next‑key locks lock both the record and its preceding gap, using left‑open right‑closed intervals.

(-∞,15] , (15,18] , (18,20] , (20,30] , (30,49] , (49,50] , (50,+∞)

Insert‑intention locks do not conflict with each other but conflict with gap locks and next‑key locks, ensuring that concurrent inserts into the same gap are serialized.

Compatibility of Lock Types

Insert‑intention locks do not affect other lock types, but they conflict with gap locks and next‑key locks. Gap locks do not conflict with other locks (except insert‑intention). Record locks conflict with other record locks and with next‑key locks.

Understanding these mechanisms helps design efficient transactions and avoid deadlocks in MySQL.