WARNING

How to Fix MySQL Error 1205: Lock Wait Timeout Exceeded

Quick Fix Summary

TL;DR

Kill the blocking transaction and increase innodb_lock_wait_timeout.

A transaction is waiting longer than the configured timeout to acquire a lock held by another transaction. This prevents data modification but doesn't cause data loss.

Diagnosis & Causes

Long-running UPDATE or DELETE without proper indexing.

Uncommitted transactions holding locks open.

Application logic causing circular lock dependencies.

Explicit table locks (LOCK TABLES) not released.

Insufficient innodb_lock_wait_timeout for batch operations.

Recovery Steps

Step 1: Identify and Kill the Blocking Transaction

First, find the transaction holding the lock and terminate it to immediately unblock production.

sql

SELECT * FROM information_schema.INNODB_TRX WHERE trx_state = 'RUNNING' ORDER BY trx_started ASC LIMIT 1;
-- Note the `trx_mysql_thread_id` from the result, then:
KILL <thread_id>;

Step 2: Increase the Global Lock Wait Timeout (Temporary)

Raise the timeout to allow long-running but valid transactions to complete, preventing immediate errors.

sql

SET GLOBAL innodb_lock_wait_timeout = 120; -- Increase from default 50 seconds to 120

Step 3: Analyze and Optimize the Problematic Query

Find the slow query causing the lock and optimize it with better indexes or batching.

sql

SHOW ENGINE INNODB STATUS\G
-- Look in the 'TRANSACTIONS' and 'LATEST DETECTED DEADLOCK' sections.
EXPLAIN FORMAT=JSON <your_problem_query>;

Step 4: Implement Permanent Configuration and Monitoring

Update the MySQL configuration file and set up monitoring to prevent recurrence.

bash

# Edit /etc/my.cnf or /etc/mysql/my.cnf
[mysqld]
innodb_lock_wait_timeout=120
innodb_print_all_deadlocks=ON
# Then restart MySQL:
sudo systemctl restart mysql

Architect's Pro Tip

"Set `innodb_print_all_deadlocks=ON` in your config. This logs all deadlocks to the error log, turning intermittent timeouts into traceable patterns for root-cause fixes."

Frequently Asked Questions

Will killing a transaction cause data corruption?

No. MySQL rolls back the killed transaction entirely, preserving data integrity. The operations performed by that transaction are not applied.

What's a safe value for innodb_lock_wait_timeout?

For OLTP systems, 30-120 seconds. For heavy batch/ETL jobs, you may need 300+ seconds, but prioritize optimizing the query logic first.

How is this different from a deadlock (ERROR 1213)?

A deadlock is a cycle of locks where MySQL picks a victim to roll back. Error 1205 is a simple, one-way wait that timed out.

Related MySQL Guides

ERROR 1040