📋

Description

Error 1213, 'Deadlock found', indicates that two or more transactions in your MySQL database are mutually blocking each other, preventing any of them from completing. This typically occurs in highly concurrent environments when transactions attempt to acquire locks on resources already held by another transaction, leading to an infinite wait state.

💬

Error Message

Deadlock found when trying to get lock; try restarting transaction

🔍

Known Causes

4 known causes

⚠️

Concurrent Data Access

Multiple transactions simultaneously attempt to update or access the same rows or tables, leading to a contention for locks.

⚠️

Missing or Inefficient Indexes

Lack of proper indexing can force transactions to scan larger portions of tables, acquiring more locks than necessary and increasing deadlock probability.

⚠️

Long-Running Transactions

Transactions that hold locks for extended periods increase the window of opportunity for other transactions to become deadlocked while waiting for those locks.

⚠️

Inconsistent Lock Order

When different transactions acquire locks on resources in varying sequences, it creates a classic scenario for a circular wait, resulting in a deadlock.

🛠️

Solutions

5 solutions available

1. Retry the Transaction easy

Simply retry - deadlock victim transaction can succeed

1

Implement automatic retry

import mysql.connector
import time

def execute_with_retry(conn, query, params, max_retries=3):
    for attempt in range(max_retries):
        try:
            cursor = conn.cursor()
            cursor.execute(query, params)
            conn.commit()
            return True
        except mysql.connector.Error as e:
            conn.rollback()
            if e.errno == 1213:  # Deadlock
                time.sleep(0.1 * (attempt + 1))
                continue
            raise
    raise Exception('Max retries exceeded')

2

PHP example

$maxRetries = 3;
for ($i = 0; $i < $maxRetries; $i++) {
    try {
        $pdo->beginTransaction();
        $pdo->exec($query);
        $pdo->commit();
        break;
    } catch (PDOException $e) {
        $pdo->rollBack();
        if (strpos($e->getMessage(), 'Deadlock') !== false && $i < $maxRetries - 1) {
            usleep(100000 * ($i + 1));
            continue;
        }
        throw $e;
    }
}

2. Access Tables in Consistent Order medium

Prevent deadlock by ordering lock acquisition

1

Always access tables in same order

-- Bad: Different order causes deadlock
-- Transaction 1: UPDATE users, then UPDATE orders
-- Transaction 2: UPDATE orders, then UPDATE users

-- Good: Same order in all transactions
-- Transaction 1: UPDATE orders, then UPDATE users
-- Transaction 2: UPDATE orders, then UPDATE users

2

Sort operations by primary key

-- When updating multiple rows, sort by PK
-- Bad: Random order
UPDATE accounts SET balance = balance - 100 WHERE id IN (5, 2, 8, 1);

-- Good: Sorted order
UPDATE accounts SET balance = balance - 100 WHERE id IN (1, 2, 5, 8);

3. Reduce Transaction Scope medium

Hold locks for shorter time

1

Keep transactions small and fast

-- Bad: Long transaction
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- ... lots of application logic ...
UPDATE users SET balance = 500 WHERE id = 1;
COMMIT;

-- Good: Minimal transaction
-- Do calculations outside transaction
START TRANSACTION;
UPDATE users SET balance = 500 WHERE id = 1;
COMMIT;

4. Use SELECT ... FOR UPDATE Wisely advanced

Lock upfront to prevent deadlock

1

Lock all needed rows at start

START TRANSACTION;
-- Lock all rows you'll need at the beginning
SELECT * FROM accounts WHERE id IN (1, 2) FOR UPDATE;
-- Now do your updates - no deadlock possible
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

5. Analyze Deadlock Details advanced

Understand what's causing deadlocks

1

View latest deadlock info

SHOW ENGINE INNODB STATUS;

2

Enable deadlock logging

SET GLOBAL innodb_print_all_deadlocks = ON;

3

Check error log for deadlock details

tail -100 /var/log/mysql/error.log | grep -A 50 'DEADLOCK'

🔗

Related Errors

5 related errors

MySQL Error 1213: Transaction Deadlock Detected