When Are InnoDB Locks Released? A Deep Dive into MySQL 8.0.32 Lock Release Mechanisms

1. Overview

InnoDB typically releases table and row locks when a transaction is about to commit or roll back, because the lock lifetime follows the transaction lifetime. However, some locks are released earlier to avoid unnecessary blocking.

2. Locks Released When WHERE Condition Does Not Match

During SELECT , UPDATE , or DELETE execution, every scanned record acquires a row lock regardless of whether the WHERE clause matches an index. In REPEATABLE-READ and SERIALIZABLE isolation levels, all locks are held until transaction end.

In READ-UNCOMMITTED and READ-COMMITTED levels, if a record is found not to satisfy the WHERE condition, the row lock is released immediately. This can happen in two ways:

InnoDB pushes part or all of the WHERE condition down to the storage engine and checks each record during the scan.

The server layer checks each returned record against the original WHERE condition.

When a record does not match, its row‑lock bitmap bit is cleared, and waiting transactions may be granted the lock.

An exception occurs when a lock is obtained after waiting for another transaction; InnoDB cannot determine which SQL statement caused the lock, so it does not release the lock even if the record later fails the WHERE test.

3. Prepare Stage of Two‑Phase Commit

Under READ-UNCOMMITTED and READ-COMMITTED , index record locks acquired during full‑table scans or range scans are ordinary record locks, not gap or Next‑Key locks. Foreign‑key and duplicate‑value checks still use gap/Next‑Key locks.

These gap locks can be released in the prepare stage, before the final commit, by traversing the transaction's trx_locks list:

If the lock is a gap lock, remove it from the lock list and clear the corresponding bitmap bits.

If the lock is a Next‑Key lock, set its type_mode to LOCK_REC_NOT_GAP , turning it into a plain record lock while keeping the record‑level lock.

4. Transaction Commit or Rollback

Both shared and exclusive row locks are preceded by intention table locks, which are released only at the end of the transaction. Apart from the early releases described above, all remaining row locks are held until commit or rollback.

Releasing a row lock involves:

Removing the lock structure from the transaction's trx_locks list.

Removing it from the hash table that indexes row locks.

Granting the lock to any waiting transaction according to the lock‑granting rules.

Releasing a table lock follows a similar process, also updating the table object's lock list.

5. AUTO‑INC Lock

There are two kinds of AUTO‑INC locks:

A lightweight mutex‑style lock that is released immediately after the auto‑increment value is fetched.

A true table‑level AUTO‑INC lock that behaves like other table locks and is released when the locking SQL statement finishes.

The release steps for the table‑level AUTO‑INC lock are:

Delete the lock from the transaction's autoinc_locks array.

Remove it from trx_locks and from the table object's lock list.

Grant the lock to any waiting transaction.

6. Summary

All table locks added by InnoDB are released at transaction end (except manually added table locks). Row‑lock release timing depends on isolation level:

In REPEATABLE-READ and SERIALIZABLE , every row lock is held until commit/rollback.

In READ‑UNCOMMITTED and READ‑COMMITTED , locks on records that do not match the WHERE clause are released immediately, and gap/Next‑Key locks are partially released in the prepare stage.

Remaining locks are released at the final commit/rollback.

AUTO‑INC locks have two release points: the lightweight lock is released after use, while the table‑level lock is released when the statement finishes.