Granting Table Locks and Row Locks in InnoDB

This article is based on the MySQL 8.0.32 source code, using the InnoDB storage engine.

1. Active and Passive

When a transaction requests a table or row lock, it may obtain the lock immediately (active) or be blocked by another transaction and enter a waiting state (passive). The passive case is referred to as a granted lock after the wait is resolved.

The article uses a food‑ordering analogy: ordering a meal is like requesting a lock; picking up the meal at the restaurant is an immediate lock, while waiting for delivery is a granted (passive) lock.

2. Granting Table Locks

When a transaction releases a table lock, InnoDB removes the lock from the transaction’s trx_locks list and the table’s locks list, then scans the table’s lock list to find waiting lock structures that can be granted.

The scan starts from the lock structure immediately after the one that was released, not from the head of the list. For each lock structure, InnoDB checks two conditions: (1) the lock is in waiting state (type_mode bit 9 = 1) and (2) the lock is blocked by the lock that was just released.

Locks satisfying both conditions are called waiting lock structures . Only these are considered for granting.

If a waiting lock is not blocked by any earlier lock in the list, InnoDB clears its waiting flag, wakes up the owning transaction (by signaling its slot event), and the lock is granted. If it is blocked, the waiting transaction’s lock‑wait relationship is updated, and the dead‑lock detector is notified.

3. Granting Row Locks

Releasing row locks can happen for a single record or for all records in one or more lock structures. Internally, InnoDB always releases one record at a time, iterating over the bitmap of a lock structure.

Row‑lock granting is more complex than table‑lock granting because it must consider transaction priority, weight, and FIFO order. InnoDB builds two arrays before granting:

granted array : contains locks that have already been granted, ordered by grant time (newest first).

waiting array : contains all waiting row‑lock structures, ordered as follows: High‑priority transactions (priority > 0) are placed first, preserving FIFO within this group. High‑weight transactions (weight > 1) follow, ordered by descending weight, then FIFO for equal weight. Low‑weight transactions (weight ≤ 1) are placed last, preserving FIFO.

The granting algorithm iterates over the waiting array. For each waiting row‑lock structure, InnoDB checks whether it would be blocked by any lock in the granted array. If not blocked, the waiting flag is cleared, the owning transaction is awakened, the lock is moved to the front of the rec_hash chain, and the lock is appended to the granted array.

If the lock would be blocked, the transaction’s wait‑for relationship is updated to point to the blocking lock, and the dead‑lock detector thread is notified.

4. Summary

Granting a table lock follows a simple FIFO rule: the first waiting transaction receives the lock, and compatible locks can be granted together.

Granting a row lock follows a more elaborate order: first high‑priority transactions (FIFO), then high‑weight transactions (by weight, then FIFO), and finally low‑weight transactions (FIFO). If a granted lock is compatible with other waiting locks, those can also be granted regardless of the ordering rules.