MySQL Deadlock Analysis: Reading InnoDB Status and Writing Deadlock-Safe Code

SHOW ENGINE INNODB STATUS\G

------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-11-27 09:15:33 0x7f8b2c001700
*** (1) TRANSACTION:
TRANSACTION 421938, ACTIVE 0 sec starting index read
MySQL thread id 142, OS thread handle 140234..., query id 8291 ...
UPDATE orders SET status='shipped' WHERE id=100
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 23 page no 4 n bits 72 index PRIMARY of table `shop`.`orders`
lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: ... id=100
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 6 n bits 72 index PRIMARY of table `shop`.`shipments`
lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: ... order_id=100
*** (2) TRANSACTION:
TRANSACTION 421939, ACTIVE 0 sec starting index read
UPDATE shipments SET tracking='1Z...' WHERE order_id=100
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY of table `shop`.`shipments` lock_mode X locks rec but not gap
Record lock ... order_id=100
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY of table `shop`.`orders` lock_mode X locks rec but not gap
Record lock ... id=100
*** WE ROLL BACK TRANSACTION (2)

-- WRONG: Different code paths lock in different order
-- Path A: UPDATE orders → UPDATE shipments
-- Path B: UPDATE shipments → UPDATE orders

-- RIGHT: Always lock in the same order across all code paths
-- Rule: Always lock orders BEFORE shipments
BEGIN;
UPDATE orders SET status='shipped' WHERE id=100;
UPDATE shipments SET tracking='1Z...' WHERE order_id=100;
COMMIT;

-- Table without index on status
CREATE TABLE jobs (
  id BIGINT PRIMARY KEY,
  status VARCHAR(20),
  created_at DATETIME
);

-- This UPDATE scans all rows (no index on status),
-- taking next-key locks on everything in its range
UPDATE jobs SET status='done' WHERE status='pending' AND created_at < NOW();

-- Fix: add index
ALTER TABLE jobs ADD INDEX idx_status_created (status, created_at);

-- Pattern that causes deadlocks in high-concurrency queues:
BEGIN;
SELECT * FROM jobs WHERE status='pending' LIMIT 1 FOR UPDATE;
-- Both TX1 and TX2 might grab the same row gap, then deadlock
UPDATE jobs SET status='running' WHERE id=?;
COMMIT;

-- Fix: use SKIP LOCKED (MySQL 8.0+)
BEGIN;
SELECT * FROM jobs WHERE status='pending' LIMIT 1 FOR UPDATE SKIP LOCKED;
UPDATE jobs SET status='running' WHERE id=?;
COMMIT;

[mysqld]
innodb_print_all_deadlocks = ON  -- Log ALL deadlocks (not just latest)

def execute_with_retry(conn, fn, max_retries=3):
    """Retry transaction on deadlock (MySQL error 1213)."""
    for attempt in range(max_retries):
        try:
            with conn.cursor() as cur:
                fn(cur)
            conn.commit()
            return
        except pymysql.err.OperationalError as e:
            if e.args[0] == 1213:  # ER_LOCK_DEADLOCK
                conn.rollback()
                if attempt == max_retries - 1:
                    raise
                time.sleep(0.05 * (2 ** attempt))  # Exponential backoff
            else:
                raise