Row Locks vs. Table Locks in MySQL InnoDB

When a query in MySQL's InnoDB storage engine makes another query wait, InnoDB usually locks entries in an index that the first query scanned, or a span of those entries. It does not automatically lock the whole table. InnoDB also uses table-level locks to coordinate row locks. MySQL has separate mechanisms for explicit table locks and for access to table metadata, and each affects which queries can run at the same time.

A record lock protects an entry in an index. That often corresponds to a row developers recognize, but InnoDB locks the index entry itself. A gap lock protects the space between index entries from inserts. A table-level intention lock signals that a transaction is locking rows in that table; it does not, by itself, block ordinary access to the whole table. Check which index range a statement scans and what kind of lock a waiting session requests.

What InnoDB locks

InnoDB primarily locks entries in indexes. When a query searches a secondary index and takes exclusive locks, InnoDB also locks the matching entries in the table’s clustered index, which stores the row data. The InnoDB locking rules describe shared (S) and exclusive (X) record locks. A shared lock conflicts with an exclusive request. An exclusive lock conflicts with both shared and exclusive requests.

Before a transaction takes row-level locks, InnoDB acquires an intention lock on the table. IS indicates that the transaction intends to take shared row locks; IX indicates that it intends to take exclusive row locks. These locks coordinate row locks with table-level requests. Seeing LOCK_TYPE = 'TABLE' and LOCK_MODE = 'IX' in Performance Schema therefore does not mean that InnoDB has exclusively locked every row. Check the mode and the other locks held by the transaction.

Gaps matter when a query searches for values across a span rather than for one exact value. A next-key lock protects both an index record and the gap immediately before it. In MySQL 8.4, InnoDB defaults to REPEATABLE READ. Under that isolation level, a search across a span of values can take gap or next-key locks. A search for one value using a unique index generally locks only the matching record. Under READ COMMITTED, locking reads, updates, and deletes generally do not take gap locks. Foreign-key and duplicate-key checks are exceptions. See the manual’s isolation-level details.

How statements affect concurrency

A plain SELECT under REPEATABLE READ or READ COMMITTED normally reads a consistent snapshot, a view of the data, without setting record locks. Locking reads and data changes work differently. The locks depend on the statement, the index and records it scans, the isolation level, and any constraints.

The execution plan determines which index records the engine scans, so the WHERE clause alone does not reveal the lock footprint. The statement locking rules explain why. Under SERIALIZABLE, ordinary searches in a transaction also use shared locking behavior.

A locking read protects a value while a transaction uses it. For example, use FOR UPDATE when the transaction reads a row and plans to change it based on that value. A plain SELECT does not protect the row from changes. InnoDB keeps locks from locking reads and data changes until the transaction commits or rolls back. With autocommit enabled, each successful statement is its own transaction. See locking reads and commit and rollback behavior.

A targeted lookup versus a broad scan

A unique-key lookup typically limits contention to one record because the key identifies at most one row:

CREATE TABLE inventory (
    sku BIGINT PRIMARY KEY,
    warehouse_id INT NOT NULL,
    qty INT NOT NULL
) ENGINE = InnoDB;

START TRANSACTION;

SELECT qty
FROM inventory
WHERE sku = 42
FOR UPDATE;

Because sku is a unique primary key, InnoDB can identify the matching record directly. A transaction that tries to update that record must wait for the lock to be released. Work on a different record in the same table is not blocked merely because both transactions use inventory. Under REPEATABLE READ, this unique equality lookup locks the record rather than the preceding gap.

Now consider a search with no useful index on qty:

START TRANSACTION;

SELECT sku, qty
FROM inventory
WHERE qty < 5
FOR UPDATE;

/* The transaction does more work before COMMIT. */

If MySQL scans the table to find rows that meet the condition, InnoDB can lock many of the index records it passes. Under REPEATABLE READ, a scan across a range of values can also protect gaps and affect inserts. The query can return only a few rows even though MySQL scanned a much wider range. Check the plan with EXPLAIN; the number of returned rows does not show how many records InnoDB locked.

When a table-level wait is not a row lock

“Table lock” can refer to different mechanisms. An IS or IX intention lock accompanies row locking and does not, on its own, block ordinary table access. An explicit LOCK TABLES ... READ or LOCK TABLES ... WRITE request restricts access at the table level.

Metadata locks are separate. MySQL uses them to coordinate access to database objects and protect their structure. A transaction that has used a table can keep a metadata lock until the transaction ends, which can make a statement such as ALTER TABLE wait even when the transaction’s data locks concern only particular records. In that case, inspect metadata locks rather than concluding that InnoDB locked every row.

Find the lock that is blocking a query

Performance Schema reports InnoDB locks in data_locks and connects waiting requests to blockers in data_lock_waits. This query shows the lock type, mode, index, and record data for each side of a wait:

SELECT
    req.OBJECT_SCHEMA AS waiting_schema,
    req.OBJECT_NAME   AS waiting_table,
    req.INDEX_NAME    AS waiting_index,
    req.LOCK_TYPE     AS waiting_lock_type,
    req.LOCK_MODE     AS waiting_lock_mode,
    req.LOCK_DATA     AS waiting_lock_data,
    blk.OBJECT_SCHEMA AS blocking_schema,
    blk.OBJECT_NAME   AS blocking_table,
    blk.INDEX_NAME    AS blocking_index,
    blk.LOCK_TYPE     AS blocking_lock_type,
    blk.LOCK_MODE     AS blocking_lock_mode,
    blk.LOCK_DATA     AS blocking_lock_data,
    req.THREAD_ID     AS waiting_thread_id,
    blk.THREAD_ID     AS blocking_thread_id
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS req
  ON req.ENGINE = w.ENGINE
 AND req.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS blk
  ON blk.ENGINE = w.ENGINE
 AND blk.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID;

A RECORD lock shows that a query is waiting for an index record; inspect the index and lock data to see which one. For a TABLE lock, check the mode: IS and IX are intention locks. If the wait involves DDL, which changes table structure, inspect performance_schema.metadata_locks. The data lock tables and wait relationships show current lock information. Compare it with the sessions’ statements and transaction state.

Compare the blocking statement with its EXPLAIN plan. If MySQL scans more records than expected, review the index and the condition in the statement. Check whether the blocking transaction stays open after its statement finishes; InnoDB normally keeps its locks until COMMIT or ROLLBACK. A selective index and shorter transactions help reduce lock contention and deadlocks. Accessing rows in a consistent order also helps reduce them. See MySQL’s guidance on handling InnoDB deadlocks.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com