How to fix ERROR 1213: Deadlock found when trying to get lock; try restarting transaction in MySQL

SHOW ENGINE INNODB STATUS;

SET GLOBAL innodb_print_all_deadlocks = 1;

async function updateUserWithRetry(userId, data, maxRetries = 3) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      await db.query('BEGIN');
      // Your transaction logic here
      await db.query('UPDATE users SET ... WHERE id = ?', [userId, ...]);
      await db.query('COMMIT');
      return;
    } catch (error) {
      if (error.code === 'ER_LOCK_DEADLOCK' && attempt < maxRetries) {
        // Deadlock detected, retry with exponential backoff
        const delay = Math.pow(2, attempt) * 100; // 200ms, 400ms, 800ms
        await new Promise(r => setTimeout(r, delay));
        continue;
      }
      throw error; // Other error or max retries exceeded
    }
  }
}

-- BAD: No index, full table scan locks all rows
SELECT * FROM orders WHERE customer_id = 123 FOR UPDATE;

-- GOOD: Index allows targeting specific rows
CREATE INDEX idx_customer_id ON orders(customer_id);
SELECT * FROM orders WHERE customer_id = 123 FOR UPDATE;

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 FOR UPDATE;
-- Look for "Using index" or "ref" in the output

// BAD: Long transaction with unrelated work
async function badExample() {
  await db.query('BEGIN');

  // These locks are held for seconds while doing external work
  const user = await db.query('SELECT * FROM users WHERE id = ? FOR UPDATE', [userId]);

  const result = await slowExternalAPI.call(); // Locks held during this!

  await db.query('UPDATE users SET last_api_call = ? WHERE id = ?', [result, userId]);
  await db.query('COMMIT');
}

// GOOD: Quick transaction, external work outside
async function goodExample() {
  const result = await slowExternalAPI.call(); // NO locks held here

  await db.query('BEGIN');

  const user = await db.query('SELECT * FROM users WHERE id = ? FOR UPDATE', [userId]);
  await db.query('UPDATE users SET last_api_call = ? WHERE id = ?', [result, userId]);

  await db.query('COMMIT'); // Locks released immediately
}

// BAD: Inconsistent access order
// Transaction 1
await db.query('UPDATE users SET balance = balance - ? WHERE id = ?', [amount, userId1]);
await db.query('UPDATE users SET balance = balance + ? WHERE id = ?', [amount, userId2]);

// Transaction 2 (accesses in opposite order)
await db.query('UPDATE users SET balance = balance - ? WHERE id = ?', [amount, userId2]);
await db.query('UPDATE users SET balance = balance + ? WHERE id = ?', [amount, userId1]);

// GOOD: Always access rows in the same order (e.g., by ID)
// Both transactions do this:
const [first, second] = [userId1, userId2].sort((a, b) => a - b);
await db.query('UPDATE users SET balance = balance - ? WHERE id = ?', [amount, first]);
await db.query('UPDATE users SET balance = balance + ? WHERE id = ?', [amount, second]);

-- Use READ COMMITTED isolation to avoid gap locks (faster but less strict)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- Your queries here use fewer locks
COMMIT;

-- MySQL 8.0+: Use NOWAIT to fail fast instead of waiting for locks
SELECT * FROM orders WHERE id = 123 FOR UPDATE NOWAIT;

-- MySQL 8.0+: Use SKIP LOCKED to ignore locked rows
SELECT * FROM orders WHERE id = 123 FOR UPDATE SKIP LOCKED;