Skip to content

Why are we getting deadlocks when every query is indexed?

Last updated

View as Markdown

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.

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 READlocks it held
SELECT ... WHERE k = 20none
SELECT ... WHERE k = 20 FOR UPDATE, key existsrecord lock, no gap
SELECT ... WHERE k = 15 FOR UPDATE, key missinggap lock, no record lock
SELECT ... WHERE k > 15 FOR UPDATErecord and gap locks across the range
the same equality, on a non-unique indexrecord 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.

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

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:

-- 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

Section titled “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:

-- 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

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.

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

-- 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:

statementrecord locks it held
UPDATE t SET note='x' WHERE k = 20two: uk_k and PRIMARY
UPDATE t SET note='x' WHERE id = 2one: PRIMARY
UPDATE t SET k = 21 WHERE id = 2one: 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:

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:

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.

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 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 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:

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:

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.

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.

DROP DATABASE gapdemo;
  • Why do deadlocks happen when every query uses an index?

    Indexing is not the cause; a locking read for a key that is not there is. Under REPEATABLE READ that read takes a gap lock rather than a row lock, and an insert into that gap has to wait for any gap lock another transaction holds. Two transactions doing it in the same gap wait on each other. Measured on MySQL 8.4.11 in a container, 2026-09-20, removing only the two locking reads and running the same two inserts in the same interleaving committed both.

  • What is a gap lock?

    A lock on the space between two index entries rather than on a row. Its only job is to stop a row appearing there, which the MySQL manual calls purely inhibitive. Measured on MySQL 8.4.11 in a container, 2026-09-20, it appears in SHOW ENGINE INNODB STATUS as 'lock_mode X locks gap before rec' and in performance_schema.data_locks with a LOCK_MODE of X,GAP.

  • Does a locking read on an indexed column always take a gap lock?

    No, and this is where the rule is usually stated too broadly. Measured on MySQL 8.4.11 in a container, 2026-09-20, a SELECT ... FOR UPDATE on a unique index for a key that exists took a record lock and no gap lock; the same statement for a key that does not exist took a gap lock and no record lock. A plain SELECT took no lock at all at either isolation level.

  • How do I read the deadlock report?

    SHOW ENGINE INNODB STATUS prints a LATEST DETECTED DEADLOCK section with both transactions, the statement each was running, the lock each held and the lock each was waiting for. The lock lines name the index, so the access path is in the output rather than something you work out. Read it as a cycle: the two waiting locks are the deadlock, and the held locks are what they are waiting for.

  • Are deadlocks something to fix or something to handle?

    Both, in that order. InnoDB detects them in milliseconds and rolls one transaction back, so an application that retries the whole transaction survives them. That retry is not optional, because some deadlocks are inherent to concurrent access. Then fix the ones that are frequent enough to cost throughput.