A SELECT ... FOR UPDATE loop is causing deadlocks in production — diagnose and fix it

Question

Accepted Answer

Section 15: Transactions & Locking (Real Production Bugs)



  104
   A SELECT ... FOR UPDATE loop is causing deadlocks in production — diagnose and fix it
  Level: Very Hard — A real on-call incident; you must reason about the exact interleaving that causes the circular wait.

  
     Scenario: A money-transfer service locks two accounts with SELECT ... FOR UPDATE before updating balances. Under load, you're getting "ERROR 1213: Deadlock found when trying to get lock." a few times per minute. The logic looks correct. Why are transactions deadlocking, and how do you fix it?
  

   Sample Data — accounts
  
    
      
        acc_idownerbalance
      
      
        1Rumman10000
        2Krushna5000
      
    
  

   The Deadlock — Exact Interleaving
  Two transfers happen at once, in opposite directions. Each locks accounts in the order the money flows:
  
    
      
        TimeTxn A (transfer 1 → 2)Txn B (transfer 2 → 1)
      
      
        t1Locks acc 1 (FOR UPDATE)Locks acc 2 (FOR UPDATE)
        t2Wants acc 2 → waitsWants acc 1 → waits
        t3Circular wait → DEADLOCK (InnoDB kills one)
      
    
  

  The root cause: The transactions acquire locks in inconsistent order. Txn A locks 1→2; Txn B locks 2→1. Each holds what the other needs. InnoDB detects the cycle and rolls back a "victim."

   The Buggy Code
-- Txn A: transfer from acc 1 to acc 2
START TRANSACTION;
SELECT balance FROM accounts WHERE acc_id = 1 FOR UPDATE;  -- locks 1
SELECT balance FROM accounts WHERE acc_id = 2 FOR UPDATE;  -- wants 2
UPDATE accounts SET balance = balance - 100 WHERE acc_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE acc_id = 2;
COMMIT;

-- Txn B: transfer from acc 2 to acc 1 (locks in the OPPOSITE order)
START TRANSACTION;
SELECT balance FROM accounts WHERE acc_id = 2 FOR UPDATE;  -- locks 2
SELECT balance FROM accounts WHERE acc_id = 1 FOR UPDATE;  -- wants 1
-- ... DEADLOCK ...

   The Fix — Always Lock in a Consistent Order
-- FIX: lock accounts in a DETERMINISTIC order (e.g., ascending acc_id)
-- regardless of transfer direction. Both transactions now agree on order.
START TRANSACTION;

-- Lock the LOWER id first, always
SELECT balance FROM accounts
WHERE acc_id IN (1, 2)
ORDER BY acc_id          -- deterministic lock acquisition order
FOR UPDATE;

-- Now safely apply the transfer (direction handled in the UPDATEs)
UPDATE accounts SET balance = balance - 100 WHERE acc_id = 1;  -- sender
UPDATE accounts SET balance = balance + 100 WHERE acc_id = 2;  -- receiver

COMMIT;

  
     The golden rule: If all transactions acquire locks in the same global order (e.g., always ascending primary key), a circular wait is impossible — so deadlocks from lock-ordering vanish. In app code: sort the ids before locking (min(a,b) then max(a,b)).
  

   Defense in Depth
  
     Consistent lock order — the primary fix (shown above).
     Retry logic — catch error 1213 and retry the transaction (deadlocks are transient).
     Keep transactions short — lock, update, commit fast; no user input or API calls mid-transaction.
     Diagnose with SHOW ENGINE INNODB STATUS → "LATEST DETECTED DEADLOCK".
  

   The Retry Pattern (pseudo-logic)
-- Application-side retry wrapper:
-- FOR attempt IN 1..3:
--     TRY:
--         START TRANSACTION;
--         ... locking + updates in consistent order ...
--         COMMIT;
--         BREAK;                     -- success
--     CATCH deadlock (error 1213):
--         ROLLBACK;
--         SLEEP(random 10-100ms);    -- backoff, then retry

  
     Interviewer follow-up: "Even with consistent lock order, you still see occasional deadlocks — why?" → Deadlocks can also arise from gap locks under REPEATABLE READ, secondary index locking, or foreign key checks locking parent rows. That's why retry logic is mandatory — you reduce deadlocks by design, but can never guarantee zero, so the app must handle them gracefully.

A SELECT ... FOR UPDATE loop is causing deadlocks in production — diagnose and fix it

Answer with Explanation

Section 15: Transactions & Locking (Real Production Bugs)

A SELECT ... FOR UPDATE loop is causing deadlocks in production — diagnose and fix it

Sample Data — `accounts`

The Deadlock — Exact Interleaving

The Buggy Code

The Fix — Always Lock in a Consistent Order

Defense in Depth

The Retry Pattern (pseudo-logic)

Time	Txn A (transfer 1 → 2)	Txn B (transfer 2 → 1)
t1	Locks acc 1 (FOR UPDATE)	Locks acc 2 (FOR UPDATE)
t2	Wants acc 2 → waits	Wants acc 1 → waits
t3	Circular wait → DEADLOCK (InnoDB kills one)

acc_id	owner	balance
1	Rumman	10000
2	Krushna	5000