MySQL transactions, locking and deadlocks explained

A deadlock in MySQL is not a crash and not usually a bug in the database. It is InnoDB noticing that two transactions are each waiting for a lock the other holds, picking one to roll back, and telling your application to try again - error 1213, Deadlock found when trying to get lock; try restarting transaction. The message is literal advice. Applications that handle it with a retry and keep transactions short rarely notice deadlocks at all. The ones that suffer are the ones with long transactions, updates that scan rows they do not change, and no retry. This post explains what InnoDB actually locks, how the isolation levels differ in practice, and how to find and fix the waits and deadlocks you get.

Transactions and autocommit#

InnoDB is transactional, and MySQL runs with autocommit = 1 by default: each statement on its own is a transaction that commits when it finishes. To group statements, start a transaction explicitly.

START TRANSACTION;UPDATE accounts SET balance = balance - 25 WHERE id = 1;UPDATE accounts SET balance = balance + 25 WHERE id = 2;COMMIT;   -- or ROLLBACK;

Inside a transaction, locks taken by writes are held until COMMIT or ROLLBACK, not until the statement finishes. That single fact explains most locking trouble: every second a transaction stays open is a second its locks block everybody else.

Things that end a transaction without you asking:

• DDL and some administrative statements - CREATE, ALTER, DROP, TRUNCATE, RENAME, LOCK TABLES, and starting another transaction - commit implicitly. You cannot roll back a schema change in MySQL, and a migration that mixes DDL and data changes in one "transaction" is not atomic.
• Disconnecting rolls back anything uncommitted.
• A deadlock rolls back the whole transaction.

SAVEPOINT name and ROLLBACK TO SAVEPOINT name undo part of a transaction without ending it, which is how ORMs implement nested transactions.

Isolation levels and what they mean in InnoDB#

The isolation level decides what a transaction sees of other transactions' changes. MySQL's default is REPEATABLE READ, unlike PostgreSQL and SQL Server, which default to READ COMMITTED.

Plain SELECT statements in InnoDB do not take locks at all. They read from a consistent snapshot using multi-version concurrency control: older row versions are kept in the undo log so a reader sees the data as of its snapshot while writers carry on. Under REPEATABLE READ the snapshot is taken at the first read in the transaction (or at START TRANSACTION WITH CONSISTENT SNAPSHOT) and kept to the end, so the same query returns the same rows every time - even if other transactions committed changes meanwhile.

-- Check and change for this sessionSELECT @@transaction_isolation;SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;-- Or for the next transaction onlySET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Many applications run perfectly well on READ COMMITTED, and it takes fewer gap locks, which means fewer lock waits and fewer deadlocks on insert-heavy tables. It is a reasonable change to make deliberately; it is not a fix to make blindly, because code that relied on a stable snapshot within a transaction will behave differently.

A long-running transaction has a cost even if it only reads: InnoDB cannot purge old row versions that its snapshot might still need, so the undo history grows. SHOW ENGINE INNODB STATUS reports this as History list length; a number in the millions means something has had a transaction open for a very long time.

Locking reads and the lost update#

Because plain reads do not lock, the classic read-modify-write is unsafe:

-- Two requests run this at the same time for the same accountSELECT balance FROM accounts WHERE id = 1;          -- both read 100UPDATE accounts SET balance = 75 WHERE id = 1;      -- both write 75

Both requests saw 100 and both wrote 75; one withdrawal disappeared. Three fixes, from best to most general:

1. Do the arithmetic in the UPDATE. UPDATE accounts SET balance = balance - 25 WHERE id = 1 AND balance >= 25; is atomic, and rowCount() = 0 tells you the balance was insufficient.
2. Lock the row when you read it. SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; takes an exclusive lock, so the second request waits until the first commits and then reads the new value.
3. Optimistic locking. Add a version column, update with WHERE id = 1 AND version = 7, and retry if no row matched. No locks held while the user thinks.

FOR SHARE (the 8.0 spelling of LOCK IN SHARE MODE) takes a shared lock: others can read-lock the row too, but nobody can change it until you commit. Two modifiers make locking reads useful for work queues:

-- Fail immediately instead of waitingSELECT * FROM jobs WHERE id = 42 FOR UPDATE NOWAIT;-- Claim the next free job, skipping rows other workers have lockedSTART TRANSACTION;SELECT id, payload FROM jobsWHERE status = 'pending'ORDER BY id LIMIT 1FOR UPDATE SKIP LOCKED;UPDATE jobs SET status = 'running' WHERE id = ?;COMMIT;

SKIP LOCKED lets many workers pull from one table without queuing behind each other, which is how a database-backed job queue stays fast.

What InnoDB actually locks#

InnoDB locks index records, not rows in the abstract. This matters more than anything else in this post.

• Record lock: a lock on one index entry.
• Gap lock: a lock on the gap between two index entries, stopping inserts into it. Used under REPEATABLE READ to prevent phantom rows appearing in a range you locked.
• Next-key lock: a record lock plus the gap before it. This is what REPEATABLE READ uses for locking reads and for UPDATE and DELETE that search by a range or a non-unique index.
• Insert intention lock: a special gap lock taken by INSERT. Two inserts into the same gap at different positions do not block each other, but an insert waits for any gap lock held by someone else.

The consequence: a locking statement locks every index record it examines, not just the ones it changes. With no usable index, it examines - and locks - the whole table.

-- No index on email: scans and locks every row in usersUPDATE users SET last_seen = NOW() WHERE email = 'a@example.com';-- With an index: locks one record (and a gap under REPEATABLE READ)CREATE INDEX idx_users_email ON users (email);

The first version turns two unrelated users logging in at once into a lock wait. An index on the columns in WHERE clauses of UPDATE, DELETE and SELECT ... FOR UPDATE is a locking fix as much as a speed fix. MySQL indexes and EXPLAIN shows how to check what a statement will scan.

Foreign keys add locks too: inserting a child row takes a shared lock on the parent row it references, and a unique index check on insert takes a shared lock on a duplicate it finds. Both appear in deadlock reports and surprise people who never wrote a locking read.

Lock wait timeouts: finding the blocker#

When a transaction waits for a lock longer than innodb_lock_wait_timeout - 50 seconds by default - it gets error 1205, Lock wait timeout exceeded; try restarting transaction.

A lock wait is always caused by another transaction holding a lock - usually one that is open for far too long. The sys schema shows who is blocking whom:

SELECT wait_age, locked_table, locked_index,       waiting_pid, waiting_query,       blocking_pid, blocking_query,       sql_kill_blocking_connectionFROM sys.innodb_lock_waits;

An empty blocking_query is the most common and most telling result: the blocking session is not running anything. It started a transaction, changed a row, and went idle - a request that crashed without rolling back but kept its pooled connection, a developer's SQL client with autocommit off, or an application that does an HTTP call inside a transaction. Find how long transactions have been open:

SELECT trx_mysql_thread_id AS pid, trx_state, trx_started,       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS open_s,       trx_rows_locked, trx_rows_modified, trx_queryFROM information_schema.innodb_trxORDER BY trx_started;

KILL <pid> ends the session and rolls back its transaction. Then fix the cause - a transaction should never stay open while the code waits on anything other than the database. performance_schema.data_locks and data_lock_waits show every individual lock, if you need the detail.

Deadlocks: why they happen and how to read one#

A deadlock is a cycle: transaction A holds a lock B wants, and B holds a lock A wants. Neither can proceed, so waiting is pointless. InnoDB's deadlock detector (innodb_deadlock_detect, on by default) spots the cycle immediately, rolls back the transaction that has modified the fewest rows, and returns error 1213 with SQLSTATE 40001. Unlike a timeout, the whole victim transaction is rolled back.

The common patterns:

• Opposite order. One code path updates order 10 then 20, another updates 20 then 10. Fix: always lock rows in a consistent order, typically by primary key - sort the IDs before the loop.
• Check then insert. Two sessions run SELECT ... FOR UPDATE for a row that does not exist yet (both get compatible gap locks), then both INSERT it (each insert intention waits for the other's gap lock). Fix: let a unique index decide - INSERT ... ON DUPLICATE KEY UPDATE, or insert and catch 1062.
• Wide scans. An UPDATE with a poorly indexed WHERE locks far more rows than it changes, so it overlaps with everyone. Fix: the index.
• Long transactions. The longer a transaction holds locks, the more likely another transaction needs one of them in the wrong order.

The most recent deadlock is described in full by SHOW ENGINE INNODB STATUS\G, in the LATEST DETECTED DEADLOCK section: both transactions, the statement each was running, the locks held and the lock waited for, including the index name. Only the last one is kept. To log every deadlock to the error log, switch on innodb_print_all_deadlocks, which needs an administrative account:

SET PERSIST innodb_print_all_deadlocks = ON;

Read the HOLDS THE LOCK(S) and WAITING FOR THIS LOCK TO BE GRANTED lines for each transaction; the index named there tells you which access path to look at. lock_mode X locks gap before rec and insert intention together are the check-then-insert pattern almost every time.

Retrying correctly#

You cannot eliminate deadlocks on a busy system, only make them rare. Every transaction that can deadlock needs a retry, and the retry must re-run the whole transaction from the beginning - including the reads, because the data may have changed.

import timeimport pymysqlRETRYABLE = {1213, 1205}  # deadlock, lock wait timeoutdef run_in_transaction(conn, work, attempts=3):    for attempt in range(1, attempts + 1):        try:            conn.begin()            result = work(conn)            conn.commit()            return result        except pymysql.err.OperationalError as e:            conn.rollback()            if e.args[0] not in RETRYABLE or attempt == attempts:                raise            time.sleep(0.05 * attempt)  # short, growing backoff

Keep the retried function free of side effects outside the database - sending an email or charging a card inside it means doing that twice. Laravel's DB::transaction($callback, 3) and Django's patterns around transaction.atomic() provide the same structure; in raw PDO, check $e->errorInfo[1] for 1213 and 1205 as in PHP PDO and MySQL.

Habits that prevent most lock trouble#

Almost every lock problem on a small MySQL server comes down to a handful of habits. None of them needs a configuration change:

1. Keep transactions short. Open the transaction, run the statements, commit. Never call an external API, send an email, wait for a user or sleep inside one. Do the slow work first, then the database work.
2. Index what you lock. Every UPDATE, DELETE and SELECT ... FOR UPDATE should find its rows through an index, so it locks the rows it needs and not the table.
3. Lock in a consistent order. When a transaction touches several rows, sort them by primary key first. Two transactions that always lock in the same order cannot deadlock on those rows.
4. Prefer atomic statements. UPDATE ... SET stock = stock - 1 WHERE stock > 0 beats read, check, write.
5. Let unique indexes arbitrate. Insert and handle error 1062 rather than checking first.
6. Batch big changes. Deleting two million old rows in one statement holds two million locks and bloats the undo log; deleting 5,000 at a time in a loop, each its own transaction, lets other work through between batches.
7. Retry 1213 and 1205 with the whole transaction, a few times, with a short backoff.

Batching deserves an example, because it is the one people skip:

-- Repeat until it affects 0 rowsDELETE FROM events WHERE created_at < '2026-01-01' ORDER BY id LIMIT 5000;

Metadata locks: when ALTER TABLE hangs everything#

There is a second lock system above InnoDB's row locks. Any statement touching a table takes a metadata lock on it, held until the transaction ends. ALTER TABLE needs an exclusive metadata lock, so it waits for every open transaction that has touched the table - and every new query on the table queues behind the waiting ALTER. One idle transaction plus one migration can stop a whole site, with every query showing Waiting for table metadata lock.

lock_wait_timeout, which governs metadata locks, defaults to 31,536,000 seconds - a year. Before running DDL on a live system, set it low for your session so the migration gives up instead of freezing traffic:

SET SESSION lock_wait_timeout = 5;ALTER TABLE orders ADD COLUMN note VARCHAR(255) NULL, ALGORITHM=INSTANT;

If it fails, find the idle transaction with the innodb_trx query above (or sys.schema_table_lock_waits), deal with it, and try again. Migrations without downtime covers the rest of that process.

FAQ#

Are deadlocks a sign that something is broken?

Occasional deadlocks are normal on any system with concurrent writes, and the retry handles them. A steady stream of them, or deadlocks between the same two statements every few minutes, points at an access-order or indexing problem worth fixing. Turn on innodb_print_all_deadlocks and look for the pattern.

Should I switch to READ COMMITTED?

If your workload is insert-heavy and you see gap-lock deadlocks, it often helps, and many frameworks are tested on it. Change it per session or globally, then test the code paths that read the same data twice in one transaction. Do not expect it to fix lock waits caused by long transactions; nothing fixes those except shorter transactions.

Should I increase innodb_lock_wait_timeout?

Rarely. Fifty seconds is already longer than any web request should wait. Lower it for interactive workloads (5-10 seconds) so stuck requests fail fast, and raise it only for batch jobs that knowingly wait behind other batch jobs.

Does SELECT block UPDATE in MySQL?

A plain SELECT does not; it reads a snapshot and takes no row locks. SELECT ... FOR UPDATE and FOR SHARE do, and every SELECT takes a shared metadata lock on the table, which blocks ALTER TABLE but not UPDATE.

Can I turn off deadlock detection?

Yes, with innodb_deadlock_detect = OFF, after which deadlocks resolve by innodb_lock_wait_timeout instead. It exists for very high-concurrency systems where detection itself becomes a bottleneck. On a small server, leave it on: instant detection beats a 50-second wait.