// MySQL error reference

MySQL error 1213: Deadlock found when trying to get lock

Two transactions were each waiting for a lock the other held. InnoDB rolled one back; run the whole transaction again.

Error number
1213
Name
ER_LOCK_DEADLOCK
SQLSTATE
40001
Category
Transactions and locking
Error message
Deadlock found when trying to get lock; try restarting transaction

What it means

A deadlock is a cycle of lock waits that can't resolve itself. InnoDB detects it right away (with innodb_deadlock_detect on, the default) and rolls back the transaction it estimates is cheaper to undo, releasing its locks. 1213 tells the rolled-back session to retry.

The whole transaction was rolled back, not just the last statement, so retry it from the start.

Common causes

  • Two transactions updating the same rows in a different order.
  • Range or gap locks from UPDATE, DELETE or SELECT ... FOR UPDATE on non-unique or missing indexes in REPEATABLE READ.
  • Concurrent inserts into a unique index combined with deletes on the same key range.
  • Foreign key checks taking shared locks on parent rows.

How to fix it

  1. Catch 1213 in the application and retry the whole transaction, a few times with a short back-off. Occasional deadlocks are normal.

  2. Read the details of the last deadlock: both transactions, the locks held and wanted.

    SHOW ENGINE INNODB STATUS;  -- LATEST DETECTED DEADLOCK
  3. Access rows in a consistent order and keep transactions short.

    START TRANSACTION;
    SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    COMMIT;
  4. To log every deadlock, not just the latest, turn on innodb_print_all_deadlocks.

    SET PERSIST innodb_print_all_deadlocks = ON;

Related errors

More transactions and locking errors

The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.

// fewer errors, faster fixes

Work on MySQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.