How to fix MySQL "ERROR 1205: Lock wait timeout exceeded"

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Common causes

• A transaction left open by a script, console session or crashed worker
• Long batch UPDATE or DELETE statements holding locks for a long time
• UPDATE or DELETE with a WHERE clause on an unindexed column, locking many rows
• External API calls or slow code inside an open transaction
• Many workers updating the same hot rows, such as a counter
• Large imports or ALTER TABLE running alongside normal traffic

How to fix it

1. Find the blocking transaction. Run SELECT * FROM sys.innodb_lock_waits\G in MySQL 8. It shows the waiting query, the blocking thread and its process ID.
2. Look at long-open transactions. Run SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx ORDER BY trx_started;. Old trx_started times are the suspects.
3. End the blocker if safe. If a session is idle with an open transaction, end it with KILL <thread_id>;. Its changes are rolled back, so make sure that is acceptable.
4. Index the WHERE columns. Run EXPLAIN on the UPDATE or DELETE. If it scans many rows, add an index on the filtered columns so InnoDB locks only the rows it changes.
5. Keep transactions short. Commit as soon as the writes are done. Do not call external APIs, send email or wait for user input inside a transaction.
6. Batch big changes and retry. Split large updates into chunks of a few thousand rows. In code, catch 1205 and 1213 and retry the whole transaction a few times.

Find who is blocking whom (MySQL 8)

SELECT waiting_pid, waiting_query,
       blocking_pid, blocking_query,
       wait_age
FROM sys.innodb_lock_waits;

-- if the blocker is an abandoned session:
-- KILL <blocking_pid>;

How to stop it happening again

• Commit or roll back transactions promptly
• Index columns used in UPDATE and DELETE filters
• Run bulk jobs in small batches during quiet hours
• Add retry logic for lock timeouts and deadlocks