MySQL의 교착 상태

-- Transaction A: locks row 1, then tries to lock row 2
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- locks row 1
-- (Transaction B runs here and locks row 2)
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- waits for B's lock on row 2

-- Transaction B: locks row 2, then tries to lock row 1
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- locks row 2
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- waits for A's lock on row 1
-- Deadlock: A waits for B, B waits for A

SHOW ENGINE INNODB STATUS\G

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 0 sec starting index read
MySQL thread id 10, query: UPDATE accounts SET balance = balance + 100 WHERE id = 2
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 87 page no 3 n bits 72 index PRIMARY of table accounts trx id 12345 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: ...

*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 0 sec starting index read
MySQL thread id 11, query: UPDATE accounts SET balance = balance - 100 WHERE id = 1
*** (2) HOLDS THE LOCK(S): ...
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: ...
*** WE ROLL BACK TRANSACTION (1)

-- Both transactions lock in order: lower ID first
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- lower ID first
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- then higher ID
COMMIT;

-- Fetch data outside the transaction, compute, then write in one short transaction
BEGIN;
UPDATE orders SET status = 'shipped', shipped_at = NOW() WHERE id = ?;
COMMIT;
-- Don't: read+compute+write all inside one long-running transaction

BEGIN;
SELECT * FROM orders WHERE id = 42 FOR UPDATE;  -- acquires write lock now
-- ... application logic ...
UPDATE orders SET status = 'processed' WHERE id = 42;
COMMIT;

MAX_RETRIES = 3
for attempt in range(MAX_RETRIES):
    try:
        with connection.transaction():
            do_work()
        break
    except DeadlockError:
        if attempt == MAX_RETRIES - 1:
            raise
        time.sleep(0.1 * (attempt + 1))  # brief backoff

-- Locking a non-existent id doesn't just lock a gap -- it locks up to
-- the next existing row too (a gap lock, not a plain row lock).
-- Locking an id that DOES exist takes only a record lock, no gap.
SELECT * FROM orders WHERE id = 6 FOR UPDATE;

-- Switching to READ COMMITTED reduces gap locking
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
-- 50 seconds by default

SET SESSION innodb_lock_wait_timeout = 10;

SELECT * FROM performance_schema.data_locks\G
SELECT * FROM performance_schema.data_lock_waits\G