Skip to content

Locking & Deadlocks

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

Two students try to enroll in the last available seat of a course at the same time. Both read "1 seat left." Both enroll. The course is now over capacity. 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.

Start fresh: Click Reset in the sidebar to clear your database, then run the setup below.

CREATE DATABASE IF NOT EXISTS school;
USE school;

CREATE TABLE IF NOT EXISTS course_enrollment_slots (
  id INT AUTO_INCREMENT PRIMARY KEY,
  course_name VARCHAR(100) NOT NULL,
  slots_available INT NOT NULL DEFAULT 30,
  max_capacity INT NOT NULL DEFAULT 30
);

CREATE TABLE IF NOT EXISTS fee_payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  payment_date DATE NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending'
);

INSERT INTO course_enrollment_slots (course_name, slots_available, max_capacity) VALUES
  ('Algebra', 25, 30),
  ('Biology', 30, 30),
  ('English Composition', 28, 30);

INSERT INTO fee_payments (student_id, amount, payment_date, status) VALUES
  (1, 500.00, '2026-01-15', 'pending'),
  (2, 500.00, '2026-01-15', 'pending');

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 slots_available = 1. Plans to subtract 1 and enroll the student.

Transaction B: Also reads slots_available = 1. Also plans to subtract 1 and enroll.

Both subtract 1 and commit. Final value: 0. But two students enrolled. The course is over capacity. 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 the Algebra course
SELECT * FROM course_enrollment_slots WHERE course_name = 'Algebra' FOR SHARE;

-- Other transactions can still read this row
-- But they cannot UPDATE or DELETE it until we COMMIT
UPDATE course_enrollment_slots SET slots_available = slots_available - 1 WHERE course_name = 'Algebra';

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 course_enrollment_slots WHERE course_name = 'Algebra' LOCK IN SHARE MODE;

-- MySQL 8.0+ standard syntax
SELECT * FROM course_enrollment_slots WHERE course_name = 'Algebra' 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 course_enrollment_slots WHERE course_name = 'Algebra' FOR UPDATE;

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

-- Write the result
UPDATE course_enrollment_slots SET slots_available = slots_available - 1 WHERE course_name = 'Algebra';

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 course slots
SELECT * FROM course_enrollment_slots WHERE course_name IN ('Algebra', 'Biology') FOR UPDATE;

-- No other transaction can modify these rows
UPDATE course_enrollment_slots SET slots_available = slots_available - 1 WHERE course_name = 'Algebra';

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 course_enrollment_slots WHERE course_name = 'Algebra' FOR UPDATE;
-- ... long operation, forgot to COMMIT

-- Transaction 2 (tries to lock the same row)
START TRANSACTION;
SELECT * FROM course_enrollment_slots WHERE course_name = 'Algebra' 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 fee_payments row 1, waits for fee_payments row 2.

Transaction B: Locks fee_payments row 2, waits for fee_payments 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 fee_payments SET status = 'paid' WHERE id = 1;
-- small delay here...
UPDATE fee_payments SET status = 'paid' WHERE id = 2;

-- Transaction B (runs concurrently)
START TRANSACTION;
UPDATE fee_payments SET status = 'overdue' WHERE id = 2;
-- small delay here...
UPDATE fee_payments SET status = 'overdue' 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 fee_payments row 1, then row 2
-- Transaction B: lock fee_payments row 2, then row 1

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

-- Practical example: always lock fee_payments by id ASC
START TRANSACTION;
SELECT * FROM fee_payments 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

Related Blog Posts

Related Tutorials