Understanding Implicit Locks in MySQL InnoDB

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

1. What is an implicit lock?

In InnoDB, explicit locks have a corresponding lock structure that can be queried through performance_schema.data_locks. Implicit locks are a special form of ordinary record lock that lack such a structure, making them invisible to direct queries. They exist like air—present but not directly observable—until they are transformed into explicit locks.

2. How to determine the existence of an implicit lock?

Implicit locks can appear on both primary‑key and secondary‑index records. Four typical situations create them:

Case 1: An INSERT or UPDATE that inserts a row into the primary key index leaves an implicit lock on the new record until the transaction commits.

Case 2: An INSERT that adds a row to a secondary index leaves an implicit lock on that index record.

Case 3: An UPDATE that modifies a secondary‑index column deletes the old index entry and inserts a new one, leaving implicit locks on both.

Case 4: A DELETE that scans without using a secondary index leaves an implicit lock on the deleted secondary‑index record.

For primary‑key records, InnoDB reads the DB_TRX_ID field to find the owning transaction; if the transaction is uncommitted and no explicit lock exists, an implicit lock is assumed. For secondary indexes, the page header field PAGE_MAX_TRX_ID indicates the latest transaction that modified any record on the page. To check a specific secondary‑index record (e.g., S1), the algorithm is:

Read PAGE_MAX_TRX_ID from the page header and see if the corresponding transaction is committed.

If not committed, locate the primary‑key record for S1, read its DB_TRX_ID, and check its commit status.

If either transaction is still active, the record is considered to have an implicit lock.

3. Converting to an explicit lock

When a transaction needs to lock a row that currently holds an implicit lock, the implicit lock is upgraded to an explicit lock before the waiting transaction can proceed. This also happens during partial rollbacks (to a savepoint), where any record that had an implicit lock is turned into an explicit lock.

Because implicit locks are a type of exclusive ordinary record lock, the conversion results in an exclusive explicit lock that can be observed via performance_schema.data_locks.

4. Summary

Implicit locks are a special form of exclusive ordinary record lock that cannot be directly queried. Their existence can be inferred from transaction states and page metadata. In certain scenarios they are automatically converted to explicit locks, after which they become visible through the performance schema.