# Why are we getting deadlocks when every query is indexed?

> **Quick answer:** A `SELECT ... FOR UPDATE` for a key that is not there locks
> the gap where it would go, not a row. Two transactions doing that in the same
> gap then each need to insert into a gap the other holds, and an insert waits
> for somebody else's gap lock. The index is where the lock lives, not the
> reason it exists.

## What is the problem?

The usual advice about deadlocks is to index the query, because an unindexed
`UPDATE` scans and locks more rows than it changes. That advice is correct, and
MySQL's own deadlock guidance gives it. Indexing reduces the number of locks; it
does not make a lock type more dangerous.

The predicate, access path, and isolation level affect the result together.
Measured with `performance_schema.data_locks` on a MySQL 8.4.11 container,
2026-09-20, against a table with a unique key `uk_k` holding values 10, 20, and
30:

| statement, at `REPEATABLE READ` | locks it held |
|---|---|
| `SELECT ... WHERE k = 20` | none |
| `SELECT ... WHERE k = 20 FOR UPDATE`, key exists | record lock, no gap |
| `SELECT ... WHERE k = 15 FOR UPDATE`, key missing | gap lock, no record lock |
| `SELECT ... WHERE k > 15 FOR UPDATE` | record and gap locks across the range |
| the same equality, on a non-unique index | record lock plus the next gap |

The short version of this rule gets three of those five cases wrong. A plain
`SELECT` takes no locks: repeated reads return the same rows because of the
transaction's read view, not because anything is locked. A found unique key is
locked as a record, with no gap, which is what the manual means by gap locking
not being needed to "search for a unique row". A missing unique key is the
opposite case: there is no record to lock, so the lock is the gap. That is the
case that deadlocks.

## The reproduction

MySQL 8.4.11, default `REPEATABLE-READ`. A table with a unique index and three
rows:

```sql
CREATE DATABASE IF NOT EXISTS gapdemo;
USE gapdemo;

CREATE TABLE t (
  id   INT PRIMARY KEY AUTO_INCREMENT,
  k    INT,
  note VARCHAR(20),
  UNIQUE KEY uk_k (k)
);
INSERT INTO t (k) VALUES (10), (20), (30);

SELECT @@transaction_isolation;
```

Now two transactions. Neither touches a row the other touches, both use the
unique index, and both look up a value that **does not exist**:

<!-- sql-check: skip this needs two concurrent sessions; both transactions and the resulting deadlock are quoted from a MySQL 8.4.11 container -->
```sql
-- session A                        -- session B
BEGIN;                              BEGIN;
SELECT * FROM t                     SELECT * FROM t
  WHERE k = 15 FOR UPDATE;            WHERE k = 17 FOR UPDATE;

INSERT INTO t (k) VALUES (16);      INSERT INTO t (k) VALUES (18);
```

Session B got:

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

Nothing overlaps at the row level. 15, 16, 17, and 18 are four different values,
and none of them existed. The collision is that 15 and 17 are both in the gap
between the index entries for 10 and 20, so both `SELECT ... FOR UPDATE`
statements locked that gap. Both inserts then needed to place a row inside it.

### The control, because the inserts are not the problem

Two inserts into the same gap do not deadlock on their own. That is deliberate
in InnoDB, and it is the first thing to rule out. The same two statements, with
the same interleaving and only the two locking reads removed:

<!-- sql-check: skip this needs two concurrent sessions; the result below is quoted from a MySQL 8.4.11 container -->
```sql
-- session A                        -- session B
BEGIN;                              BEGIN;
INSERT INTO t (k) VALUES (16);      INSERT INTO t (k) VALUES (18);
COMMIT;                             COMMIT;
```

On that MySQL 8.4.11 container, 2026-09-20, both committed, and `k` then read
10, 16, 18, 20, and 30. The deadlock therefore comes from the two locking reads,
not from the pair of inserts or the shared gap. Removing the reads removes it.

The MySQL 8.4 manual says why in two sentences that have to be kept apart:

> Gap locks in `InnoDB` are "purely inhibitive", which means that their only
> purpose is to prevent other transactions from inserting to the gap. Gap locks
> can co-exist.

> An insert intention lock is a type of gap lock set by `INSERT` operations
> prior to row insertion. This lock signals the intent to insert in such a way
> that multiple transactions inserting into the same index gap need not wait for
> each other if they are not inserting at the same position within the gap.

A gap lock coexists with another gap lock, which is why the two `FOR UPDATE`
statements both succeed. An insert intention coexists with another insert
intention, which is why the control above commits. Neither sentence exempts an
insert intention from an ordinary gap lock held by another transaction, and the
manual's own worked example shows that wait. That asymmetry causes the
deadlock.
[InnoDB Locking, MySQL 8.4 manual](https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html)

## Reading the report

`SHOW ENGINE INNODB STATUS` has the answer in a section headed
`LATEST DETECTED DEADLOCK`. This is the whole section from the run above,
untrimmed, because the parts people cut are the ones that prove the cycle:

```
LATEST DETECTED DEADLOCK
------------------------
2026-09-20 21:38:21 138727611414080
*** (1) TRANSACTION:
TRANSACTION 22776, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 921, OS thread handle 138728049669696, query id 74370 localhost root update
INSERT INTO t (k) VALUES (16)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 57 page no 5 n bits 72 index uk_k of table `gapdemo`.`t` trx id 22776 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 57 page no 5 n bits 72 index uk_k of table `gapdemo`.`t` trx id 22776 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 22777, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 922, OS thread handle 138728050726464, query id 74371 localhost root update
INSERT INTO t (k) VALUES (18)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 57 page no 5 n bits 72 index uk_k of table `gapdemo`.`t` trx id 22777 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 57 page no 5 n bits 72 index uk_k of table `gapdemo`.`t` trx id 22777 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
```

Read the two `WAITING FOR` blocks first. They show the deadlock. The other
blocks show what each transaction is waiting for. Then check four things:

**The index is named.** `index uk_k` tells you which access path took the lock,
which is the fastest route from a deadlock to a code change.

**`locks gap before rec`** means a gap lock rather than a row lock. In a deadlock
between statements that touch different rows, look here first. It names a lock
in the cycle, not the cause. The cause is the statement that took the lock, and
you still have to find that statement.

**The hex line says which gap.** `hex 80000014` is the first column of the index
entry the gap sits before, and `0x14` is 20. The report therefore states that
both transactions are fighting over the space before the entry for `k = 20`,
which is the gap holding 15, 16, 17, and 18. Both transactions print the
identical record, `heap no 3`.

**`insert intention waiting`** means that an insert is waiting for a gap. An
insert intention does not conflict with another insert intention, as the
control above demonstrates, but it does wait for an ordinary gap lock held by
another transaction. Each transaction here holds the gap the other's insert
needs, which forms the cycle.

Read it as soon as you can: the section holds only the most recent deadlock, and
the next one overwrites it.

## The other common shapes

**Opposite ordering.** Two transactions update the same two rows in different
orders. This is the textbook deadlock and the one the MySQL 8.4 manual names:
"A deadlock can occur when transactions lock rows in multiple tables (through
statements such as `UPDATE` or `SELECT ... FOR UPDATE`), but in the opposite
order." The same thing happens with two rows in one table.
[Deadlocks in InnoDB, MySQL 8.4 manual](https://dev.mysql.com/doc/refman/8.4/en/innodb-deadlocks.html)

<!-- sql-check: skip this needs two concurrent sessions -->
```sql
-- session A                        -- session B
UPDATE accounts SET bal = bal - 10  UPDATE accounts SET bal = bal - 10
  WHERE owner = 'alice';              WHERE owner = 'bob';
UPDATE accounts SET bal = bal + 10  UPDATE accounts SET bal = bal + 10
  WHERE owner = 'bob';                WHERE owner = 'alice';
```

Reproduced on MySQL 8.4.11 against an `accounts` table with a unique key on
`owner`. Two things in the report differ from the gap case above:

```
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS ... index uk_owner of table `gapdemo2`.`accounts` lock_mode X locks rec but not gap

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index uk_owner of table `gapdemo2`.`accounts` lock_mode X locks rec but not gap waiting
```

**`locks rec but not gap`**, not `locks gap before rec`. That phrase separates
the two causes: a real row contended by both transactions, versus empty space
contended by both. Read it before forming a theory.

The named index is `uk_owner`, the secondary index the `WHERE` clause used, not
`PRIMARY`. People often look at the primary key because that is where the row
lives, while the report points to the index used by the query.

**Secondary index plus primary key.** An `UPDATE` found through a secondary index
locks more places than one found through the primary key, so two statements
reaching the same row by different paths have more chances to collide. Measured
with `data_locks` on that MySQL 8.4.11 container, 2026-09-20, against the same
table:

| statement | record locks it held |
|---|---|
| `UPDATE t SET note='x' WHERE k = 20` | two: `uk_k` and `PRIMARY` |
| `UPDATE t SET note='x' WHERE id = 2` | one: `PRIMARY` |
| `UPDATE t SET k = 21 WHERE id = 2` | one: `PRIMARY` |

The count is what `data_locks` measures. It reports the locks a transaction
holds, not the order in which it took them. Nothing here says anything about
ordering in either direction. A page that gives you the order is using
information its own tool cannot see.

**Unique key checks.** A duplicate-key check takes a shared lock on the existing
entry, so two transactions can deadlock when each holds an uncommitted unique
value and then asks for the other's. Measured on that MySQL 8.4.11 container,
2026-09-20, session A inserted `k = 40` then `k = 41`, session B inserted 41 then
40, and A returned `ERROR 1213`. The report is a different shape from the gap
case:

```
INSERT INTO t (k) VALUES (40)
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS ... index uk_k of table `gapdemo`.`t` lock_mode X locks rec but not gap
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index uk_k of table `gapdemo`.`t` lock mode S waiting
```

`lock mode S waiting` appears instead of `insert intention waiting`, and no gap
lock appears in it. That difference matters for the fix below.

**Foreign keys.** An insert into a child table takes a shared lock on the parent
row. That lock is easy to miss because it is on a table the statement does not
name, so inspect the locks instead of inferring them. `performance_schema.data_locks`
shows every lock held or requested, by transaction, table, and index:

<!-- sql-check: skip this reads the locks held by an open transaction, so it needs the INSERT above it uncommitted; run on MySQL 8.4.11 -->
```sql
SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
FROM performance_schema.data_locks;
```

A single `INSERT INTO child VALUES (1, 1)` inside a transaction reported:

```
| OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_DATA |
| child       | NULL       | TABLE     | IX            | NULL      |
| parent      | NULL       | TABLE     | IS            | NULL      |
| parent      | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |
```

Three locks, two of them on a table the statement never mentions. `data_locks` is
the tool to use before the deadlock report because it works on a live
transaction rather than after the fact.

Its companion `performance_schema.data_lock_waits` gives the edges rather than
the nodes, one row per blocked request and the lock blocking it, with both
transactions named. It carries only identifiers, so join it back to
`data_locks` for readable results:

<!-- sql-check: skip this returns rows only while a lock wait is in progress; run on MySQL 8.4.11 -->
```sql
SELECT w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
       w.BLOCKING_ENGINE_TRANSACTION_ID   AS blocking_trx,
       l.OBJECT_NAME, l.INDEX_NAME, l.LOCK_MODE, l.LOCK_DATA
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks l
  ON l.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID;
```

One session holding `SELECT ... WHERE k = 15 FOR UPDATE` and one trying to
`INSERT` 16 into the same gap, on MySQL 8.4.11, 2026-09-20:

```
| waiting_trx | blocking_trx | OBJECT_NAME | INDEX_NAME | LOCK_MODE                | LOCK_DATA |
| 22800       | 22799        | t           | uk_k       | X,GAP,INSERT_INTENTION   | 20, 2     |
```

That single row shows the mechanism behind the deadlock at the top of this page,
with the cycle removed: one gap lock, one insert intention, and one wait. Two
such rows pointing at each other make a deadlock.

A deadlock is a cycle in this table. A single long wait is one row in it, and
that is the case the `LATEST DETECTED DEADLOCK` section never reports because
nothing has deadlocked yet.

## How do I stop it coming back?

**Retry on 1213, always, and retry the whole transaction.** InnoDB rolls one
transaction back entirely, so resuming from the failed statement resumes inside
a transaction that no longer exists. Retry from `BEGIN`, with a bounded count
and a little jitter, and make the body safe to run twice. The
[MySQL 8.4 manual](https://dev.mysql.com/doc/refman/8.4/en/innodb-deadlocks.html)
says so directly, read 2026-09-20: "even if your application logic is correct,
you must still handle the case where a transaction must be retried."

**Handle the timeout as well as the deadlock.** With `innodb_deadlock_detect`
turned off, the reproduction at the top of this page returned
`ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction`
instead of 1213, on that MySQL 8.4.11 container, 2026-09-20, with
`innodb_lock_wait_timeout` set to 3. The same cycle produced a different error
code and a wait that lasted as long as the timeout. Error handling that only
catches 1213 breaks when that server setting changes.

**Keep indexing the locking statements.** Narrowing what a statement scans
narrows what it locks, which reduces deadlocks rather than causing them. The
MySQL 8.4 manual's deadlock advice is to "create indexes on the columns used
in `SELECT ... FOR UPDATE` and `UPDATE ... WHERE` statements". Nothing on this
page argues against that. The index is where the lock lives, so it is where to
read the deadlock report.

**Order writes consistently.** Sort the rows a transaction will touch by
primary key before touching any of them. This eliminates the opposite-ordering
shape.

**Make transactions short.** Every extra statement adds time during which locks
are held. Keep network calls, model calls, and user input out of transactions.

**Insert rather than lock and insert.** `SELECT ... FOR UPDATE` on a row that
does not exist is the pattern that produced the deadlock above. `INSERT ... ON
DUPLICATE KEY UPDATE`, or an insert that catches the duplicate error, does the
same job without a locking read holding the gap first. On a duplicate unique
key, `ON DUPLICATE KEY UPDATE` still takes a next-key lock, which covers a gap.

**Consider `READ COMMITTED` for the workloads that suffer.** It does not take gap
locks for ordinary reads, which removes this class of deadlock. The reproduction
at the top of this page, unchanged except for
`SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED` in both sessions,
produced no deadlock at all on that MySQL 8.4.11 container, 2026-09-20, and both
inserts committed.

The qualifier matters because `READ COMMITTED` is not "no gap locks ever". The
[MySQL 8.4 manual](https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html)
says gap locking "is used only for foreign-key constraint checking and
duplicate-key checking" at that level, which is not none. Measured on MySQL
8.4.11, 2026-09-20, against `gapdemo.t` from the reproduction above, still
holding its three rows at `k` of 10, 20, and 30:

<!-- sql-check: skip this leaves a transaction open for a second session to read data_locks from; run on a MySQL 8.4.11 container -->
```sql
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
INSERT INTO t (k) VALUES (20) ON DUPLICATE KEY UPDATE id = id;
```

With that transaction still open, `performance_schema.data_locks` from another
session showed:

```
INDEX_NAME  LOCK_TYPE  LOCK_MODE        LOCK_DATA
PRIMARY     RECORD     X,REC_NOT_GAP    2
uk_k        RECORD     X                20, 2
```

The primary key lock says `REC_NOT_GAP`; the unique index lock does not, and that
absence is the gap. `READ COMMITTED` narrows where gaps are taken rather than
abolishing them.

It also does not remove deadlocks that never needed a gap. The duplicate-key
pair above, 40 and 41 inserted in opposite orders, deadlocked again with
`READ COMMITTED` set in both sessions on that MySQL 8.4.11 container,
2026-09-20, with the identical lock lines: `lock_mode X locks rec but not gap`
held, `lock mode S waiting`. Record locks, lock ordering, unique checks, and
foreign keys all survive the change.

That is a fix for one shape, but it changes behavior. `READ COMMITTED` gives
each statement a fresh snapshot instead of one per transaction, so a locking
read can see rows that were not there before. That changes what the application
can assume. It is a decision about invariants and retry behavior, not just a
tuning knob.

Check the settings you are relying on:

```sql
SHOW VARIABLES WHERE Variable_name IN (
  'transaction_isolation',
  'innodb_deadlock_detect',
  'innodb_lock_wait_timeout',
  'innodb_print_all_deadlocks'
);
```

Measured on a MySQL 8.4.11 container, 2026-09-20, all four at their defaults:

```
| innodb_deadlock_detect     | ON              |
| innodb_lock_wait_timeout   | 50              |
| innodb_print_all_deadlocks | OFF             |
| transaction_isolation      | REPEATABLE-READ |
```

`innodb_print_all_deadlocks` defaulting to `OFF` is the one to change. With it
off, the only record of a deadlock is the `LATEST DETECTED DEADLOCK` section,
which the next deadlock overwrites. A burst of deadlocks therefore leaves one
sample and no count. Turn it on to send every deadlock to the error log.

## Why does AI-generated code cause this?

The generated pattern is check then act. Ask for code that inserts a row if it is
not already there, and a common output is a `SELECT ... FOR UPDATE` followed by
an `INSERT`. It reads as the careful version, it is common in application code,
and on a row that does not exist it takes a gap lock and holds it until commit.

The second pattern is the transaction that grew. A model asked to make an
operation atomic wraps more statements in `BEGIN` and `COMMIT`, which is the
correct response to the request but lengthens every lock the operation takes.

The third is the one that looks like diligence: adding an index for each query in
a hot path. Every index a write maintains is another place where that write
takes a lock, so indexes chosen for reads change the lock footprint of writes
nobody was considering. The deadlock report may then name an index somebody
added last week to fix something else. That is a reason to know which indexes a
write touches, not a reason to remove them.

## Clean up

```sql
DROP DATABASE gapdemo;
```

## How DBGorilla helps

A deadlock report names two statements at one instant. What it does not tell you
is how often that pair collides, or what else was running. DBGorilla connects
read-only and gives your AI coding agent the workload by query fingerprint with
invocations and duration, plus activity samples showing sessions with their
state and what they were waiting on, sampled continuously. That lets you
count deadlocks rather than see one at a time. Engine
coverage is listed on the product pages. It surfaces and explains; it does not
write to your database.
[Get started free →](https://app.dbgorilla.com/signup)

## Related

- [How to read a MySQL EXPLAIN plan](https://www.dbgorilla.com/learn/mysql/how-to-read-a-mysql-explain-plan/)
- [Why is MySQL not using my index?](https://www.dbgorilla.com/learn/mysql/why-is-mysql-not-using-my-index/)
- [How to find slow queries in MySQL](https://www.dbgorilla.com/learn/mysql/how-to-find-slow-queries-in-mysql/)