A Real Deadlock: Error 1213, and the Lock-Order Fix

Two sessions, two rows, opposite locking order — and MySQL’s real response isn’t a hang, it’s an immediate, specific error.

A lock that makes a second session wait

-- Session A
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT SLEEP(20);
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Session B, started a few seconds into session A's sleep
UPDATE accounts SET balance = balance + 100 WHERE id = 1;

-- session A's real timestamps
session_a_start   2026-09-13 17:31:05
session_a_end     2026-09-13 17:31:25

-- session B's real timestamps
session_b_start   2026-09-13 17:31:14
session_b_end     2026-09-13 17:31:25

Session B’s UPDATE started at 17:31:14 — while session A’s FOR UPDATE lock on row 1 was still held — and didn’t finish until 17:31:25, the exact same instant session A committed. Session B wasn’t stuck or erroring; it was correctly waiting for a lock that session A was holding, and proceeded the moment that lock released.

-- checked while both sessions were live
SELECT * FROM performance_schema.data_lock_waits;

REQUESTING_ENGINE_TRANSACTION_ID  BLOCKING_ENGINE_TRANSACTION_ID
1851                               1850

Direct confirmation from the server itself: transaction 1851 (session B) is waiting on a lock held by transaction 1850 (session A) — not inference from timing, the actual recorded lock-wait relationship.

The bug: two sessions, opposite lock order, a real deadlock

-- Session A: locks row 1, then (after a delay) wants row 2
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT SLEEP(12);
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
COMMIT;

-- Session B: locks row 2, then (after a longer delay) wants row 1
START TRANSACTION;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
SELECT SLEEP(20);
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
COMMIT;

-- session A
a_locks_1   2026-09-13 17:42:44.712073
a_tries_2   2026-09-13 17:42:56.713882
a_got_2     2026-09-13 17:43:13.687709

-- session B
b_locks_2   2026-09-13 17:42:53.658025
b_tries_1   2026-09-13 17:43:13.659352
ERROR 1213 (40001) at line 7: Deadlock found when trying to get lock; try restarting transaction

Both sessions ended up waiting on each other: A held row 1 and wanted row 2 (which B held); B held row 2 and wanted row 1 (which A held). Neither could ever proceed by waiting longer — a genuine deadlock. MySQL didn’t hang: the instant B’s request for row 1 completed the cycle (17:43:13.659), InnoDB detected it and immediately killed session B’s transaction with a real, specific error. Session A’s blocked request for row 2 then succeeded 28 milliseconds later (17:43:13.687), the moment B’s rollback released its lock.

The fix: lock in a consistent order

-- Both sessions now lock row 1 before row 2, no exceptions
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- ... then row 2 ...
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
COMMIT;

-- session A (holds row 1 first, sleeps 10s, then locks row 2)
a_locks_1   2026-09-13 17:50:14.895280
a_tries_2   2026-09-13 17:50:24.897809
a_got_2     2026-09-13 17:50:24.898238

-- session B (also tries row 1 first, arriving mid-way through A's hold)
b_tries_1_first  2026-09-13 17:50:24.030004
b_got_1          2026-09-13 17:50:24.898945
b_got_2          2026-09-13 17:50:24.899975

No error this time. Session B requested row 1 at 17:50:24.030 and simply waited — the same kind of ordinary block from the first example — until session A committed at 17:50:24.898, then acquired both locks in the same order (1, then 2) without contention. With every transaction agreeing on lock order, the cycle that caused the earlier deadlock can never form: there’s no scenario where one session holds row 2 while waiting on row 1, because nothing ever locks row 2 first.

Practice

SELECT * FROM performance_schema.data_lock_waits;

Empty set (0.00 sec)

SELECT balance FROM accounts WHERE id = 1;

balance
500.00

SELECT * FROM accounts WHERE id IN (1, 2);

id  name   balance
1   Alice  500.00
2   Bob    300.00

Next: stored procedures and triggers — behavior that fires automatically, and what a syntax tutorial doesn’t show about it.