How to fix ERROR 1205: Lock wait timeout exceeded; try restarting transaction in MySQL

-- View detailed InnoDB transaction and lock information
SHOW ENGINE INNODB STATUS;

-- Look for the "TRANSACTIONS" section which shows:
-- - All active transactions
-- - Locks each transaction holds
-- - Locks each transaction is waiting for
-- - Lock wait time in seconds

-- Example output shows:
-- TRANSACTIONS
-- trx id 12345, active 120 sec, process no 1234, OS thread id 5678
-- ...
-- LOCK WAIT 50 sec active, the following trx is waiting for this lock:

-- Show which transactions are waiting and which are blocking
SELECT
  waiting_trx.trx_id AS waiting_transaction,
  waiting_thread.processlist_id AS waiting_connection_id,
  waiting_thread.processlist_user AS waiting_user,
  waiting_thread.processlist_info AS waiting_query,
  blocking_trx.trx_id AS blocking_transaction,
  blocking_thread.processlist_id AS blocking_connection_id,
  blocking_thread.processlist_user AS blocking_user,
  blocking_thread.processlist_info AS blocking_query
FROM performance_schema.data_lock_waits
JOIN information_schema.innodb_trx waiting_trx
  ON waiting_trx.trx_id = data_lock_waits.requesting_trx_id
JOIN information_schema.innodb_trx blocking_trx
  ON blocking_trx.trx_id = data_lock_waits.blocking_trx_id
JOIN performance_schema.threads waiting_thread
  ON waiting_thread.thread_id = data_lock_waits.requesting_thread_id
JOIN performance_schema.threads blocking_thread
  ON blocking_thread.thread_id = data_lock_waits.blocking_thread_id;

-- List all active connections
SHOW PROCESSLIST;

-- Or use information_schema for more detail
SELECT
  id,
  user,
  host,
  db,
  command,
  time,
  state,
  info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;

-- Kill a specific connection (replace 12345 with actual connection ID)
KILL 12345;

-- Kill a query without closing the connection (MySQL 5.7.8+)
KILL QUERY 12345;

-- For stuck connections that don't respond to KILL:
KILL CONNECTION 12345;

-- Enable slow query log to capture queries taking longer than 2 seconds
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

-- View current slow query log file location
SHOW VARIABLES LIKE 'slow_query_log_file';

-- Check query execution time with EXPLAIN
EXPLAIN SELECT * FROM large_table WHERE indexed_column = 'value';

-- If the EXPLAIN shows "type: ALL", the query is doing a full table scan
-- Add an index to speed it up:
CREATE INDEX idx_column ON large_table(indexed_column);

-- WRONG: Slow operations inside transaction hold locks longer
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- This performs a network call to external service (slow!)
-- Result = call_external_payment_service();
UPDATE accounts SET transaction_status = 'pending' WHERE id = 1;
COMMIT;

-- CORRECT: Fast DB work in transaction, slow work outside
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Now do slow work after releasing locks
-- Result = call_external_payment_service();

-- Update status in a new, quick transaction
START TRANSACTION;
UPDATE accounts SET transaction_status = 'completed' WHERE id = 1;
COMMIT;

-- View current timeout setting (default is 50 seconds)
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- Increase for current session (changes revert when connection closes)
SET SESSION innodb_lock_wait_timeout = 120;

-- Permanently increase in my.cnf / mysql.cnf
-- Add to [mysqld] section:
-- innodb_lock_wait_timeout = 120

-- Verify after server restart
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- Check current setting (default is OFF/0)
SHOW VARIABLES LIKE 'innodb_rollback_on_timeout';

-- Enable in my.cnf / mysql.cnf (requires restart)
-- Add to [mysqld] section:
-- innodb_rollback_on_timeout = 1

-- Restart MySQL for the change to take effect

// Example: Node.js with exponential backoff retry
async function executeWithRetry(query, maxRetries = 3) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await executeQuery(query);
    } catch (error) {
      if (error.code === 'ER_LOCK_WAIT_TIMEOUT' && attempt < maxRetries) {
        // Wait before retrying: 100ms, 200ms, 400ms, etc.
        const delayMs = 100 * Math.pow(2, attempt - 1);
        console.log(`Lock timeout, retrying in ${delayMs}ms (attempt ${attempt}/${maxRetries})`);
        await new Promise(resolve => setTimeout(resolve, delayMs));
        continue;
      }
      throw error; // Other errors or final attempt failed
    }
  }
}

// Usage
try {
  await executeWithRetry('UPDATE users SET last_login = NOW() WHERE id = 123');
} catch (error) {
  console.error('Transaction failed after retries:', error);
}

-- Identify most-contended tables using Performance Schema
SELECT
  object_schema,
  object_name,
  count_star AS lock_waits,
  count_read AS read_waits,
  count_write AS write_waits
FROM performance_schema.table_io_waits_summary_by_table
WHERE count_star > 0
ORDER BY count_star DESC
LIMIT 10;

-- Check lock wait times per table
SELECT
  object_schema,
  object_name,
  count_star,
  sum_timer_wait / 1000000000000 AS wait_time_seconds
FROM performance_schema.table_io_waits_summary_by_table
WHERE count_star > 0
ORDER BY sum_timer_wait DESC;