MySQL ERROR 1205: Lock Wait Timeout Exceeded - Find the Blocker

Error 1205 means your statement waited for a row lock held by another transaction and gave up. InnoDB waits up to innodb_lock_wait_timeout seconds, which is 50 by default. The query that fails is the victim; the real problem is usually the transaction holding the lock.

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

This is not a deadlock. Deadlocks are detected immediately and reported as error 1213. A lock wait is slower: one session simply sits on its locks, often because it is idle.

Step 1: find the blocker

On MySQL 5.7 and 8.0 the sys schema gives a readable answer:

SELECT wait_started, wait_age, locked_table, locked_type,
       waiting_pid, waiting_query,
       blocking_pid, blocking_query, sql_kill_blocking_connection
FROM sys.innodb_lock_waits\G

On MySQL 8.0 you can query the underlying tables, performance_schema.data_lock_waits and performance_schema.data_locks. On MariaDB and older versions, use information_schema.innodb_lock_waits and information_schema.innodb_trx.

To see open transactions, oldest first:

SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id,
       trx_rows_locked, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

A transaction with an old trx_started and a NULL trx_query is idle with locks still held. That is usually an application that opened a transaction, then went off to call an API, send an email or wait for user input. SHOW ENGINE INNODB STATUS\G shows the same data in its TRANSACTIONS section.

Step 2: relieve the pressure now

Once you know the blocking thread id, check what it is and kill it if it is safe:

SHOW PROCESSLIST;
KILL 12345;

Killing rolls back that transaction, so any uncommitted work is lost. Be careful with a long-running ALTER TABLE or bulk delete: rolling it back may take as long as it ran.

Step 3: fix the cause

Long transactions

Keep transactions short. Open them late, do only database work inside, and commit quickly. Never hold one across network calls or user interaction. Batch large updates into chunks:

UPDATE events SET archived = 1
WHERE archived = 0 AND id BETWEEN 1 AND 5000;
COMMIT;

Then repeat for the next range, rather than updating millions of rows in one statement.

Missing indexes

An UPDATE or DELETE whose WHERE cannot use an index scans the table and locks many rows it does not change, which makes collisions likely. Run EXPLAIN UPDATE ... (supported since MySQL 5.6) and add an index on the filtered columns.

Lost connections that keep locks

If an app crashes or its connection drops mid-transaction, the server holds the locks until it notices. Check wait_timeout and interactive_timeout and close connections cleanly. Pool settings that return connections to the pool with an open transaction are a known source.

Foreground jobs colliding

Cron jobs, imports and reports often run concurrently with web traffic. Schedule heavy jobs off-peak, and track them with cron monitoring so you notice overlap. See also the heartbeat monitoring guide.

Metadata locks

An ALTER TABLE waiting on a long open read transaction blocks all later queries on that table, and the error is a metadata lock wait rather than row lock wait. In that case, lock_wait_timeout (default one year) governs, and SHOW PROCESSLIST shows "Waiting for table metadata lock". Find the old transaction and end it.

Step 4: tune the timeout, carefully

SET SESSION innodb_lock_wait_timeout = 10;   -- this connection only
SET GLOBAL  innodb_lock_wait_timeout = 30;   -- new connections

A lower value fails fast, which can help a web request time out sooner than the user gives up. A higher value hides the problem. It is not a cure.

By default, InnoDB rolls back only the statement that timed out, not the whole transaction (innodb_rollback_on_timeout is OFF). Your code should therefore roll back the entire transaction and retry it, or you may commit a half-finished set of changes.

अगले कदम

• Understand the sibling error in MySQL error 1213: deadlock found.
• Too many sessions piling up? Read MySQL too many connections.
• Track slow requests with an अपटाइम मॉनिटर and diagnose with the वेबसाइट हेल्थ चेक.
• Learn why requests stall in Fix 504 Gateway Timeout.

सामान्य प्रश्न

What is the default lock wait timeout?

innodb_lock_wait_timeout defaults to 50 seconds. It applies to InnoDB row locks only.

Is error 1205 the same as a deadlock?

No. A deadlock is a cycle that MySQL detects and breaks at once. A lock wait timeout means one transaction held a lock too long.

Does the timeout roll back my whole transaction?

Not by default. Only the failed statement is rolled back, so your application must issue its own rollback.

Can I find the query that holds the lock?

Often only the last statement is visible. Idle blockers show no query, so inspect the application code for that thread's earlier statements, or enable the general log briefly.

यह पेज सामान्य जानकारी है, जिसे हमारी संपादकीय टीम ने लिखा है और दस्तावेज़ीकरण बदलने पर अपडेट किया जाता है। सर्वर और प्रदाता अलग-अलग होते हैं, इसलिए बदलाव पहले सुरक्षित तरीके से जाँचें। देखें: संपादकीय नीति और अस्वीकरण.