📋

Description

Error 1213 indicates a deadlock in MariaDB, a situation where two or more transactions are waiting for locks held by each other, forming a circular dependency. MariaDB automatically detects and resolves this by aborting one transaction, which becomes the 'victim'.

💬

Error Message

Deadlock found when trying to get lock; try restarting transaction

🔍

Known Causes

3 known causes

⚠️

Concurrent Lock Contention

Multiple transactions attempting to acquire locks on the same resources but in conflicting orders, creating a circular wait.

⚠️

Inefficient Query Design

Poorly optimized queries or lack of specific WHERE clauses can acquire excessive locks, increasing the chance of contention.

⚠️

Missing or Inadequate Indexes

Insufficient indexing forces full table scans, leading to more extensive and prolonged lock acquisitions than necessary.

🛠️

Solutions

5 solutions available

1. Retry the Transaction easy

Implement automatic retry logic

1

Deadlocks are normal - just retry the failed transaction

def execute_transaction(connection):
    max_retries = 3
    for attempt in range(max_retries):
        try:
            cursor = connection.cursor()
            cursor.execute("START TRANSACTION")
            # ... your queries ...
            cursor.execute("COMMIT")
            return
        except mysql.connector.Error as e:
            cursor.execute("ROLLBACK")
            if e.errno == 1213 and attempt < max_retries - 1:
                time.sleep(0.1 * (attempt + 1))  # Exponential backoff
                continue
            raise

2

Node.js retry example

async function executeWithRetry(pool, queries, maxRetries = 3) {
    for (let attempt = 0; attempt < maxRetries; attempt++) {
        const conn = await pool.getConnection();
        try {
            await conn.beginTransaction();
            for (const q of queries) await conn.query(q);
            await conn.commit();
            return;
        } catch (err) {
            await conn.rollback();
            if (err.errno === 1213 && attempt < maxRetries - 1) {
                await new Promise(r => setTimeout(r, 100 * (attempt + 1)));
                continue;
            }
            throw err;
        } finally {
            conn.release();
        }
    }
}

2. Analyze the Deadlock medium

Understand what caused the deadlock

1

View the most recent deadlock

SHOW ENGINE INNODB STATUS;

2

Enable deadlock logging to error log

SET GLOBAL innodb_print_all_deadlocks = ON;

3

Key info to look for in deadlock output

-- Look for these in INNODB STATUS:
-- (1) TRANSACTION: First transaction involved
-- (2) TRANSACTION: Second transaction involved  
-- HOLDS THE LOCK(S): What each transaction locked
-- WAITING FOR: What lock each is waiting for
-- WE ROLL BACK: Which transaction was killed

3. Access Tables in Consistent Order medium

Prevent deadlocks by ordering resource access

1

Deadlock example - inconsistent order

-- Transaction 1:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Locks row 1
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Waits for row 2

-- Transaction 2 (concurrent):
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- Locks row 2
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- Waits for row 1
-- DEADLOCK!

2

Fix: Always access in same order (e.g., by ID)

-- Both transactions access lower ID first:
-- Transaction 1:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction 2:
UPDATE accounts SET balance = balance + 50 WHERE id = 1;  -- Same order!
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- No deadlock possible

3

In application code, sort IDs before updating

def transfer_money(from_id, to_id, amount):
    # Always lock lower ID first
    ids = sorted([from_id, to_id])
    
    cursor.execute("START TRANSACTION")
    cursor.execute("SELECT * FROM accounts WHERE id IN (%s,%s) FOR UPDATE", ids)
    cursor.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, from_id))
    cursor.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, to_id))
    cursor.execute("COMMIT")

4. Add Appropriate Indexes medium

Reduce lock scope with better indexes

1

Without index, UPDATE locks more rows

-- Without index on status, this may lock entire table
UPDATE orders SET processed = 1 WHERE status = 'pending';

-- Add index
CREATE INDEX idx_status ON orders(status);

2

Check query uses index

EXPLAIN UPDATE orders SET processed = 1 WHERE status = 'pending';

5. Use Lower Isolation Level advanced

Reduce locking with READ COMMITTED

1

Check current isolation level

SELECT @@transaction_isolation;

2

Use READ COMMITTED for less locking

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

3

Set globally in my.cnf

[mysqld]
transaction-isolation = READ-COMMITTED

🔗

Related Errors

5 related errors

MariaDB Error 1213: Deadlock Detected, Transaction Victim