Deadlocks and Retry Patterns

Learning Focus

Use this lesson to understand deadlocks — what causes them, how to diagnose them, and how to build applications that handle them gracefully with retry logic.

Concept Overview

What Is a Deadlock?

A deadlock occurs when two or more transactions are waiting for each other's locks, creating a circular dependency that none can resolve.

Transaction A: holds lock on Row 1, waiting for lock on Row 2
Transaction B: holds lock on Row 2, waiting for lock on Row 1
→ Neither can proceed. This is a deadlock.

How MySQL Handles Deadlocks

MySQL's InnoDB engine has a built-in deadlock detector. When it detects a deadlock, it:

1. Chooses one transaction as the victim (usually the one that has done the least work)
2. Rolls back the victim transaction
3. Returns error 1213: Deadlock found when trying to get lock
4. The other transaction proceeds normally

Deadlocks are not bugs to prevent — they are normal events to handle. Any system with concurrent writes will eventually encounter deadlocks.

Deadlocks vs. Lock Waits

Situation	What Happens
Lock Wait	Transaction waits until the lock is released (or times out)
Deadlock	Circular wait — InnoDB detects it and rolls back one transaction immediately

---

Basic Syntax & Rules

Diagnosing Deadlocks

-- View the most recent deadlock information
SHOW ENGINE INNODB STATUS\G

Look for the LATEST DETECTED DEADLOCK section. It shows:

• Which transactions were involved
• Which locks they held
• Which locks they were waiting for
• Which transaction was rolled back

Deadlock-Related Settings

Variable	Default	Purpose
innodb_lock_wait_timeout	50 (seconds)	How long to wait for a lock before timing out
innodb_deadlock_detect	ON	Whether InnoDB actively checks for deadlocks
innodb_print_all_deadlocks	OFF	Whether to log all deadlocks to the error log

-- Enable logging all deadlocks
SET GLOBAL innodb_print_all_deadlocks = ON;

-- Check lock wait timeout
SELECT @@innodb_lock_wait_timeout;

Monitoring Active Locks (MySQL 8.0+)

-- View current locks
SELECT * FROM performance_schema.data_locks;

-- View lock waits
SELECT * FROM performance_schema.data_lock_waits;

-- View InnoDB transaction list
SELECT * FROM information_schema.INNODB_TRX;

---

Step-by-Step Examples

Example 1: Creating a Deadlock (Intentionally)

This demonstrates exactly how a deadlock happens.

Session 1:

START TRANSACTION;

-- Lock Row A
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1001;
-- Holds lock on account 1001

Session 2:

START TRANSACTION;

-- Lock Row B
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1002;
-- Holds lock on account 1002

-- Now try to lock Row A (held by Session 1)
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1001;
-- ⏳ WAITING for Session 1's lock...

Session 1 (tries to lock Row B):

-- Try to lock Row B (held by Session 2)
UPDATE accounts SET balance = balance + 200 WHERE account_id = 1002;
-- 💥 DEADLOCK DETECTED!
-- ERROR 1213: Deadlock found when trying to get lock
-- Session 1 is rolled back (chosen as victim)

Session 2 now proceeds — its wait on Row A is released because Session 1 rolled back.

---

Example 2: Analyzing a Deadlock

After a deadlock occurs, examine it:

SHOW ENGINE INNODB STATUS\G

Key sections to read in the output:

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 5 sec starting index read
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 4 n bits 72 index PRIMARY of table `app_db`.`accounts`
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 4 n bits 72 index PRIMARY of table `app_db`.`accounts`

*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 3 sec starting index read
*** (2) HOLDS THE LOCK(S): ... (opposite pattern)

*** WE ROLL BACK TRANSACTION (1)

---

Example 3: Application Retry Pattern (Pseudocode)

The standard approach is to retry the rolled-back transaction:

-- In a stored procedure:
DELIMITER //

CREATE PROCEDURE safe_transfer(
    IN p_from BIGINT,
    IN p_to BIGINT,
    IN p_amount DECIMAL(12,2)
)
BEGIN
    DECLARE v_retries INT DEFAULT 3;
    DECLARE v_done BOOLEAN DEFAULT FALSE;

    WHILE v_retries > 0 AND NOT v_done DO
        BEGIN
            DECLARE EXIT HANDLER FOR 1213  -- Deadlock error
            BEGIN
                SET v_retries = v_retries - 1;
                -- Optional: small delay before retry
            END;

            START TRANSACTION;

            -- Always lock in consistent order (smallest ID first)
            IF p_from < p_to THEN
                SELECT balance FROM accounts WHERE account_id = p_from FOR UPDATE;
                SELECT balance FROM accounts WHERE account_id = p_to FOR UPDATE;
            ELSE
                SELECT balance FROM accounts WHERE account_id = p_to FOR UPDATE;
                SELECT balance FROM accounts WHERE account_id = p_from FOR UPDATE;
            END IF;

            UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from;
            UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to;

            COMMIT;
            SET v_done = TRUE;
        END;
    END WHILE;

    IF NOT v_done THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Transfer failed after 3 retries due to deadlocks';
    END IF;
END //

DELIMITER ;

---

Example 4: Preventing Deadlocks with Consistent Lock Ordering

The single most effective deadlock prevention strategy: always lock rows in the same order.

-- BAD: Session 1 locks A then B; Session 2 locks B then A → deadlock
-- Session 1: UPDATE accounts WHERE id = 1001; UPDATE accounts WHERE id = 1002;
-- Session 2: UPDATE accounts WHERE id = 1002; UPDATE accounts WHERE id = 1001;

-- GOOD: Both sessions lock in ascending ID order
-- Session 1: UPDATE accounts WHERE id = 1001; UPDATE accounts WHERE id = 1002;
-- Session 2: UPDATE accounts WHERE id = 1001; UPDATE accounts WHERE id = 1002;

When both sessions request locks in the same order, the second session simply waits instead of creating a circular dependency.

---

Practical Use Cases

1. Financial Systems

Money transfers between accounts — the canonical deadlock scenario. Use consistent lock ordering.

2. Inventory Updates

Multiple orders reducing stock on the same product. FOR UPDATE with retry handles this.

3. Queue Processing

Multiple workers dequeuing tasks. Use SKIP LOCKED to avoid contention entirely.

4. Bulk Updates

Large UPDATE statements can lock many rows. Break into smaller batches to reduce deadlock window.

5. ORM/Framework Integration

Most ORMs (Hibernate, SQLAlchemy, Django) have deadlock retry configuration. Enable it.

---

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
No retry logic	Application crashes on first deadlock	Implement retry with 2–3 attempts
Inconsistent lock ordering	Frequent deadlocks between same transactions	Always acquire locks in a consistent order (e.g., ascending ID)
Very large transactions	More rows locked = more deadlock surface	Keep transactions small: fewer rows, shorter duration
Ignoring SHOW ENGINE INNODB STATUS	Can't diagnose deadlock cause	Check it after every deadlock to understand the pattern
Missing indexes on WHERE clauses	InnoDB locks entire index ranges instead of specific rows	Add indexes to narrow lock scope
Retrying without backoff	Immediate retries can cause repeated deadlocks	Add exponential backoff: 100ms, 200ms, 400ms

---

Best Practices

• Implement retry logic — every write operation should handle error 1213
• Lock in consistent order — sort lock targets by a deterministic key (e.g., primary key ascending)
• Keep transactions short — less time holding locks = less chance of deadlock
• Use smaller batches — update 1000 rows at a time instead of 1 million
• Add exponential backoff — wait longer between each retry attempt
• Enable deadlock logging — SET GLOBAL innodb_print_all_deadlocks = ON
• Monitor deadlock frequency — track deadlocks per minute; sudden spikes indicate a code change

---

Hands-On Practice

Exercise 1: Trigger a Deadlock (Easy)

Open two MySQL sessions. Follow the steps in Example 1 to intentionally create a deadlock. Observe which session is rolled back. Check SHOW ENGINE INNODB STATUS to see the deadlock report.

Exercise 2: Prevent with Consistent Ordering (Medium)

Modify the Example 1 scenario so both sessions lock accounts in ascending ID order. Verify that the deadlock no longer occurs.

Exercise 3: Retry Logic (Advanced)

Write a stored procedure that:

1. Performs a two-account transfer
2. Handles deadlock errors with retry (max 3 attempts)
3. Uses consistent lock ordering
4. Logs each retry attempt to a retry_log table
5. Fails with a clear error message after exhausting retries

---

Connection to Other Concepts

Related Concept	How It Connects
Transactions	Deadlocks occur within transaction boundaries
Isolation Levels	Higher isolation = more locking = more deadlock potential
Indexes	Missing indexes cause wider row locks, increasing deadlock risk
Performance	Deadlock retries add latency; prevention is better than cure
Stored Procedures	Retry logic is naturally implemented in procedures

What to Learn Next

• 17. User Management and Security — secure access to your transactional data

---

Visual Learning Diagram

flowchart TD
    A["Transaction A\nlocks Row 1"] --> B["Transaction B\nlocks Row 2"]
    B --> C["Transaction A\nwaits for Row 2"]
    A --> D["Transaction B\nwaits for Row 1"]
    C --> E["🔄 Circular wait = DEADLOCK"]
    D --> E
    E --> F["InnoDB detects deadlock"]
    F --> G["Rolls back victim transaction"]
    G --> H["Application retries"]

    classDef deadlock fill:#dc3545,stroke:#fff,color:#fff
    classDef retry fill:#ffc107,stroke:#333,color:#333
    class E deadlock
    class H retry

Quick Reference

-- Diagnose deadlocks
SHOW ENGINE INNODB STATUS\G

-- Enable deadlock logging
SET GLOBAL innodb_print_all_deadlocks = ON;

-- View active locks
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- Check lock timeout setting
SELECT @@innodb_lock_wait_timeout;

-- Consistent lock ordering pattern
START TRANSACTION;
SELECT * FROM accounts WHERE id = LEAST(a, b) FOR UPDATE;
SELECT * FROM accounts WHERE id = GREATEST(a, b) FOR UPDATE;
-- ... updates ...
COMMIT;

What's Next

• 17. User Management and Security