Why should you care?

Concurrent transfers between accounts A↔B in opposite order cause deadlocks — apps must retry, not assume success first try.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

-- Session 1                    -- Session 2
START TRANSACTION;                START TRANSACTION;
UPDATE accounts SET balance_inr = balance_inr - 100 WHERE account_id = 1;
                                  UPDATE accounts SET balance_inr = balance_inr - 100 WHERE account_id = 2;
UPDATE accounts SET balance_inr = balance_inr + 100 WHERE account_id = 2;
                                  UPDATE accounts SET balance_inr = balance_inr + 100 WHERE account_id = 1;
-- One session gets ERROR 1213 Deadlock found

What happened?

• Each session locks one account then waits for the other — circular wait.
• InnoDB kills one txn; survivor continues.
• Fix: always lock accounts in same id order.

Practice next

1. Two Workbench tabs with accounts table.
2. Run opposing UPDATE order as above.
3. Note 1213 error and which txn rolled back.
4. SHOW ENGINE INNODB STATUS after deadlock — read LATEST DETECTED DEADLOCK.
5. Use SELECT ... FOR UPDATE in fixed id order before updates.

Remember

Deadlock = circular lock wait. InnoDB picks a victim and rolls back. Consistent lock ordering prevents many deadlocks.

DataFlow concurrent transfers

API retries transfer up to 3 times on 1213 during festival traffic.

Outcome: Users see success after harmless automatic retry.