How MySQL InnoDB Locking Reads Work

When a MySQL query reads rows that a later statement depends on, use a locking read inside an explicit transaction, a group of statements you start and finish together. A locking read asks InnoDB to lock the rows it reads so another transaction cannot change them before yours ends. A regular SELECT usually reads without taking those locks, so another session can update or delete a row before your next statement.

InnoDB is MySQL’s storage engine for transactions. Its two locking-read forms are SELECT ... FOR SHARE, which lets other sessions read locked rows but prevents them from changing those rows, and SELECT ... FOR UPDATE, which takes a stronger lock when you plan to read and then update rows. The MySQL manual on locking reads describes both forms and their transaction requirements.

Choose a locking read when the next step depends on the rows

Use a plain SELECT to display data or make a decision that does not depend on the selected rows staying unchanged. InnoDB normally treats a plain SELECT as a nonlocking consistent read: it returns a view of data that transactions have committed, without waiting for another transaction’s row lock.

Use FOR UPDATE when you plan to update rows based on what you read. For example, a transaction that checks an inventory quantity and then subtracts from it should lock the inventory row before deciding. The lock prevents another transaction from changing that row in the meantime.

Use FOR SHARE when you need to check a row and prevent other transactions from changing it, but you do not plan to update it yourself. For example, you can use it when checking a parent row before inserting a related child row. The shared lock lets other sessions read the parent row, while transactions that want to change it must wait until yours ends.

Keep the lock inside a transaction

With autocommit enabled, MySQL commits each statement as its own transaction. To keep a locking read’s locks while you do the work that depends on it, start a transaction with START TRANSACTION. The locks remain until you run COMMIT or ROLLBACK. Setting autocommit to 0 also keeps transactions open, but you must then finish each transaction yourself.

For example, this does not protect a multi-statement decision when autocommit is on:

SELECT quantity
FROM inventory
WHERE id = 1
FOR UPDATE;

UPDATE inventory
SET quantity = quantity - 1
WHERE id = 1;

The first statement’s transaction ends as soon as the SELECT finishes, so its lock does not protect the later UPDATE. Start an explicit transaction before the locking read to keep the lock until the later statement finishes.

Watch another session wait for the lock

The example uses a primary key, which InnoDB indexes to find the row, and one existing row. Run the setup once, then follow the session order.

CREATE TABLE inventory (
    id INT PRIMARY KEY,
    quantity INT NOT NULL
) ENGINE=InnoDB;

INSERT INTO inventory (id, quantity) VALUES (1, 10);

Session A starts a transaction and locks the row:

START TRANSACTION;

SELECT quantity
FROM inventory
WHERE id = 1
FOR UPDATE;

The query returns 10 and keeps the row locked.

Session B starts a transaction and requests the same lock:

START TRANSACTION;

SELECT quantity
FROM inventory
WHERE id = 1
FOR UPDATE;

Session B waits because Session A holds the lock. While it waits, run these statements in Session A:

UPDATE inventory
SET quantity = quantity - 1
WHERE id = 1;

COMMIT;

After Session A commits, Session B’s locking read finishes and returns the current quantity, 9. Session B then holds its own lock until it commits or rolls back:

COMMIT;

The transaction must cover both the read and the work that depends on it. Session A’s lock keeps other transactions from changing the row between its read and update.

The query plan and isolation level affect lock scope

InnoDB locks entries in an index, the structure it uses to find rows, when a locking read searches them. A search across a range of values can lock more entries than the query returns. An isolation level is a setting that controls how transactions read data while other transactions change it. Under the default REPEATABLE READ level, a range search can also lock gaps between index entries, which prevents inserts into those gaps. At READ COMMITTED, locking reads generally lock index entries without locking those gaps. The MySQL isolation-level documentation describes how the search condition and isolation level affect these behaviors.

Use a condition on an indexed column that matches only the rows you need to limit contention. A broad scan can make InnoDB examine and lock more index entries than expected. LIMIT alone does not ensure a narrow set of locks if the query must sort rows before choosing which ones to return.

A locking clause applies only to the tables in the part of the query where you write it. If a locking read contains a nested query and you also need to protect the rows that nested query reads, add a locking clause inside the subquery too. The manual also documents NOWAIT, which returns an error instead of waiting for a locked row, and SKIP LOCKED, which leaves locked rows out of the result. These options let you choose whether to fail quickly or let workers claim available queue rows.

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