A deadlock occurs when two transactions each hold a lock the other needs, creating a cycle. Neither can proceed. MySQL detects deadlocks automatically and rolls back one transaction (the victim) so the other can continue. The rolled-back transaction receives an error.

How can I prevent deadlocks?

Access tables in the same order across all transactions. Keep transactions short to minimize lock duration. Use appropriate isolation levels. Add indexes to reduce the number of rows locked. Use SELECT ... FOR UPDATE with specific WHERE clauses to lock only necessary rows.

Locking & Deadlocks

Prevent race conditions with row-level locking and understand how deadlocks happen and how MySQL resolves them.

Two people try to book the last seat on a flight at the same time. Both read "1 seat left." Both book it. The airline now has a sold seat that doesn't exist. This is a race condition, and it happens in databases every day. Locking is the mechanism that prevents it: one transaction locks the row, the other waits, and the data stays correct.

Definition

A deadlock occurs when two or more transactions wait for each other's locks indefinitely. MySQL automatically detects deadlocks and rolls back one transaction to resolve the conflict.

Required step: Make sure you understand transactions from Stage 14 and isolation levels from Stage 22. If you do not have these tables yet, run this setup in the playground:

CREATE DATABASE IF NOT EXISTS school;
USE school;

CREATE TABLE IF NOT EXISTS seats (
  id INT AUTO_INCREMENT PRIMARY KEY,
  class VARCHAR(50) NOT NULL,
  available INT NOT NULL DEFAULT 1,
  price DECIMAL(10,2) NOT NULL
);

INSERT INTO seats (class, available, price) VALUES
  ('economy', 5, 299.99),
  ('business', 2, 899.99),
  ('first', 1, 1599.99);

CREATE TABLE IF NOT EXISTS accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  balance DECIMAL(10,2) NOT NULL DEFAULT 0
);

INSERT INTO accounts (name, balance) VALUES
  ('Alice', 1000.00),
  ('Bob', 500.00);

Troubleshooting: If you get "table doesn't exist" errors, re-run the setup above. It uses IF NOT EXISTS so it is safe to run multiple times.

Why Locking Matters

Without locking, concurrent transactions can read and modify the same rows at the same time. The result is corrupted data: lost updates, inconsistent reads, and phantom rows. Locking ensures that only one transaction can modify a row at a time, while allowing other transactions to read (depending on the lock type).

Scenario:Race Condition Example

Transaction A: Reads seats_available = 1. Plans to subtract 1 and book.

Transaction B: Also reads seats_available = 1. Also plans to subtract 1 and book.

Both subtract 1 and commit. Final value: 0. But two seats were booked. The airline lost a seat. This is a lost update.

Shared vs Exclusive Locks

MySQL uses two fundamental lock types. Understanding the difference between them is the key to understanding all locking behavior.

Key concepts:Lock Types

Shared lock (S): Allows reading the locked row. Multiple transactions can hold shared locks on the same row simultaneously. Blocks write operations (UPDATE, DELETE, SELECT ... FOR UPDATE) on that row.

Exclusive lock (X): Allows both reading and writing the locked row. Only one transaction can hold an exclusive lock on a given row. Blocks all other locks (shared or exclusive) on that row.

Reference:Lock Compatibility

Shared + Shared = OK. Multiple transactions can read the same row.

Shared + Exclusive = Blocked. A write lock waits until all shared locks are released.

Exclusive + anything = Blocked. Only one transaction can write at a time.

MySQL acquires locks automatically in some cases. An UPDATE statement acquires an exclusive lock. A plain SELECT acquires no lock (under the default isolation level). Explicit locking with FOR SHARE or FOR UPDATE gives you control over when locks are acquired.

SELECT ... FOR SHARE

Acquires a shared lock on the selected rows. Other transactions can read the same rows but cannot modify them until this transaction ends.

Read-lock a row before updating

START TRANSACTION;

-- Acquire a shared lock on seat 1
SELECT * FROM seats WHERE id = 1 FOR SHARE;

-- Other transactions can still read this row
-- But they cannot UPDATE or DELETE it until we COMMIT
UPDATE seats SET available = available - 1 WHERE id = 1;

COMMIT;

FOR SHARE acquires a shared lock. Other transactions can read the row but cannot write to it until your transaction ends. This prevents another transaction from modifying the same row while you're working with it.

Old syntax (still works)

-- Pre-MySQL 8.0 syntax
SELECT * FROM seats WHERE id = 1 LOCK IN SHARE MODE;

-- MySQL 8.0+ standard syntax
SELECT * FROM seats WHERE id = 1 FOR SHARE;

LOCK IN SHARE MODE is the older syntax. FOR SHARE is the standard MySQL 8.0+ syntax. Both do the same thing. Use FOR SHARE for new code.

SELECT ... FOR UPDATE

Acquires an exclusive lock on the selected rows. No other transaction can read (with FOR SHARE or FOR UPDATE) or write these rows until this transaction ends. This is the standard pattern for read-modify-write operations.

The read-modify-write pattern

START TRANSACTION;

-- Exclusive lock: no other transaction can touch this row
SELECT * FROM seats WHERE id = 1 FOR UPDATE;

-- Do your logic (check available, calculate, etc.)
-- ...

-- Write the result
UPDATE seats SET available = available - 1 WHERE id = 1;

COMMIT;

FOR UPDATE acquires an exclusive lock. While this transaction is open, no other transaction can read or write this row. This prevents the lost update problem. Always use FOR UPDATE when you read data you plan to modify.

Lock multiple rows

START TRANSACTION;

-- Lock all economy seats
SELECT * FROM seats WHERE class = 'economy' FOR UPDATE;

-- No other transaction can modify these rows
UPDATE seats SET price = price * 1.1 WHERE class = 'economy';

COMMIT;

FOR UPDATE locks all rows matching the WHERE clause. If an index is used, InnoDB locks only the index entries (not the entire table). Without an index, InnoDB may lock the entire table.

Guidelines:When to Use FOR UPDATE

Always use FOR UPDATE when you read data you plan to modify. This is the safest pattern for read-modify-write operations.

Use FOR SHARE when you need consistent reads across multiple queries but don't plan to modify the data.

No explicit lock needed for read-only queries or when you don't need consistency across multiple statements.

Lock Wait Timeout

When a transaction tries to acquire a lock that is held by another transaction, it waits. If the wait exceeds the lock timeout, MySQL returns an error instead of waiting forever.

Lock timeout scenario

-- Transaction 1 (holds the lock)
START TRANSACTION;
SELECT * FROM seats WHERE id = 1 FOR UPDATE;
-- ... long operation, forgot to COMMIT

-- Transaction 2 (tries to lock the same row)
START TRANSACTION;
SELECT * FROM seats WHERE id = 1 FOR UPDATE;
-- ERROR 1205 (HY000): Lock wait timeout exceeded;
-- try restarting transaction

Transaction 2 waits for Transaction 1 to release the lock. If Transaction 1 takes longer than innodb_lock_wait_timeout seconds (default 50), MySQL returns error 1205.

Adjust the timeout

-- Check current timeout (default 50 seconds)
SELECT @@innodb_lock_wait_timeout;

-- Set for this session (shorter = fail faster)
SET SESSION innodb_lock_wait_timeout = 10;

A shorter timeout causes applications to fail fast when there's contention. A longer timeout allows more time for concurrent transactions to complete. Choose based on your workload.

Deadlocks

A deadlock occurs when two transactions each hold a lock the other needs. Both wait for the other to release, creating a circular dependency. Neither can proceed.

Key concept:What is a Deadlock?

Transaction A: Locks row 1, waits for row 2.

Transaction B: Locks row 2, waits for row 1.

Both wait for the other to release. Without intervention, both would wait forever. MySQL's InnoDB engine detects this cycle and resolves it by rolling back one transaction (the "victim").

Classic deadlock scenario

-- Transaction A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- small delay here...
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction B (runs concurrently)
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- small delay here...
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- ERROR 1213 (40001): Deadlock found when
-- trying to get lock; try restarting transaction

Transaction A locks row 1 then tries to lock row 2. Transaction B locks row 2 then tries to lock row 1. MySQL detects the deadlock and rolls back Transaction B (the victim) so Transaction A can proceed.

MySQL resolves deadlocks automatically

-- MySQL's InnoDB engine has a built-in deadlock detector
-- (innodb_deadlock_detect = ON by default)

-- When a deadlock is found:
-- 1. MySQL chooses one transaction as the victim
-- 2. Returns error 1213 to the victim
-- 3. The victim's transaction is rolled back
-- 4. The other transaction proceeds

-- The victim should catch the error and retry
-- the transaction from the beginning

You don't need to manually detect or resolve deadlocks. MySQL handles it automatically. Your application just needs to catch the error and retry the transaction.

Diagnosing Deadlocks

When deadlocks happen in production, you need to understand why. SHOW ENGINE INNODB STATUS shows the most recent deadlock, including which transactions were involved, which locks each held, and which locks they were waiting for.

Check the latest deadlock

SHOW ENGINE INNODB STATUS\G

-- Look for the LATEST DETECTED DEADLOCK section
-- It shows:
-- - Which transactions were involved
-- - Which locks each transaction held
-- - Which lock each was waiting for
-- - Which transaction was chosen as victim

The LATEST DETECTED DEADLOCK section contains the full story of what happened. Use it to understand why the deadlock occurred and how to prevent it.

Best practices:How to Prevent Deadlocks

Lock rows in the same order. If Transaction A locks row 1 then row 2, Transaction B should do the same. This eliminates the circular dependency.

Keep transactions short. The longer a transaction holds locks, the more likely it is to conflict with another transaction. Commit as soon as possible.

Use proper indexes. Without indexes, InnoDB locks the entire table instead of individual rows. More rows locked = more contention = more deadlocks.

Avoid user interaction inside transactions. Don't wait for user input while holding a lock. Lock, query, compute, write, commit.

Prevent deadlocks with consistent ordering

-- BAD: Different lock order = deadlock risk
-- Transaction A: lock row 1, then row 2
-- Transaction B: lock row 2, then row 1

-- GOOD: Same lock order = no deadlock
-- Transaction A: lock row 1, then row 2
-- Transaction B: lock row 1, then row 2
-- (Transaction B waits for A to release row 1,
--  then proceeds. No circular dependency.)

-- Practical example: always lock accounts by id ASC
START TRANSACTION;
SELECT * FROM accounts WHERE id IN (1, 2)
ORDER BY id ASC FOR UPDATE;
-- Both transactions lock in the same order
COMMIT;

By always locking rows in ascending id order, you ensure that transactions wait in a queue rather than forming a circular dependency. This eliminates deadlocks entirely.

What does SELECT ... FOR UPDATE do?

How does MySQL handle a deadlock?

Key Takeaways

Shared locks allow concurrent reads; exclusive locks block everything.
SELECT ... FOR UPDATE acquires an exclusive lock for read-modify-write patterns.
SELECT ... FOR SHARE acquires a shared lock for consistent reads.
Lock wait timeout (default 50s) prevents indefinite waiting.
Deadlocks occur when transactions hold locks the other needs in a circular pattern.
MySQL detects deadlocks automatically and rolls back one transaction.
Prevent deadlocks by locking rows in the same order and keeping transactions short.
Use SHOW ENGINE INNODB STATUS to diagnose why deadlocks happened.

Ready to test your knowledge?

Take a Quiz