skip to content

InnoDB Locking & Isolation

InnoDB defaults to REPEATABLE READ and locks index ranges with gap and next-key locks. Deadlock stories and lock waits are a favourite MySQL interview prompt.

questions

6

In MySQL InnoDB at REPEATABLE READ, why can a plain SELECT and SELECT ... FOR UPDATE in one transaction return different row values?

level: middleimportance: must knowfreq 65%

answer

  1. two kinds of read
  2. snapshot versus newest committed
  3. snapshot fixed at first plain read
  4. UPDATE and FOR UPDATE skip the snapshot

basics

~20 s

InnoDB serves a plain SELECT as a consistent read from a snapshot fixed at the transaction's first such read, while FOR UPDATE, FOR SHARE, UPDATE and DELETE are locking reads that see and lock the newest committed row version.

solid answer

~40 s

InnoDB's default isolation level is `REPEATABLE READ`, and at that level a plain `SELECT` is a **consistent (nonlocking) read**: it reads a multi-version snapshot established by the transaction's first consistent read and sets no locks. `SELECT ... FOR UPDATE`, `SELECT ... FOR SHARE`, `UPDATE` and `DELETE` are **locking reads**: they ignore that snapshot, read the latest committed version and lock the index records they touch. So if another session commits a change after your first plain read, the plain `SELECT` keeps showing the old value while `FOR UPDATE` shows the new one, and an `UPDATE` applies to the new one without any error. InnoDB does not abort the write the way some engines' snapshot isolation does, so a read-modify-write must lock first or use a relative `UPDATE`.

code

sql · 16 lines
sql
CREATE TABLE stock (sku_id INT PRIMARY KEY, qty INT NOT NULL) ENGINE=InnoDB;
INSERT INTO stock VALUES (42, 10);

-- Session A
START TRANSACTION;
SELECT qty FROM stock WHERE sku_id = 42;             -- 10: this read fixes the snapshot

-- Session B, autocommit on
UPDATE stock SET qty = 7 WHERE sku_id = 42;

-- Session A again
SELECT qty FROM stock WHERE sku_id = 42;             -- still 10 (snapshot)
SELECT qty FROM stock WHERE sku_id = 42 FOR UPDATE;  -- 7 (latest committed, now locked)
UPDATE stock SET qty = qty - 1 WHERE sku_id = 42;    -- writes 6
SELECT qty FROM stock WHERE sku_id = 42;             -- 6: its own change is visible
COMMIT;

go deeper

for a junior

Remember that InnoDB defaults to REPEATABLE READ and that a plain SELECT and a SELECT ... FOR UPDATE are different kinds of read.

for a middle

Explain when the snapshot is fixed, why locking reads and DML read the newest committed version, and walk through a timeline where the two disagree.

for a senior

Show how this produces lost updates in read-compute-write code, and fix it with FOR UPDATE or a relative UPDATE checked by affected rows.

for a principal

Weigh where a codebase should rely on snapshot reads versus explicit locking, and how to make that rule reviewable rather than tribal.

## Two kinds of read inside one InnoDB transaction InnoDB, MySQL's default transactional storage engine, answers reads in two different ways, and at its default isolation level, `REPEATABLE READ` (the server variable `transaction_isolation` defaults to `REPEATABLE-READ`), the two can disagree inside a single transaction. - **Consistent (nonlocking) read**: a plain `SELECT`. InnoDB uses multi-versioning to show each row as it was at a point in time, rebuilding an older version from undo information when the current one is too new. It sets no locks, so other sessions can change those rows while you read them. - **Locking read**: `SELECT ... FOR UPDATE`, `SELECT ... FOR SHARE` (the current spelling of the older `LOCK IN SHARE MODE`) and the row search that `UPDATE` and `DELETE` perform. These read the **latest committed version** and lock the index records they reach. If a row was changed by a transaction that has not committed yet, the locking read waits for that transaction to end. The manual puts the consequence plainly: the snapshot applies to `SELECT` statements within a transaction, not necessarily to DML statements. ## When the snapshot is fixed At `REPEATABLE READ`, every consistent read in a transaction reads the snapshot established by the **first consistent read**, not by `START TRANSACTION`. 1. `START TRANSACTION` (or `BEGIN`) opens the transaction but takes no snapshot. 2. The first plain `SELECT` against an InnoDB table fixes the timepoint; changes committed by others after it stay invisible to later plain reads. 3. `START TRANSACTION WITH CONSISTENT SNAPSHOT` takes the snapshot at once, which has the same effect as `START TRANSACTION` followed by a `SELECT` from any InnoDB table. At any level other than `REPEATABLE READ` the clause is ignored and a warning is raised. 4. To see newer data through plain reads you commit and start a new transaction. At `READ COMMITTED`, by contrast, each consistent read sets and reads its own fresh snapshot, so the divergence described here mostly disappears. ## What each statement sees | Statement | Version it reads | Locks it sets | |---|---|---| | plain `SELECT` | snapshot from the first consistent read | none | | `SELECT ... FOR SHARE` | latest committed | shared locks on the index records it reads | | `SELECT ... FOR UPDATE` | latest committed | exclusive locks, as an `UPDATE` would set | | `UPDATE`, `DELETE` | latest committed | exclusive locks on the records the search encounters | The manual's own example shows the edge: `SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz'` returns 0, and the next statement, `DELETE FROM t1 WHERE c1 = 'xyz'`, deletes several rows another transaction has just committed. Once your transaction has updated such rows, its own later plain `SELECT` does see them, because a consistent read always includes the transaction's own earlier changes. That mix can show a table state that never existed: the rows you changed at their newest version, every other row at the old snapshot. ## A worked timeline ```sql -- Session A START TRANSACTION; SELECT qty FROM stock WHERE sku_id = 42; -- 10, and this read fixes the snapshot -- Session B commits: UPDATE stock SET qty = 7 WHERE sku_id = 42; SELECT qty FROM stock WHERE sku_id = 42; -- still 10 SELECT qty FROM stock WHERE sku_id = 42 FOR UPDATE; -- 7, and the row is now X-locked UPDATE stock SET qty = qty - 1 WHERE sku_id = 42; -- writes 6 SELECT qty FROM stock WHERE sku_id = 42; -- 6, its own change COMMIT; ``` Nothing in that sequence raises an error. PostgreSQL at the same level would abort the `UPDATE` with a serialization failure; InnoDB simply applies it to the newest version. ## What this means for application code - **The lost-update trap.** Code that reads a value with a plain `SELECT`, computes in the application and writes back an absolute value (`SET qty = 9`) silently overwrites a change another session committed in between. InnoDB's `REPEATABLE READ` does not detect that conflict. - **Lock before you compute.** Read the row with `SELECT ... FOR UPDATE` so you see the newest value and hold an exclusive lock until commit. - **Or make the write relative.** `UPDATE stock SET qty = qty - 1 WHERE sku_id = 42 AND qty >= 1` reads and changes the newest version in one statement; check the affected-row count to know whether stock was available. - **Do not mix the two styles casually.** The manual advises against mixing locking statements with nonlocking `SELECT`s in one `REPEATABLE READ` transaction, noting that such code typically wants `SERIALIZABLE`, where InnoDB turns plain `SELECT`s into `SELECT ... FOR SHARE` when autocommit is disabled. - **Consistent reads ignore locks.** At `REPEATABLE READ` and `READ COMMITTED` a plain `SELECT` is not blocked by another session's `FOR UPDATE` lock; old row versions cannot be locked at all, they are rebuilt from undo. The general idea of a per-transaction snapshot belongs with the engine-neutral material on isolation levels; what is specific to InnoDB is that its DML and locking reads step outside the snapshot instead of failing.

  • How do you make a stock decrement safe in InnoDB without changing the isolation level?
    Either read the row with `SELECT ... FOR UPDATE` before computing, which returns the newest committed value and holds an exclusive lock until commit, or skip the read and run `UPDATE stock SET qty = qty - 1 WHERE sku_id = 42 AND qty >= 1`, then check the affected-row count. Both act on the latest version; a plain `SELECT` followed by an absolute write does not.
  • What does START TRANSACTION WITH CONSISTENT SNAPSHOT change in InnoDB?
    It establishes the consistent-read snapshot immediately instead of at the first plain `SELECT`, the same as `START TRANSACTION` followed by a read from any InnoDB table. It only has that effect at `REPEATABLE READ`; at other levels the clause is ignored with a warning. It does not change what locking reads or DML see.
  • Why can an InnoDB consistent read show a table state that never existed?
    A consistent read sees the transaction's own earlier changes at their newest version but every other row as of the snapshot. If your `UPDATE` touched rows another session had just committed, your next plain `SELECT` shows those rows updated alongside older versions of untouched rows, a combination no single moment ever held.

  • A plain SELECT in InnoDB takes shared locks on every row it reads.
  • START TRANSACTION by itself fixes the REPEATABLE READ snapshot.
  • InnoDB's UPDATE computes from the transaction's snapshot values.
  • InnoDB aborts a REPEATABLE READ update when another session changed the row.
  • SELECT ... FOR UPDATE reads from the same snapshot as an earlier plain SELECT.
open as a page

In MySQL InnoDB at REPEATABLE READ, what do record, gap and next-key locks cover, and what does a range SELECT ... FOR UPDATE lock?

level: middleimportance: must knowfreq 75%

basics

~20 s

InnoDB locks index records: a record lock covers one entry, a gap lock the space between entries, and a next-key lock both an entry and the gap before it, so a range FOR UPDATE blocks inserts anywhere in the scanned range.

open as a page

Why do two MySQL InnoDB transactions that each run SELECT ... FOR UPDATE for a not-yet-present sku_id 42 on stock_reservation's non-unique index, then INSERT that row, deadlock with error 1213, and what fixes it?

level: seniorimportance: must knowfreq 60%

basics

~20 s

Both FOR UPDATE searches find no sku 42 entry and take compatible gap locks on the same index gap; each INSERT then needs an insert intention lock the other's gap lock blocks, so InnoDB detects the cycle and rolls one back.

open as a page

In MySQL 8.4 InnoDB, what does innodb_autoinc_lock_mode control, and why is its default 2 (interleaved) rather than 1 (consecutive)?

level: middleimportance: should knowfreq 35%

basics

~20 s

innodb_autoinc_lock_mode chooses how InnoDB hands out AUTO_INCREMENT values: 0 and 1 use a statement-long table-level AUTO-INC lock for some or all inserts, while 2, the 8.4 default, uses none, trading consecutive per-statement values for concurrency.

open as a page

A MySQL service logs InnoDB's ERROR 1205 Lock wait timeout exceeded; what state is its transaction left in, and how do you find the blocking session?

level: seniorimportance: should knowfreq 50%

basics

~10 s

By default InnoDB rolls back only the timed-out statement, leaving the transaction open with its earlier locks; sys.innodb_lock_waits, built on performance_schema.data_lock_waits, names the blocking session, often an idle one holding an uncommitted transaction.

open as a page

Should a team switch MySQL's transaction_isolation from InnoDB's default REPEATABLE-READ to READ-COMMITTED fleet-wide, and what does that trade in locking and correctness?

level: principalimportance: should knowfreq 30%

basics

~20 s

Sometimes: READ COMMITTED drops InnoDB's gap locking for searches and releases locks on non-matching rows, cutting lock waits and gap-related deadlocks, but it takes a fresh snapshot per statement and stops protecting range checks against concurrent inserts.

open as a page