1213ERRORTier 2 — Caution✅ HIGH confidence

Deadlock found when trying to get lock; try restarting transaction

Category: Locking & ConcurrencyVersions: All MariaDB / MySQL versions

🔴 Production Risk Error

Frequent deadlocks indicate a design issue in transaction ordering or missing indexes. Use SHOW ENGINE INNODB STATUS to capture the deadlock graph and identify which tables and rows are involved.

What this means

Error 1213 (SQLSTATE 40001) is raised when InnoDB's deadlock detection algorithm identifies a circular lock dependency between two or more transactions. InnoDB automatically selects one transaction as the victim and rolls it back to break the cycle. The rolled-back transaction should typically be retried by the application.

Why it happens

1Transaction A locks row 1 then tries to lock row 2; Transaction B locks row 2 then tries to lock row 1
2Transactions access the same rows but in inconsistent order
3A missing index causes a table scan that takes row locks on many rows, increasing conflict probability

How to reproduce

Two transactions acquire locks in opposite order, forming a cycle.

trigger — this will ERROR

-- Connection A:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- locks row 1

-- Connection B (simultaneously):
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- locks row 2
UPDATE accounts SET balance = balance - 50 WHERE id = 1;   -- waits for A's lock on row 1

-- Connection A (continues):
UPDATE accounts SET balance = balance + 50 WHERE id = 2;   -- waits for B's lock on row 2
-- DEADLOCK: InnoDB rolls back one of these transactions with error 1213

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Fix 1: Access rows in a consistent order across all transactions

As a permanent fix to prevent deadlocks.

fix

-- Always lock lower IDs first:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- lowest id first
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Why this works

When all transactions acquire locks in the same canonical order (e.g., by ascending primary key), a circular lock dependency cannot form, eliminating the deadlock condition.

Fix 2: Implement retry logic in the application

Since deadlocks can occur even with ordered access under high concurrency, retry the rolled-back transaction.

fix

-- Pseudocode pattern:
-- max_retries = 3
-- for attempt in range(max_retries):
--     try:
--         with db.transaction():
--             perform_transfer(conn, from_id, to_id, amount)
--         break
--     except DatabaseError as e:
--         if e.errno == 1213 and attempt < max_retries - 1:
--             continue  # retry
--         raise

Why this works

InnoDB rolls back the victim transaction completely. The application detects error 1213 and re-executes the entire transaction. After the other transaction commits, the retry will succeed.

What not to do

✗

Disable innodb_deadlock_detect to avoid the error

Why it's wrong: Disabling deadlock detection means deadlocked transactions wait until innodb_lock_wait_timeout expires instead of being resolved immediately, severely degrading throughput.

Version notes

MariaDB 10.3+innodb_deadlock_detect is ON by default. Run SHOW ENGINE INNODB STATUS and look for the LATEST DETECTED DEADLOCK section to see the full lock graph of the last deadlock.