skip to content

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%

answer

  1. gap locks do not conflict
  2. inserts need insert intention
  3. each waits on the other's gap
  4. read LATEST DETECTED DEADLOCK
  5. lock one parent row instead

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.

solid answer

~50 s

At InnoDB's default `REPEATABLE READ`, `SELECT ... WHERE sku_id = 42 FOR UPDATE` on a non-unique index that holds no 42 yet finds nothing and locks the gap where 42 would go. Gap locks are purely inhibitive and do not conflict with each other, so both transactions get one on the same gap. Each `INSERT` of a sku 42 row must first set an **insert intention lock** in that gap, which conflicts with the *other* transaction's gap lock: A waits for B, B waits for A. With `innodb_deadlock_detect` on (the default), InnoDB spots the cycle at once, rolls back the smaller transaction entirely with `ERROR 1213 (40001)` and lets the other continue. `SHOW ENGINE INNODB STATUS` shows it under `LATEST DETECTED DEADLOCK`. The fix is to serialise on one existing row, such as `SELECT ... FROM inventory WHERE sku_id = 42 FOR UPDATE`, and to keep retrying the whole transaction on 1213.

code

plaintext · 18 lines
plaintext
------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
...
INSERT INTO stock_reservation (sku_id, order_id, qty) VALUES (42, 9001, 1)
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 12 page no 5 n bits 80 index idx_sku of table `shop`.`stock_reservation` trx id 51007 lock_mode X locks gap before rec
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12 page no 5 n bits 80 index idx_sku of table `shop`.`stock_reservation` trx id 51007 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
...
INSERT INTO stock_reservation (sku_id, order_id, qty) VALUES (42, 9002, 1)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 12 page no 5 n bits 80 index idx_sku of table `shop`.`stock_reservation` trx id 51008 lock_mode X locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12 page no 5 n bits 80 index idx_sku of table `shop`.`stock_reservation` trx id 51008 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

go deeper

for a junior

Remember that error 1213 means InnoDB found a deadlock and rolled one whole transaction back, so the transaction must be retried.

for a middle

Explain why two gap locks coexist but an insert intention lock waits on another transaction's gap lock.

for a senior

Read LATEST DETECTED DEADLOCK to name the index and lock modes, then restructure the transaction around a single parent-row lock and keep a full retry.

for a principal

Decide where per-entity serialisation belongs in the data model, and set deadlock-rate monitoring and retry policy as team standards.

## The workload Two order-placement transactions run the same code against a MySQL table `stock_reservation (id BIGINT AUTO_INCREMENT PRIMARY KEY, sku_id INT, order_id BIGINT, qty INT, KEY idx_sku (sku_id))`. Each one checks what is already reserved for the SKU, then reserves more. The trouble shows up when the first two orders for a new SKU arrive together, so no sku 42 row exists yet: ```sql START TRANSACTION; SELECT COALESCE(SUM(qty), 0) FROM stock_reservation WHERE sku_id = 42 FOR UPDATE; -- application compares the sum with available stock INSERT INTO stock_reservation (sku_id, order_id, qty) VALUES (42, 9001, 1); COMMIT; ``` Run singly it works. Run concurrently, one of the two fails with `ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction`. ## Which locks form the cycle The server is at InnoDB's default isolation level, `REPEATABLE READ`. 1. **A's locking read.** The search on the non-unique `idx_sku` finds no sku 42 entry. To keep a phantom 42 out, it locks the gap where 42 would go, the space before the next key in the index. 2. **B's locking read.** B asks for a lock on the same gap and gets it at once: **gap locks are purely inhibitive and do not conflict with each other**, so A and B now both hold a lock on that gap. 3. **A's INSERT.** Before inserting, InnoDB sets an **insert intention lock**, a gap lock that signals the intent to insert. It conflicts with gap and next-key locks other transactions hold on that gap, so A waits for B. 4. **B's INSERT.** B needs the same kind of lock in the same gap and waits for A. Neither can proceed: a deadlock. No row was ever locked in the opposite order; the cycle is made entirely of gap locks that were compatible when taken and blocking when an insert arrived. Once sku 42 rows exist, the second `FOR UPDATE` usually queues behind the first one's next-key locks on those records instead, which is why this deadlock clusters on the first rows for a key and on searches that come back empty. ## How InnoDB resolves it - **Detection.** With `innodb_deadlock_detect` on (the default), InnoDB finds the cycle immediately, without waiting for any timeout; `innodb_lock_wait_timeout` (default 50 seconds) plays no part. - **Victim choice.** InnoDB tries to roll back a small transaction, where size is the number of rows inserted, updated or deleted. - **What the victim gets.** The **whole transaction** is rolled back and its locks released; the error is 1213 with SQLSTATE `40001`. Retry the entire transaction, not the last statement. - **Very long wait lists.** If the wait-for list passes 200 transactions, InnoDB treats it as a deadlock and the status output says `TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH`. ## Reading the evidence `SHOW ENGINE INNODB STATUS` keeps only the most recent deadlock, under `LATEST DETECTED DEADLOCK`. For each transaction it prints the statement, `HOLDS THE LOCK(S)` and `WAITING FOR THIS LOCK TO BE GRANTED`, then `*** WE ROLL BACK TRANSACTION (n)`. The signature of this deadlock is a held gap lock on `index idx_sku`, printed as `lock_mode X locks gap before rec` (or as plain `lock_mode X`, a next-key lock, when the search also locked a record), and a waiting request of `lock_mode X locks gap before rec insert intention waiting` on the same index. Because only the latest deadlock is kept, turn on `innodb_print_all_deadlocks` (default `OFF`, dynamic) to write every one to the error log, and watch the `lock_deadlocks` counter in `INFORMATION_SCHEMA.INNODB_METRICS` for the rate. ## Fixing it | Change | Effect on this deadlock | Cost | |---|---|---| | Lock one parent row first: `SELECT qty FROM inventory WHERE sku_id = 42 FOR UPDATE` on the primary key | Orders for one SKU queue on a single record lock; no shared gap is ever held by two of them | Orders for the same SKU serialise, which is the intent | | Run the transaction at `READ COMMITTED` | Searches take no gap locks, so this cycle cannot form | The range check no longer stops a concurrent insert, so stock can be oversold unless something else serialises it | | Turn off `innodb_deadlock_detect` | The cycle remains: a waiter fails only when `innodb_lock_wait_timeout` expires, and the locks clear only once a transaction rolls back | Stalls of up to the timeout; meant for very high-concurrency hot-lock cases | The parent-row lock is the usual answer: a unique-key equality on an existing row takes a record lock without a gap lock, and the reservation check then runs while holding it. Whatever the fix, application code must still catch 1213 and replay the whole transaction, because InnoDB documents deadlocks as normal on busy servers. The theory of wait-for graphs and generic retry design is covered with deadlock detection and avoidance in general; what is specific here is InnoDB's gap and insert intention locks and the way its status output names them.

  • Why does locking the inventory row for the SKU first remove the deadlock?
    `SELECT qty FROM inventory WHERE sku_id = 42 FOR UPDATE` on the primary key finds one existing row and takes only a record lock. The second order waits there before it has taken any gap lock on `stock_reservation`, so the first one's insert meets no conflicting gap lock and commits, and then the second proceeds. The SKU's orders serialise instead of interleaving.
  • Should you disable innodb_deadlock_detect when deadlocks are frequent?
    Usually not. Disabling it leaves the cycle in place and InnoDB only breaks it when a waiter hits `innodb_lock_wait_timeout`, 50 seconds by default, and that rolls back only the waiting statement unless `innodb_rollback_on_timeout` is on. The manual offers it for very high-concurrency systems where detection itself slows threads queued on one lock, not as a deadlock cure.
  • Why does InnoDB's status output sometimes show a deadlock you cannot find in your logs?
    `LATEST DETECTED DEADLOCK` keeps only the most recent deadlock, so an earlier one is overwritten. Enabling `innodb_print_all_deadlocks`, which is dynamic and off by default, writes every deadlock to the error log, and the `lock_deadlocks` counter in `INFORMATION_SCHEMA.INNODB_METRICS` gives the rate.

  • A deadlock needs two transactions to lock the same rows in opposite order.
  • Two transactions cannot hold gap locks on the same gap at once.
  • InnoDB waits innodb_lock_wait_timeout before resolving a deadlock by default.
  • After error 1213 only the failed INSERT is rolled back, so retry that statement.
  • SHOW ENGINE INNODB STATUS lists every deadlock since the server started.