Skip to content
Tutorials Center
My learning

Topics

Lesson 58 of 75 ยท Transactions and Concurrency

Locking, Gap Locks and Deadlocks

Learn InnoDB locking: row, gap and next-key locks, SELECT FOR UPDATE, NOWAIT and SKIP LOCKED, lock wait timeouts, reading deadlock reports and retrying safely.

MVCC lets plain reads run without locks, but writes still have to take turns. When two transactions want to change the same row, one waits for the other. InnoDB's locking rules decide who waits, for how long, and what else gets blocked along the way. Understanding them explains the mysterious "why is my insert stuck?" moments, and it helps you design transactions that rarely collide.

This lesson covers record, gap and next-key locks, locking reads (SELECT ... FOR UPDATE and FOR SHARE), the NOWAIT and SKIP LOCKED modifiers, lock wait timeouts, how to read a deadlock report, and the retry pattern every application needs. Locks need several sessions, so the examples are two-session timelines to replay in two mysql windows, plus two single-session SQL models you can run here. Read Isolation Levels and MVCC first.

Three lock shapes

Record lock Locks one index entry.
Gap lock Locks the empty space between entries.
Next-key lock A record lock plus the gap before it.

Locks live on index entries, not on table rows.

1 / 3

Locks live on index entries

InnoDB does not lock "rows" in an abstract sense. It locks index records. A statement that updates rows found through an index locks the matching entries of that index (and the matching entries in the clustered primary key). A statement that finds rows with no usable index must scan the whole table and, at REPEATABLE READ, ends up locking every record it passes. That is why a missing index can turn "update one customer" into "block everybody".

There are two basic modes. A shared lock (S) lets several transactions read-and-hold the same record, and an exclusive lock (X) allows only one holder and conflicts with everything else. Two S locks are compatible; an X lock conflicts with S and X. UPDATE and DELETE take X locks on the records they touch, and INSERT takes an X lock on the new record.

Record, gap and next-key locks

Suppose an index on id has the entries 10, 20 and 30. InnoDB's locks fall into three kinds:

Lock What it protects
Record lock the single index entry, such as id 20
Gap lock the open interval between entries, such as (10, 20): no new entries can appear there
Next-key lock a record lock on an entry plus a gap lock on the gap before it, such as (10, 20]

At REPEATABLE READ, a locking read or write that scans a range takes next-key locks over everything it examines. That is how InnoDB stops phantoms from appearing in locking reads: nobody can insert into a locked gap. The timeline below shows the effect:

Step Session A (REPEATABLE READ) Session B
1 START TRANSACTION;
2 SELECT * FROM t WHERE id BETWEEN 11 AND 19 FOR UPDATE; (no rows match)
3 INSERT INTO t (id) VALUES (15); waits
4 COMMIT; the insert now completes

Session A found nothing, yet it holds a gap lock on (10, 20), so session B must wait. Gap locks never conflict with each other (two sessions can hold the same gap), they only block inserts into the gap. A few facts follow from this:

  • A unique lookup that finds its row, such as WHERE id = 20 on a unique index, needs only a record lock, with no gap lock.
  • A lookup on a unique index that finds nothing locks the gap where the value would be.
  • A range or non-unique lookup takes next-key locks, including the gap after the last matching entry. Searching for ids above the highest value locks the "supremum" gap, so every new insert at the end of the table waits. This is a classic cause of insert stalls.
  • At READ COMMITTED, gap locks are not used for searching and scanning, which is the main reason people switch the level on write-heavy systems.

To see the gaps in your own data, the model below lists them. This is a stand-in: the query only computes which intervals exist between the entries; locking itself needs InnoDB.

SQL Model: index entries and the gaps between them

Press Run (or Ctrl + Enter) to see the result here.

The result lists the next-key ranges (-infinity, 10], (10, 20] and (20, 30], plus the open gap after 30. A locking search for id 15 that finds nothing would hold the gap inside (10, 20), and an insert of 15 would wait.

Locking reads

A plain SELECT reads a snapshot and takes no locks. When you read a value in order to change it, you want the row held, so you use a locking read:

SQL
START TRANSACTION;

SELECT stock FROM products WHERE id = 42 FOR UPDATE;   -- X lock on the row
-- application checks stock >= 1
UPDATE products SET stock = stock - 1 WHERE id = 42;

COMMIT;

FOR UPDATE takes exclusive locks on the rows it reads and always sees the latest committed data. A second transaction that runs the same SELECT ... FOR UPDATE waits until the first one commits, then reads the new stock value. That removes the classic race where two buyers both read stock 1 and both subtract. (When the whole decision fits into one statement, a conditional UPDATE products SET stock = stock - 1 WHERE id = 42 AND stock >= 1 is even simpler, then check the affected-row count; Project: E-commerce Orders and Inventory uses both.)

FOR SHARE (older spelling LOCK IN SHARE MODE) takes shared locks: others can read and also take shared locks, but nobody can modify the rows until you finish. Use it when you must be sure a row does not change or disappear while you do related work, such as checking that a parent row exists before you insert a child (foreign keys do this internally).

Both clauses make sense only inside a transaction. With autocommit on, the lock is released the moment the statement ends.

NOWAIT and SKIP LOCKED

By default a locking read waits up to innodb_lock_wait_timeout seconds. Two modifiers (MySQL 8.0 and later) change that:

SQL
SELECT * FROM jobs WHERE state = 'ready' ORDER BY id LIMIT 1 FOR UPDATE NOWAIT;
-- ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately

SELECT * FROM jobs WHERE state = 'ready' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
-- returns the first ready job that nobody else has locked

NOWAIT fails at once if any needed row is locked, so you can tell the user "someone else is editing this" instead of freezing. SKIP LOCKED quietly ignores locked rows, which is exactly what a job queue wants: many workers each claim a different job without blocking each other. It is the right tool for work queues and the wrong one for consistent reports, because the result is intentionally incomplete.

The model below imitates two workers: each claims the first job that is not yet claimed, and the second therefore gets the next one. The claimed_by column stands in for the row lock.

SQL Model: two workers claim different jobs (SKIP LOCKED idea)

Press Run (or Ctrl + Enter) to see the result here.

Worker w1 gets job 1, w2 gets job 2, and job 3 is still free. With real concurrency the claim and the check must be one atomic step, which is what FOR UPDATE SKIP LOCKED inside a transaction provides.

Lock waits and timeouts

When a transaction needs a lock held by another, it waits. If the wait exceeds innodb_lock_wait_timeout (50 seconds by default), the statement fails with error 1205:

TEXT
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Only the statement is rolled back by default, not the transaction (unless you set innodb_rollback_on_timeout), so the earlier locks are still held and the application must roll back or retry. Typical causes are a forgotten open transaction (a session that updated a row and then idled), a long batch job, or a missing index that widens the locks.

To see who is waiting for whom, query the lock tables:

SQL
SELECT * FROM sys.innodb_lock_waits\G

This view joins performance_schema.data_locks and data_lock_waits and shows the waiting query, the blocking query, how long it has waited and a ready-made KILL statement for the blocker. SHOW PROCESSLIST and information_schema.INNODB_TRX (look for old trx_started times) help to find the sleeper that holds the lock (The Data Dictionary, information_schema and sys Schema).

Deadlocks

A deadlock is a cycle of waits: A holds a lock B needs, and B holds a lock A needs. Neither can ever proceed.

sequenceDiagram
    participant A as Transaction A
    participant R1 as Row 1
    participant R2 as Row 2
    participant B as Transaction B
    A->>R1: UPDATE row 1 (locks it)
    B->>R2: UPDATE row 2 (locks it)
    A->>R2: UPDATE row 2 (waits for B)
    B->>R1: UPDATE row 1 (waits for A)
    Note over A,B: Cycle detected, InnoDB rolls back one transaction

Each transaction holds one row and waits for the other's row, so InnoDB must break the cycle.

InnoDB detects deadlocks immediately (innodb_deadlock_detect = ON, the default), picks the transaction that has done the least work as the victim, rolls it back completely, and returns error 1213:

TEXT
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

The other transaction continues. Deadlocks are not bugs in MySQL, and on a busy system you cannot eliminate them completely: you reduce them and you handle them.

Reading the deadlock report

SQL
SHOW ENGINE INNODB STATUS\G

Look for the section LATEST DETECTED DEADLOCK. It lists both transactions, the statement each was running, the locks each held and the lock each waited for, and which transaction was rolled back. For a history of all deadlocks, set innodb_print_all_deadlocks = ON so each one is written to the error log. The report shows index names and lock_mode X locks rec but not gap lines, which tell you whether a gap lock was involved, usually a clue that a lower isolation level or a better index would help.

Reducing deadlocks

  • Access rows in a consistent order. If every transaction updates account rows in ascending id order, the picture above cannot happen. Sort the ids before you lock them.
  • Keep transactions short and commit promptly.
  • Index the columns you filter on, so only the intended rows are locked.
  • Lock what you need early with a single SELECT ... FOR UPDATE for all the rows, instead of discovering them one by one.
  • Consider READ COMMITTED if gap locks are the cause (Isolation Levels and MVCC).
  • Avoid mixing many unique-key inserts and range updates in one transaction on the same table: duplicate-key checks take shared locks that can deadlock with other inserts.

Always retry

Because deadlock victims are normal, application code must be ready to rerun the whole transaction when it receives error 1213 (and often 1205). Rerun the entire transaction, not only the failed statement, because InnoDB has already rolled the whole thing back. A pattern in pseudocode:

TEXT
repeat up to 3 times:
    begin transaction
    run the statements
    commit
    on error 1213 or 1205: roll back, wait a short random time, try again
    on any other error: roll back and give up

A little random delay (jitter) prevents the two transactions from colliding again in lockstep. Many frameworks have a built-in "retry on deadlock" option; see how the Laravel and PDO helpers do it in MySQL from PHP with PDO.

What ran live here

Two small SQL models ran in the in-browser engine: one computes the gaps between index entries, the other imitates two job-queue workers. Real locking (FOR UPDATE, gap and next-key locks, NOWAIT, SKIP LOCKED, timeouts and deadlocks) is MySQL server behavior and appears only as read-only timelines and code.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Session A runs SELECT ... WHERE id BETWEEN 11 AND 19 FOR UPDATE at REPEATABLE READ and finds no rows. Session B then inserts id 15. What happens?

    Locking reads take next-key and gap locks at REPEATABLE READ. The gap between 10 and 20 is locked, so inserts into it wait until A commits.
  2. 2. What is SKIP LOCKED mainly used for?

    SKIP LOCKED ignores rows locked by others, so workers pick different jobs. Because the result is intentionally incomplete, it is a poor fit for reports.
  3. 3. InnoDB reports error 1213. What should the application do?

    The victim of a deadlock is rolled back completely, so all of its statements must be repeated. A small random wait makes a repeat collision less likely.
  4. 4. Which habit most directly reduces deadlocks between transactions that update several rows?

    A deadlock needs a cycle of waits. If all transactions take locks in one global order, no cycle can form.
  5. 5. Where do you find the two statements involved in the most recent deadlock?

    That section lists both transactions, their statements, held and awaited locks, and the victim. Enable innodb_print_all_deadlocks to log every deadlock.

Summary

  • InnoDB locks index records: record locks on entries, gap locks on the space between them, next-key locks as both.
  • SELECT ... FOR UPDATE and FOR SHARE lock the rows they read; plain SELECT takes no locks.
  • NOWAIT fails immediately on a lock conflict; SKIP LOCKED ignores locked rows, ideal for job queues.
  • A lock wait beyond innodb_lock_wait_timeout gives error 1205; investigate with sys.innodb_lock_waits and INNODB_TRX.
  • A deadlock gives error 1213 and rolls the victim back; read SHOW ENGINE INNODB STATUS and retry the whole transaction.
  • Fewer deadlocks come from short transactions, good indexes, consistent row order and sometimes READ COMMITTED.

Next: Users, Privileges and Roles.