MySQL Error 1213 Deadlock — transaction was rolled back

What causes this error

A deadlock occurs when Transaction A holds Lock 1 and waits for Lock 2, while Transaction B holds Lock 2 and waits for Lock 1. Neither can proceed. InnoDB's deadlock detector runs continuously and resolves this by rolling back the transaction that has done the least work (fewest undo log entries). The rolled-back transaction receives error 1213. Common triggers: two transactions updating the same rows in different order; gap locks on non-unique indexes causing phantom read prevention locks that block inserts; SELECT ... FOR UPDATE queries locking rows in unpredictable order due to missing or different indexes; and bulk UPDATE/DELETE operations on large tables that acquire many locks simultaneously.

How to fix it

1. 01

   step 1

   Read the deadlock report from InnoDB

   InnoDB logs the last deadlock with full details — which transactions were involved, which locks they held, which locks they were waiting for, and which transaction was rolled back.

   -- Get the full InnoDB status (includes deadlock section)
   SHOW ENGINE INNODB STATUS\G
   
   -- Key sections to read:
   -- 'LATEST DETECTED DEADLOCK'
   --   TRANSACTION 1: shows the SQL, locks held, lock waiting for
   --   TRANSACTION 2: shows the SQL, locks held, lock waiting for
   --   'WE ROLL BACK TRANSACTION N': which one InnoDB killed
   
   -- Enable deadlock logging to error log (persistent)
   SET GLOBAL innodb_print_all_deadlocks = ON;

   Copy
2. 02

   step 2

   Identify the conflicting lock order

   The deadlock report shows the exact rows and indexes involved. Compare the lock acquisition order of both transactions — they're locking the same resources in opposite order.

   -- Example deadlock scenario:
   -- Transaction A: UPDATE orders SET status='shipped' WHERE user_id=1;
   --                UPDATE inventory SET qty=qty-1 WHERE product_id=42;
   
   -- Transaction B: UPDATE inventory SET qty=qty-1 WHERE product_id=42;
   --                UPDATE orders SET status='shipped' WHERE user_id=2;
   
   -- Both lock orders -> inventory in different positions
   -- Fix: always lock orders first, then inventory
   -- Transaction A & B should both do:
   --   UPDATE orders ... (locks orders row first)
   --   UPDATE inventory ... (locks inventory row second)

   Copy
3. 03

   step 3

   Add retry logic for deadlocked transactions

   Deadlocks can't be 100% eliminated in concurrent systems. The rolled-back transaction should be retried — it will succeed because the competing transaction has completed and released its locks.

   import time, random
   import mysql.connector
   from mysql.connector import errors
   
   def execute_with_retry(cursor, conn, sql, params, max_retries=3):
       for attempt in range(max_retries):
           try:
               cursor.execute(sql, params)
               conn.commit()
               return
           except errors.DatabaseError as e:
               if e.errno == 1213 and attempt < max_retries - 1:
                   conn.rollback()
                   time.sleep(random.uniform(0.01, 0.1 * (attempt + 1)))
               else:
                   raise

   Copy
4. 04

   step 4

   Reduce lock scope and duration

   The less time a transaction holds locks, the smaller the deadlock window. Keep transactions short, avoid user interaction mid-transaction, and use the narrowest possible WHERE clause.

   -- BAD: locks many rows with a range scan
   START TRANSACTION;
   SELECT * FROM orders WHERE created_at > '2026-01-01' FOR UPDATE;
   -- ... application logic ...
   UPDATE orders SET processed = 1 WHERE created_at > '2026-01-01';
   COMMIT;
   
   -- GOOD: lock only the specific rows you need
   START TRANSACTION;
   SELECT * FROM orders WHERE id IN (101, 102, 103) FOR UPDATE;
   UPDATE orders SET processed = 1 WHERE id IN (101, 102, 103);
   COMMIT;
   
   -- GOOD: add an index so the lock targets specific rows
   ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

   Copy

How to verify the fix

• SHOW ENGINE INNODB STATUS shows no new deadlocks after the fix.
• Application logs confirm retry logic is catching and recovering from error 1213.
• Transactions that previously deadlocked now acquire locks in a consistent order.
• innodb_print_all_deadlocks is ON so future deadlocks are logged for monitoring.