Implementing Locking Reads in MySQL InnoDB

When a MySQL transaction checks a row before doing related work, another transaction can change the row before the next statement runs. InnoDB locking reads prevent that change by locking rows the query reads. A transaction is a group of database statements that MySQL commits or rolls back together.

Use FOR SHARE when other transactions must not change or delete a row while your transaction relies on it. Use FOR UPDATE when your transaction will change the row or needs exclusive access. Run the read inside an explicit transaction and keep that transaction short. Also check how the query uses an index, a data structure that helps MySQL find rows, because InnoDB can lock records it scans, not just the rows it returns.

Choose the lock for the work that follows

FOR SHARE lets other transactions read the locked rows, but prevents them from changing or deleting those rows until your transaction ends. Use it to protect related work, such as checking that a parent row exists before inserting a child row.

FOR UPDATE takes an exclusive lock on the index records InnoDB encounters. Use it when you will change those records, or when another transaction must not treat them as available for an operation that conflicts with yours. The MySQL 8.4 locking-reads documentation describes these two clauses and their behavior.

A plain SELECT usually performs a consistent read. InnoDB returns the row version from the transaction’s read view, a snapshot of the data, without locking that version against changes. A locking read asks for the current rows and locks them. So a FOR UPDATE query does not block every reader: a plain consistent read can still return an earlier version.

Take care when code reads a row and then changes it while holding a shared lock. Two transactions can both acquire FOR SHARE locks and then try to update the same row. Each transaction waits for the other’s shared lock to end, which can cause a deadlock. Use FOR UPDATE when your transaction will read and then change the same row.

Keep the check and related work in one transaction

When checking for a parent row before inserting a child row, run the locking read and insert on the same database connection and within the same transaction:

START TRANSACTION;

SELECT id
FROM parent
WHERE id = ?
FOR SHARE;

-- The application checks that the query returned a row.
INSERT INTO child (parent_id, ...)
VALUES (?, ...);

COMMIT;

If the query finds the parent, its shared lock prevents another transaction from deleting or changing that row before the insert completes. If the query finds no row, do not insert the child. A foreign-key constraint should also enforce the parent-child relationship. The locking read supports the application’s multi-statement logic, but does not replace that constraint.

InnoDB releases locks when the transaction commits or rolls back. Use explicit START TRANSACTION and COMMIT statements to make clear how long the locks last. Locking reads require autocommit to be disabled. You can do this by starting a transaction or setting the session’s autocommit value to 0, as described in the locking-reads reference.

Lock counters before changing them

When two transactions read and increment the same counter, a shared lock does not safely coordinate their updates. An exclusive locking read lets one transaction access the counter at a time:

START TRANSACTION;

SELECT counter_field
FROM child_codes
FOR UPDATE;

UPDATE child_codes
SET counter_field = counter_field + 1
WHERE ...;

COMMIT;

The UPDATE must identify the counter row you intend to change. The MySQL manual’s example omits a WHERE clause, so copying it into a table with multiple rows would update every row. Add a condition that selects the counter row stored in your table.

For generating a unique identifier, MySQL documents a single-table-access alternative:

UPDATE child_codes
SET counter_field = LAST_INSERT_ID(counter_field + 1);

SELECT LAST_INSERT_ID();

The second statement reads identifier information specific to the connection instead of reading the table again. Use this pattern for the identifier-generation task it describes.

Check which index records the query scans

The rows a locking read returns do not show every record it locks. InnoDB generally locks index records scanned to find matches. Under the default REPEATABLE READ isolation level, a search across a range of values can also take gap or next-key locks. These locks cover spaces between index records as well as the records themselves, so inserts into those spaces can wait. When a search uses a unique index and looks for one unique value, it takes a narrower lock on the matching record. The InnoDB lock-setting rules explain how the statement and the way it searches affect the locks.

If an index is missing or does not suit the query, InnoDB can scan and lock far more records than you expect. Check the query plan, which shows how MySQL finds the rows, and add indexes for the columns used in the search. Under READ COMMITTED, locking reads lock index records but not the spaces before them, except for gap locks used in foreign-key and duplicate-key checks, as described in the isolation-level reference. If a query blocks unexpected inserts, check both its isolation level and its query plan.

A locking clause on the outer query does not automatically lock rows read by a nested subquery. Add a locking clause to the subquery itself if you also need to protect the rows it reads.

Use NOWAIT or SKIP LOCKED for different outcomes

By default, a locking read waits if another transaction holds a lock that conflicts with its request. In MySQL 8.4, you can change this behavior in two ways:

• NOWAIT returns an error immediately when the query cannot obtain a requested row lock.
• SKIP LOCKED omits locked rows and returns matching rows that are not locked.

A queue worker can use SKIP LOCKED to select available jobs without waiting for rows another worker has claimed:

START TRANSACTION;

SELECT id
FROM jobs
WHERE status = 'ready'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;

-- The application marks the selected rows as claimed here.

COMMIT;

Update or claim the selected rows in the same transaction. The application also needs to handle an empty result and failures. SKIP LOCKED deliberately leaves out rows held by other transactions, so it suits queue work where workers can take different available rows. Do not use it for a read that needs a complete result. MySQL documents that NOWAIT and SKIP LOCKED apply to row-level locks and that statements using them are unsafe for statement-based replication.

Handle waits and deadlocks in application code

Locking reads coordinate transactions that compete for rows, but lock waits and deadlocks can still occur. A deadlock occurs when transactions wait for locks held by one another. Keep write transactions short, access rows and tables in a consistent order, and add indexes for columns used in locking reads and updates. When InnoDB detects a deadlock, it chooses a transaction to roll back. If that transaction’s work needs to complete, the application should retry it. The InnoDB deadlock guidance describes these causes and ways to prevent them.

Handle NOWAIT errors as an explicit outcome in the application’s rules for competing transactions. NOWAIT avoids waiting, but does not retry or resolve the conflict. If the application retries, it should rerun the whole transaction rather than continue from statements that ran before the error.

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