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

-- Check if deadlock detection is enabled (default is ON)
SHOW VARIABLES LIKE 'innodb_deadlock_detect';

-- Enable logging of all deadlocks to error log (not just the last one)
SET GLOBAL innodb_print_all_deadlocks = ON;

-- Verify the setting
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

# Check MySQL error log for deadlock details
sudo tail -f /var/log/mysql/error.log

# Or check the log file specified in MySQL configuration
mysql -e "SHOW VARIABLES LIKE 'log_error';"

# View the most recent deadlock
SHOW ENGINE INNODB STATUS;
# Look for the LATEST DETECTED DEADLOCK section

-- Check current isolation level for session
SHOW VARIABLES LIKE 'transaction_isolation';

-- View InnoDB status including deadlock information
SHOW ENGINE INNODB STATUS\G

-- Look for this section in the output:
-- *** (1) TRANSACTION:
-- *** (2) TRANSACTION:
-- *** WE ROLL BACK TRANSACTION (1)

*** (1) TRANSACTION:
TRX ID 123456, ACTIVE 2 sec updating or deleting
MySQL thread id 123, query id 456

*** (2) TRANSACTION:
TRX ID 123457, ACTIVE 1 sec updating or deleting
MySQL thread id 124, query id 457

*** WE ROLL BACK TRANSACTION (1)

// Node.js with mysql2/promise - retry with exponential backoff
async function executeWithRetry(query, params, maxRetries = 3) {
  let lastError;
  
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      const connection = await mysql.createConnection(config);
      const result = await connection.execute(query, params);
      await connection.end();
      return result;
    } catch (error) {
      lastError = error;
      
      // Check if it's a deadlock error (code 1213)
      if (error.code === "ER_LOCK_DEADLOCK" || error.errno === 1213) {
        if (attempt < maxRetries) {
          // Exponential backoff: 100ms, 200ms, 400ms
          const delay = 100 * Math.pow(2, attempt - 1);
          console.log(`Deadlock on attempt ${attempt}, retrying in ${delay}ms...`);
          await new Promise(resolve => setTimeout(resolve, delay));
        } else {
          throw new Error(`Failed after ${maxRetries} attempts: ${error.message}`);
        }
      } else {
        throw error; // Non-deadlock error, don't retry
      }
    }
  }
  
  throw lastError;
}

// Usage
try {
  const result = await executeWithRetry(
    'UPDATE accounts SET balance = balance - ? WHERE id = ?',
    [100, account_id]
  );
} catch (error) {
  console.error('Transaction failed:', error);
}

# Python with mysql-connector-py
import mysql.connector
import time

def execute_with_retry(query, params, max_retries=3):
    last_error = None
    
    for attempt in range(1, max_retries + 1):
        connection = None
        try:
            connection = mysql.connector.connect(**config)
            cursor = connection.cursor()
            cursor.execute(query, params)
            connection.commit()
            result = cursor.fetchall()
            cursor.close()
            return result
        except mysql.connector.Error as error:
            last_error = error
            
            # Check if it's a deadlock (error code 1213)
            if error.errno == 1213:
                if attempt < max_retries:
                    # Exponential backoff
                    delay = 0.1 * (2 ** (attempt - 1))
                    print(f"Deadlock on attempt {attempt}, retrying in {delay}s...")
                    time.sleep(delay)
                else:
                    raise Exception(f"Failed after {max_retries} attempts: {error}")
            else:
                raise
        finally:
            if connection and connection.is_connected():
                connection.close()
    
    raise last_error

// PHP with PDO
function executeWithRetry($pdo, $query, $params, $maxRetries = 3) {
    $lastError = null;
    
    for ($attempt = 1; $attempt <= $maxRetries; $attempt++) {
        try {
            $stmt = $pdo->prepare($query);
            $stmt->execute($params);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            $lastError = $e;
            
            // Check for deadlock error (SQLSTATE 40001)
            if ($e->getCode() == '40001' || strpos($e->getMessage(), '1213') !== false) {
                if ($attempt < $maxRetries) {
                    $delay = 100 * pow(2, $attempt - 1); // milliseconds
                    echo "Deadlock on attempt $attempt, retrying in {$delay}ms...";
                    usleep($delay * 1000);
                } else {
                    throw new Exception("Failed after $maxRetries attempts: {$e->getMessage()}");
                }
            } else {
                throw $e; // Non-deadlock error
            }
        }
    }
    
    throw $lastError;
}

// BAD: Inconsistent ordering causes deadlocks
// Transaction 1: locks user -> locks account
async function transfer1() {
  await db.query('UPDATE users SET last_tx = NOW() WHERE id = ?', [user1]);
  await db.query('UPDATE accounts SET balance = balance - 100 WHERE id = ?', [account1]);
}

// Transaction 2: locks account -> locks user (opposite order!)
async function transfer2() {
  await db.query('UPDATE accounts SET balance = balance + 100 WHERE id = ?', [account1]);
  await db.query('UPDATE users SET last_tx = NOW() WHERE id = ?', [user2]);
}

// GOOD: Consistent ordering prevents deadlocks
async function transfer1Ordered() {
  await db.query('UPDATE accounts SET balance = balance - 100 WHERE id = ?', [account1]);
  await db.query('UPDATE users SET last_tx = NOW() WHERE id = ?', [user1]);
}

async function transfer2Ordered() {
  await db.query('UPDATE accounts SET balance = balance + 100 WHERE id = ?', [account1]);
  await db.query('UPDATE users SET last_tx = NOW() WHERE id = ?', [user2]);
}

-- Order by primary key/ID to ensure consistent lock order
-- When updating multiple rows, lock them in ID order
UPDATE accounts 
SET balance = balance - 100 
WHERE id IN (1, 2, 3)
ORDER BY id;  -- Explicit ordering

-- For complex transactions, acquire locks in consistent order
START TRANSACTION;
  -- First: lock all accounts in order
  SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
  -- Then: update in same order
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Check query execution to see how many rows are scanned/locked
EXPLAIN SELECT * FROM transactions WHERE user_id = 123 AND status = 'pending';

-- If scanning many rows, add indexes
CREATE INDEX idx_transactions_user_status ON transactions(user_id, status);

-- Before update - without index (locks many rows)
UPDATE transactions SET status = 'processed' WHERE user_id = 123;
-- This locks ALL rows for user 123 until the transaction ends

-- After index - scans fewer rows and locks only matching ones
EXPLAIN UPDATE transactions SET status = 'processed' WHERE user_id = 123;

-- Verify index is being used
SHOW INDEX FROM transactions;

-- For high-traffic tables, consider composite indexes
CREATE INDEX idx_accounts_user_updated ON accounts(user_id, updated_at);

-- Use EXPLAIN to verify index usage
EXPLAIN SELECT * FROM accounts WHERE user_id = 123 AND updated_at > NOW() - INTERVAL 1 HOUR;

// BAD: Long transaction with business logic inside
async function processPayment(paymentId) {
  await db.query('START TRANSACTION');
  
  // This validation happens INSIDE the transaction, holding locks
  const payment = await db.query('SELECT * FROM payments WHERE id = ?', [paymentId]);
  const isValid = await externalValidationService.check(payment);
  if (!isValid) throw new Error('Invalid payment');
  
  // Business logic delay while holding locks
  await sendEmail(payment.email);
  
  // Finally update database
  await db.query('UPDATE payments SET status = "processed" WHERE id = ?', [paymentId]);
  await db.query('UPDATE accounts SET balance = balance + ? WHERE id = ?', [payment.amount, payment.account_id]);
  await db.query('COMMIT');
}

// GOOD: Move logic outside transaction, keep locks short
async function processPaymentOptimized(paymentId) {
  // Validate OUTSIDE of transaction
  const payment = await db.query('SELECT * FROM payments WHERE id = ?', [paymentId]);
  const isValid = await externalValidationService.check(payment);
  if (!isValid) throw new Error('Invalid payment');
  
  // Send email OUTSIDE of transaction
  await sendEmail(payment.email);
  
  // Keep database changes SHORT
  await db.query('START TRANSACTION');
  
  // Only database operations in transaction
  await db.query('UPDATE payments SET status = "processed" WHERE id = ?', [paymentId]);
  await db.query('UPDATE accounts SET balance = balance + ? WHERE id = ?', [payment.amount, payment.account_id]);
  
  await db.query('COMMIT');
}

// Database isolation for short operations
START TRANSACTION;
  -- Lock and update only necessary rows
  UPDATE accounts SET balance = balance - 100 WHERE id = 123 FOR UPDATE;
  UPDATE accounts SET balance = balance + 100 WHERE id = 456 FOR UPDATE;
COMMIT;
-- Transaction ends immediately - locks are released

-- Check current isolation level
SHOW VARIABLES LIKE 'transaction_isolation';

-- Default is REPEATABLE READ - most common cause of deadlocks in high concurrency
-- Possible values:
-- - READ UNCOMMITTED (least isolation, fewest locks)
-- - READ COMMITTED (medium isolation, fewer locks)
-- - REPEATABLE READ (high isolation, many locks) - DEFAULT
-- - SERIALIZABLE (serialized, maximum locks)

-- Change isolation level for current session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Change globally (affects new connections)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Verify change
SHOW VARIABLES LIKE 'transaction_isolation';

-- READ COMMITTED vs REPEATABLE READ example
BEGIN;
  SELECT * FROM accounts WHERE balance < 100;  -- Snapshot at READ COMMITTED
COMMIT;

-- In READ COMMITTED: Only locks rows being modified, not rows being read
-- In REPEATABLE READ: May lock range of rows affecting reads (phantom reads)

-- For most applications, keep REPEATABLE READ (default) and fix deadlocks with:
-- 1. Retry logic
-- 2. Consistent ordering
-- 3. Better indexes