Understanding the InnoDB Lock‑Waiting Process in MySQL 8.0.32

1. Queue (先排队)

When a transaction cannot acquire a table or row lock immediately, it enters a lock‑waiting state and must be placed in a lock queue. The transaction already has initialized lock structures; the only difference is that the type_mode field’s 9th bit is set to 1 to mark the structure as waiting.

Each transaction pre‑creates eight table‑lock structures and eight row‑lock structures. If a pre‑created structure is free, it is reused; otherwise a new structure is allocated.

All lock structures for the same table are linked via their locks field, forming the locks chain . Table‑lock structures are appended to the end of the table object's locks list, while row‑lock structures are linked through the hash field of the rec_hash array. Waiting row‑lock structures are also added to the end of their respective chain.

Each lock structure, whether table or row, is also linked into the transaction’s trx_locks chain: table‑lock structures are inserted at the head, row‑lock structures at the tail.

2. Registration (再登记)

After joining the queue, InnoDB registers the waiting transaction by finding a free slot in the waiting_threads array (size srv_max_n_threads). The first slot whose in_use flag is false is selected.

The slot’s in_use flag is set to true.

The lock‑wait timeout value is stored in the slot’s wait_timeout field for background‑thread timeout checks.

Other slot attributes are filled with relevant lock‑wait information.

The background thread is notified that a lock‑wait has been registered.

When the lock being waited on is a row lock, InnoDB also records the current time as the lock‑wait start time; for table locks this time is recorded by the server layer.

3. Wait for Notification (坐等通知)

The waiting transaction will be notified in any of the following cases:

Lock‑wait timeout expires.

Another transaction releases the lock, allowing the waiting transaction to acquire it.

The deadlock detector selects the waiting transaction as a victim.

4. Summary (总结)

The lock‑waiting workflow consists of four main steps:

Allocate a lock structure and add it to the appropriate chain to start queuing.

Find a free waiting_threads slot and record lock‑wait information (registration).

If the lock is a row lock, record the start time of the wait.

Wait for one of the notification events to occur.