Lesson 0006 · Internals & Performance

InnoDB locking & deadlocks

Plain reads are lock-free (Lesson 0005) — but the moment you write, or read for update, InnoDB takes locks on index records. Here's which locks, and why two transactions can deadlock.

~11 minRetrieval quiz + hands-on deadlockCheat sheet: here

In Lesson 0005 you learned that a plain SELECT takes no locks — it reads an MVCC snapshot. This lesson is about the other operations: UPDATE, DELETE, and locking reads (SELECT … FOR UPDATE / FOR SHARE). These do take locks, and understanding what they lock explains blocked queries, why indexing matters for writes too, and the dreaded deadlock.

The one idea: InnoDB locks index records, not rows

The keystone

InnoDB locks are placed on index records — always, even on the clustered index if you defined no secondary index (recall 0001: the table is the PK index). This single fact explains everything below: which rows get locked depends on which index the query walks, so a poorly-indexed WHERE locks far more than you intended. — MySQL Manual: "Record locks always lock index records, even if a table is defined with no indexes."

The three lock types

LockWhat it locksPurpose
record lockA single index record.Stop others updating/deleting that row.
gap lockThe gap between index records (or before the first / after the last).Stop others inserting into the gap.
next-key lockA record lock + the gap before it.Both at once — the default for scans.

— MySQL Manual: "A record lock is a lock on an index record… A gap lock is a lock on a gap between index records… A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record."

Why gaps exist — the phantom connection

Remember from 0005 that InnoDB's REPEATABLE READ prevents phantoms even for locking reads. This is how: by default InnoDB uses next-key locks for searches and index scans, so it locks not just the matching rows but the gaps around them — blocking the INSERTs that would create phantom rows. — MySQL Manual: "InnoDB uses next-key locks for searches and index scans, which prevents phantom rows."

Shared vs exclusive — and the two locking reads

S (shared)Lets the holder read the row; others may also hold S. Taken by SELECT … FOR SHARE.
X (exclusive)Lets the holder update/delete the row; blocks all other locks. Taken by UPDATE, DELETE, SELECT … FOR UPDATE.

A plain SELECT takes neither — it's the lock-free snapshot read from 0005. Reach for FOR UPDATE only when you'll write based on what you read (e.g. check-then-decrement inventory).

The deadlock

A deadlock is a cycle of waiting: each transaction holds a lock the other needs, so neither can proceed. — MySQL Manual: "A deadlock is a situation in which multiple transactions are unable to proceed because each transaction holds a lock that is needed by another one."

Txn A Txn B locks row 1 ───┐ locks row 2 wants row 2 ◀── waits │ waits ──▶ wants row 1 └──────── cycle: each waits on the other ────────┘ InnoDB detects the cycle → rolls back ONE (the "victim", error 1213)

InnoDB detects the cycle automatically and rolls back one transaction — the victim — releasing its locks so the other proceeds. The victim's application gets error 1213 (ER_LOCK_DEADLOCK). — MySQL Manual: "InnoDB detects the condition and rolls back one of the transactions (the victim)."

The rule: deadlocks are normal — retry them

A deadlock is not a bug to eliminate but a condition to handle: catch error 1213 and retry the transaction. Reduce their frequency by (1) keeping transactions small and short, (2) having every transaction acquire locks in the same order, and (3) indexing the columns in your WHERE so you lock few, precise records instead of scanning. — MySQL Manual: "you must still handle the case where a transaction must be retried"; keep transactions small, "use the same order of operations… create indexes on the columns used."

The indexing tie-back (0001 + 0004)

Because locks sit on the index records a query walks, an UPDATE … WHERE unindexed_col = ? must scan — and lock — every row it examines, not just the ones that match. A missing index doesn't just make reads slow (0004); it makes writes lock the whole table's worth of rows, turning a fast update into a concurrency bottleneck and a deadlock magnet.

Check yourself

Answer from memory — effortful recall is what builds retention. Feedback is immediate.

A record lock in InnoDB locks:

A record lock locks a single index record. InnoDB always locks index records — even the clustered index when no secondary index exists.

A gap lock exists specifically to:

Gap locks are "purely inhibitive" — their only job is to stop other transactions inserting into the gap, which is how phantoms are prevented.

A next-key lock is a combination of:

A next-key lock = a record lock on the index record PLUS a gap lock on the gap before it. It's InnoDB's default for searches and scans.

In REPEATABLE READ, next-key locks exist to prevent:

By locking the gaps around matched rows, next-key locks block the INSERTs that would create phantom rows — the locking-read counterpart to the MVCC snapshot from 0005.

When InnoDB detects a deadlock, it:

InnoDB automatically detects the cycle and rolls back one transaction (the victim, error 1213), freeing its locks so the other proceeds. Your app should retry.

A plain (nonlocking) SELECT in InnoDB takes:

Recall 0005: a plain SELECT is a consistent snapshot read and takes no locks. Locks come only from UPDATE/DELETE and the locking reads FOR UPDATE / FOR SHARE.

An UPDATE … WHERE on an unindexed column tends to lock:

Locks sit on the index records the query walks. With no usable index the UPDATE scans and locks every row it examines — a deadlock magnet. Index the WHERE column.

Hands-on: cause a deadlock, then read it

Open two mysql sessions (A and B) on the people table (PK id). You'll make each lock one row, then reach for the other's.

1 · Build the cycle

-- Session A
START TRANSACTION;
UPDATE people SET city = 'A1' WHERE id = 1;   -- A holds X lock on row 1

-- Session B
START TRANSACTION;
UPDATE people SET city = 'B2' WHERE id = 2;   -- B holds X lock on row 2

-- Session A — now wants row 2 (B has it) → A waits
UPDATE people SET city = 'A2' WHERE id = 2;

-- Session B — now wants row 1 (A has it) → CYCLE
UPDATE people SET city = 'B1' WHERE id = 1;
What you should observe

The instant B issues its last statement, InnoDB detects the cycle and one session gets ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction. The victim is rolled back; the survivor can COMMIT. That error is your cue to retry, not to panic.

2 · Read the autopsy

SHOW ENGINE INNODB STATUS\G

Find the LATEST DETECTED DEADLOCK section. It names both transactions, the exact SQL each was running, and which locks each held vs waited for — the whole crime scene. Being able to read this is a senior-level skill; bring me a real one and we'll walk it together. — MySQL Manual: "To view the last deadlock… use SHOW ENGINE INNODB STATUS."

Primary source — read this next

MySQL 8.0 Reference Manual — InnoDB Locking (record / gap / next-key / S / X), then Deadlocks in InnoDB for detection, the victim, and avoidance.

Where this goes next

You've now seen both halves of concurrency: lock-free snapshot reads (0005) and the locks that writes and locking-reads take (0006). Lesson 0007 turns to durability — how InnoDB makes a committed transaction survive a crash: the redo log, undo log, and the write-ahead-log discipline behind COMMIT.

Cheat sheet → Glossary → ← Lesson 0005 All lessons
I'm your teacher — paste me a LATEST DETECTED DEADLOCK block from your own DB and we'll identify who held what, who waited, and which index made the lock wider than it needed to be. Fuzzy on gap vs next-key, or when FOR UPDATE is worth it? Just ask.