What MySQL Locks Actually Lock

You run an UPDATE. Nothing errors, it just never returns. Forty-odd seconds later it throws a line at you, Deadlock found when trying to get lock. Nobody wrote the word lock. It showed up on its own.

InnoDB has more than one kind of lock, and the names are a mess. Let’s start with two withdrawals.

Without locks, one of two withdrawals goes missing

An account table holds one row with a balance of 100. Two requests arrive at almost the same moment. A reads 100, B reads 100. A subtracts 10 and writes back 90. B subtracts 10 and writes back 90. Both withdrawals succeed and the account is only 10 lighter. That is a lost update.

MySQL does not allow this. Before anyone changes a row, they have to take that row’s write lock. A write lock goes to one transaction at a time; anyone else who wants to change the same row waits in line.

Locks come in two broad kinds. A read lock is also called a shared lock, written S. A write lock is also called an exclusive lock, written X. Two S locks on the same row can coexist, so several transactions can read together without trouble. S against X, and X against X, block each other.

There is also a table-level intent lock, IS and IX. It locks no data. It just announces ahead of time that this transaction is about to take row locks on this table. When another transaction wants to lock the whole table, one glance at the intent lock tells it whether there would be a conflict, with no need to check row by row.

Row locks sit on indexes

This is the trap most people fall into. InnoDB row locks sit on index records. Which index your WHERE clause uses decides which records get locked.

UPDATE account SET balance = balance - 10 WHERE phone = '13800000000';

With no index on phone, InnoDB cannot find the specific record and has to scan the whole table. Every row it scans gets locked, even the ones that don’t match in the end. The bigger the table, the more records get locked, and the less data anyone else can touch. Build an index on phone and the same statement locks only the rows it hits.

When the WHERE condition can’t use an index at all, every row in the table falls inside the locking range, close to locking the table. Plenty of slow endpoints and deadlocks trace back here.

A plain SELECT takes no locks by default

Reads rarely wait on each other, because a plain SELECT goes down a different path. InnoDB keeps several versions of each row, and SELECT reads from a snapshot taken when the transaction started, so it may see an older version. Reading an older version needs no lock.

Add FOR UPDATE and that changes. This is a current read. It reads the latest committed data and puts write locks on those rows, held until the transaction ends. Until then, anyone who wants to change those rows waits for you.

SELECT * FROM account WHERE id = 1 FOR UPDATE;

FOR SHARE is similar, taking read locks instead. A lock is not released until the transaction commits, and this is easier to forget than the lock itself.

Gap locks, and a deadlock that looks baffling

Under REPEATABLE READ there is another kind of lock that locks the space between records, called a gap lock. InnoDB uses it to prevent phantom reads. Once you lock a range, another transaction that tries to insert a new row into that range gets blocked.

The pair below, run in two separate sessions, collides.

BEGIN;
SELECT * FROM account WHERE id = 5 FOR UPDATE;
INSERT INTO account (id, balance) VALUES (5, 100);

When no row with id 5 exists, that SELECT takes a gap lock. The row isn’t there yet; the lock lands on the gap first.

Which gap exactly depends on the nearest records on either side. Say the primary key already holds 1, 4, 7, 10. 5 falls between 4 and 7, so the lock covers (4, 7), and inserting 5 or 6 there is blocked. If 5 is smaller than every record, the gap runs from infimum to the smallest record. If it is larger than every record, the gap runs from the largest record to supremum. Looking up a value that exists takes no gap lock, only a record lock on that row.

A gap lock hangs off the next record after it. Locking (4, 7) really means setting a mark on record 7, saying the gap in front of it belongs to this transaction. A pure gap lock does not lock the records at either end, so the non-indexed columns of rows 4 and 7 can still be read and changed by other transactions.

Back to the two sessions. Both locked the same gap, and both now want to insert into it. Before an INSERT, the transaction requests an insert intention lock, meaning I want to put a row at this position. It does not conflict with another insert intention lock, but it does conflict with someone else’s gap lock. So each side waits for the other’s gap lock, the ring closes, and neither moves.

When InnoDB detects the deadlock it handles it, usually rolling back the transaction that changed the least and letting the other continue. The rolled-back one gets 1213.

REPEATABLE READ is MySQL’s default isolation level, and phantom reads and gap locks are tied to it. Drop the level to READ COMMITTED and gap locks largely disappear, so the wait range shrinks a lot. The cost is that two reads in the same transaction may return different results. Whether that trade is acceptable depends on the business.

How InnoDB detects a deadlock

Deadlock detection is not a scheduled task sweeping the whole database. The moment a transaction’s lock request cannot be granted and is about to enter the wait queue, InnoDB runs a check on the spot.

It treats the current state as a wait-for graph. Nodes are transactions, and T1 pointing at T2 means T1 is waiting for a lock T2 holds. InnoDB does not materialize the whole graph; it starts from the transaction that just asked and does a depth-first walk.

The walk follows the lock’s wait queue. It looks at who is ahead, who holds the lock, then at which lock each of those transactions is itself waiting for and who blocks them, recursing down. If the walk arrives back at the starting transaction, the waits form a cycle and the deadlock is confirmed.

Once a cycle is found, one victim is picked to roll back. The pick compares transaction weight, roughly the number of undo records and the number of locks held, and the cheaper one is more likely chosen. If the requesting transaction is the victim, it gets 1213 directly. If someone else is chosen, that one is rolled back and its client gets 1213.

The walk has a depth cap, a constant in the code named LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK. Past the cap it assumes the worst case, so the check itself doesn’t pin the CPU.

Detection runs on every blocking lock acquisition, and the longer the wait chain, the more it costs. Under high concurrency that cost becomes a bottleneck of its own.

The switch is innodb_deadlock_detect, on by default. Turn it off and detection stops; deadlocks can only fall back on the innodb_lock_wait_timeout timeout, the 1205 from earlier.

Metadata locks run on a separate wait-for graph inside the MDL subsystem. When DDL is blocked, that subsystem handles detection, and it also reports 1213.

What these locks look like inside

Locks are pure in-memory structures and are never written to disk.

InnoDB has a global lock hash, keyed on (space_id, page_no), with one chain of locks per page. The representation of record locks has a key design choice: it does not build one lock object per row. It works per page: one lock object carries a bitmap covering the whole page, one bit per record on that page. A few hundred rows locked on a page is just a few hundred more bits, a hundred-odd bytes. This is what makes row locks cheap.

Which bit maps to which record is fixed: a 1 means locked, a 0 means not locked. It is an exact bitset, not the kind of probabilistic structure that tolerates false positives like a Bloom filter.

Each lock object lock_t encodes several things in type_mode. The mode is S or X, the type is record lock or table lock. The rest are flags, covering gap lock, next-key, insert intention, or still waiting.

On the transaction side, the locks it holds hang off a list at trx->lock.locks, all released together on commit or rollback. The one it is waiting for is recorded separately at trx->lock.wait_lock.

Acquiring a lock has three branches. If the page has no locks at all, the fast path creates one, sets a bit, and is done. If the page has locks but none conflict, the new bit merges into the existing bitmap. On a conflict, it builds a lock object marked as waiting, appends it to that lock’s wait queue, and suspends the transaction, which underneath is waiting on an event. Before suspending it runs the deadlock check, and after waking it confirms the lock can be granted.

Who conflicts with whom is decided by a compatibility matrix, lock_compatibility_matrix. Gap locks and insert intention locks have special rules, and an insert position inside someone else’s gap has to wait.

Table locks and intent locks do not go through the record lock hash. They hang directly off the table object. An intent lock is itself a kind of table lock, queued only at the table level.

The entry point for locking is row_search_mvcc. SELECT ... FOR UPDATE sets select_lock_type to X and FOR SHARE sets it to S, locking each record as the scan reaches it. When a scan hits a secondary index, it locks the secondary index record and then goes back to the table to lock the matching clustered index record too. Plain reads never enter this path; they build a snapshot from undo and a read view.

The whole lock subsystem is protected by the lock_sys latch. MySQL 5.7 had one global lock; 8.0 shards it by page, which is what brought the pressure down. Metadata locks are a separate implementation in the MDL subsystem, sharing no structures with any of the above.

How to see a deadlock

Deadlocks are normal. What matters is keeping them rare and being able to look one up when it happens.

The LATEST DETECTED DEADLOCK section of the SHOW ENGINE INNODB STATUS output records the two transactions of the most recent deadlock, what each was running, which locks it held, and which lock it was waiting for. Read that section and who was stuck behind whom is plain.

To watch live transactions, query performance_schema.data_locks and data_lock_waits, which list which locks each live transaction holds and waits for.

Waiting longer than innodb_lock_wait_timeout, 50 seconds by default, throws 1205. That error usually means some transaction held a lock for too long.

A few ways to hit fewer locks

The shorter the transaction, the shorter the locks are held. Move network calls, file reads and writes, and message sends out of the transaction. A lock is only valid while the transaction exists, so a transaction that lives one second longer holds its locks one second longer.

For a given business flow, always change data in the same order. In the earlier deadlock, reversing the order the two sides touched rows is what made them collide. Decide project-wide which table gets hit first between orders and inventory.

Build indexes on the columns your WHERE conditions use, so they don’t scan the whole table.

Retry 1213 in business code. One or two retries clear most deadlocks. Retrying a lock-wait timeout is riskier; it usually means another slow transaction is in the way, and a retry just queues up again.

Get clear on who is waiting for whom first. That deadlock log in SHOW ENGINE INNODB STATUS beats any tutorial.