Understanding InnoDB Table Lock Acquisition in MySQL 8.0.32

1. Has the lock already been acquired?

A transaction may operate on the same table multiple times; InnoDB avoids redundant table‑lock operations by checking if the transaction already holds an equivalent or stronger lock.

Before adding a table lock, InnoDB traverses the transaction's trx_locks list to see if a matching or higher‑level lock exists.

The trx_locks list contains both table‑lock and row‑lock structures, possibly for different tables. Each lock structure is examined; row‑locks are ignored, and table‑locks for other tables are also ignored.

If the lock type matches, InnoDB compares lock levels using a lock‑strength matrix (shown in the original article) to decide compatibility.

2. Acquire a token (mutex)

Each transaction’s lock structures are linked via trx_locks . All transactions that lock the same table are linked via a separate locks list.

To avoid conflicts when inserting a new table‑lock into the locks list, InnoDB uses a mutex (mutual‑exclusion) mechanism. The mutex acts as a token; a transaction must obtain the token before adding its lock structure.

The mutex is stored in lock_sys.latches.table_shards.mutexes , an array of 512 mutexes. The table ID modulo 512 determines which mutex to use.

If another transaction holds the mutex, the current transaction waits; otherwise it proceeds immediately.

3. Will the lock be blocked?

After obtaining the mutex, InnoDB checks whether existing table locks conflict with the requested lock.

Lock‑mode counters are used for fast checks of intention‑shared (IS) and intention‑exclusive (IX) locks. The relevant code snippet:

// storage/innobase/lock/lock0lock.cc
static
inline
const
lock_t *lock_table_other_has_incompatible(...){
    ...
    // LOCK_IS: intention shared lock
    // LOCK_IX: intention exclusive lock
    if ((mode == LOCK_IS || mode == LOCK_IX) &&
        table->count_by_mode[LOCK_S] == 0 &&
        table->count_by_mode[LOCK_X] == 0) {
        // No conflicting lock, can acquire immediately
        return nullptr;
    }
    ...
}

If both counters are zero, the lock can be granted immediately. Otherwise, InnoDB traverses the locks list and checks each lock’s mode against the requested mode using the compatibility matrix (plus sign = compatible, minus sign = incompatible).

4. Request a table‑lock structure

When a lock can be granted (or after waiting), a new table‑lock structure must be allocated. Two cases exist:

Case 1: Use a pre‑allocated lock structure from the transaction’s pool of eight lock objects.

Case 2: Allocate a new lock object when the pool is exhausted.

The new structure’s type_mode field is initialized with the lock mode bits, a flag indicating it is a table lock, and (if needed) a waiting flag.

After initialization, the lock structure is inserted at the head of the transaction’s trx_locks list (table locks go to the front, row locks to the tail) and appended to the table’s locks list.

Special handling exists for AUTO‑INC locks: each table has an autoinc_lock stored in the table object; if the lock can be obtained immediately, this existing structure is reused.

5. Summary

The overall workflow for a transaction (T1) acquiring a table lock on table A is:

Check whether T1 already holds an equivalent or stronger lock on A; if so, stop.

Obtain the table‑lock mutex (token). If another transaction holds it, wait.

Determine whether existing locks on A block the requested lock; if blocked, wait.

Allocate or reuse a table‑lock structure, initialize its fields, and link it into both the transaction’s trx_locks list and the table’s locks list.