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

Connect to MySQL and run:

SHOW FULL PROCESSLIST;

Look for processes with a long Time value (especially those in a "sleep" state with no active command). These are likely holding locks. Note the process ID (first column) of any suspicious connection.

Once you've identified the blocking process ID, terminate it:

KILL <process_id>;

For example:

KILL 42;

Make sure this connection isn't actively processing important work before killing it. Check the Command and Time columns to verify it's idle.

Ensure your application code always commits or rolls back transactions, even in exception handlers:

const connection = await mysql.createConnection(config);
try {
  await connection.query("START TRANSACTION");
  // Your query logic here
  await connection.query("COMMIT");
} catch (error) {
  await connection.query("ROLLBACK");
  throw error;
} finally {
  connection.close();
}

Enable autocommit by default to prevent accidentally holding locks:

SET SESSION autocommit = 1;

Missing indexes force InnoDB to scan entire tables, locking far more rows than necessary. Identify columns frequently used in WHERE clauses of UPDATE/DELETE statements:

SHOW CREATE TABLE your_table;

Add indexes for columns in lock-heavy queries:

CREATE INDEX idx_column_name ON your_table(column_name);

This reduces the number of rows locked per operation, decreasing contention.

If timeouts occur despite optimization, you can increase the timeout (default 50 seconds). This is a short-term fix; address root causes first.

Check current value:

SHOW VARIABLES LIKE "innodb_lock_wait_timeout";

Increase the timeout at runtime (session-level):

SET SESSION innodb_lock_wait_timeout = 100;

Or globally:

SET GLOBAL innodb_lock_wait_timeout = 100;

For permanent configuration, add to /etc/my.cnf:

[mysqld]
innodb_lock_wait_timeout = 100

By default, MySQL rolls back only the statement that timed out, not the entire transaction (since version 5.0.13). If you prefer automatic rollback of the entire transaction, set:

SET GLOBAL innodb_rollback_on_timeout = 1;

Or in /etc/my.cnf:

[mysqld]
innodb_rollback_on_timeout = 1

This ensures the transaction doesn't leave behind partially committed data. However, the default behavior (statement-only rollback) is usually preferred to preserve the rest of the transaction.