EC
1213
MySQLERRORNotableLocking & ConcurrencyHIGH confidence

Deadlock found when trying to get lock; try restarting transaction

Production Risk

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
  1. 1Transaction A locks row 1 then tries to lock row 2; Transaction B locks row 2 then tries to lock row 1
  2. 2Transactions access the same rows but in inconsistent order
  3. 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
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

expected output

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

Fix 1

Access rows in a consistent order across all transactions

WHEN As a permanent fix to prevent deadlocks.

Access rows in a consistent order across all transactions
-- 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

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

Implement retry logic in the application
-- 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

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.

Sources

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors