MySQL ERROR 1205: Lock wait timeout exceeded; try restarting transaction

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction appears when one transaction holds an InnoDB row lock so long that another transaction exhausts innodb_lock_wait_timeout (default 50 seconds). Unlike a deadlock, which InnoDB resolves automatically, a timeout means a blocker is still active. The root cause is almost always a long-running transaction or hot row contention. Find it before it cascades into a wider outage.

This error applies only to InnoDB row-level locks. Metadata lock waits from DDL operations show Waiting for table metadata lock in SHOW PROCESSLIST and are governed by lock_wait_timeout, not innodb_lock_wait_timeout. Table-level locks are also separate. If you see ERROR 1205, you are dealing with row-level contention inside InnoDB.

What this means

When transaction A modifies a row, it acquires an exclusive lock. If transaction B tries to modify the same row before A commits or rolls back, B waits. If B waits longer than innodb_lock_wait_timeout (default 50 seconds), MySQL aborts B’s current statement and returns ERROR 1205.

By default, innodb_rollback_on_timeout is OFF. Only the single statement that hit the timeout is rolled back. The rest of the transaction remains active, its prior statements remain intact, and the application can issue COMMIT to persist them. This is a dangerous trap. An application that catches ERROR 1205 and issues COMMIT will persist partially applied work unless it explicitly issues ROLLBACK. If you want a timeout to abort the entire transaction, you must set innodb_rollback_on_timeout=ON at server startup. It is not dynamic.

Deadlocks are different. InnoDB detects circular dependencies immediately and rolls back one transaction. innodb_lock_wait_timeout does not apply to deadlocks. If ERROR 1205 spikes but Innodb_row_lock_current_waits is zero, verify you are not seeing deadlocks.

flowchart TD
    A[Transaction acquires row lock] --> B[Waiting transaction queues]
    B --> C[Wait exceeds innodb_lock_wait_timeout]
    C --> D[MySQL returns ERROR 1205]
    D --> E{innodb_rollback_on_timeout}
    E -->|OFF| F[Only statement rolls back]
    E -->|ON| G[Entire transaction rolls back]

Common causes

Quick checks

These commands are read-only and safe to run during an incident.

-- Check InnoDB row lock wait counters
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';

-- Find the oldest active transactions
SELECT trx_id, trx_mysql_thread_id, trx_started, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC LIMIT 5;

-- Identify blocking and waiting transactions (MySQL 8.0+)
SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id;

-- View lock wait chains via sys schema (MySQL 8.0+)
SELECT * FROM sys.innodb_lock_waits;

-- Distinguish timeouts from deadlocks (cumulative counter)
SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_deadlocks';

-- Verify rollback behavior
SELECT @@innodb_rollback_on_timeout;

-- Check current timeout
SELECT @@innodb_lock_wait_timeout;

-- Check processlist for metadata locks vs row locks
SHOW PROCESSLIST;

How to diagnose it

1. Confirm it is a row lock timeout, not a metadata lock or deadlock. If SHOW PROCESSLIST shows Waiting for table metadata lock, you are dealing with a metadata lock cascade, not ERROR 1205. If SHOW ENGINE INNODB STATUS shows a LATEST DETECTED DEADLOCK section with a recent timestamp, InnoDB already resolved a deadlock. ERROR 1205 is a timeout, not an instant deadlock.
2. Quantify the scope. Check Innodb_row_lock_current_waits. Zero means the incident may have passed. Sustained elevation means active contention.
3. Find the blocking transaction. On MySQL 8.0+, query performance_schema.data_lock_waits joined with information_schema.INNODB_TRX to see exactly which transaction is blocking which. On MySQL 5.7, use information_schema.INNODB_LOCK_WAITS joined with INNODB_TRX and INNODB_LOCKS instead. On any version, SHOW ENGINE INNODB STATUS includes transaction and lock information in the TRANSACTIONS section, though it truncates heavily under high concurrency.
4. Determine why the blocker is holding the lock. If trx_query is not NULL, the blocker is actively executing a long query. If trx_query is NULL, the blocker is idle between statements with an open transaction. This is one of the most common causes: a BEGIN with no matching COMMIT.
5. Check for index and isolation issues. Run EXPLAIN on the blocking query. If it is scanning a large range or missing an index, InnoDB may hold gap locks or next-key locks that cover many rows, amplifying contention. REPEATABLE READ creates next-key locks on every scan; READ COMMITTED uses record locks only and can eliminate gap-lock contention if your application allows it.
6. Verify rollback behavior. Check whether innodb_rollback_on_timeout is enabled. If it is OFF, warn the application owner that catching ERROR 1205 and issuing COMMIT will persist partial data.

Metrics and signals to monitor

Fixes

Kill the blocking transaction

If the blocking transaction is expendable, terminate it to release the lock immediately.

KILL <trx_mysql_thread_id>;

Warning: This rolls back all uncommitted work in that transaction. If the blocker is a replication thread or a critical batch job, killing it may cause more harm than the original timeout. Identify the source and user before acting.

Reduce lock scope with indexing

If EXPLAIN shows a full scan or a wide range scan, add an index to reduce the number of rows InnoDB must lock. A missing index on a foreign key or join column can cause InnoDB to lock far more rows than the query actually needs. Even an apparently selective query can turn into a full scan under the wrong execution plan, turning a single-row update into a table-wide lock sweep.

Shorten transaction boundaries

If the blocker is an idle transaction with trx_query IS NULL, the fix is application-level. Ensure that every BEGIN has a prompt COMMIT or ROLLBACK. Connection pools should return connections in a clean state. ORM frameworks that auto-begin transactions must be configured to commit or roll back before returning a connection to the pool.

Adjust the timeout (temporary relief)

You can increase innodb_lock_wait_timeout for a specific session to let a large operation complete, but do not raise the global default to mask contention.

SET SESSION innodb_lock_wait_timeout = 120;

Tradeoff: Longer timeouts allow blocked transactions to pile up. If you have 100 transactions waiting 120 seconds instead of 50, your connection pool may exhaust before any useful work completes.

Enable full transaction rollback on timeout

If your application expects atomic transactions and should not commit after a partial failure, set innodb_rollback_on_timeout=ON in the server configuration and restart.

Tradeoff: This requires a restart and changes failure semantics. Applications that previously retried the failed statement inside the same transaction will need to restart the entire transaction instead.

Prevention

• Alert on open transaction age in information_schema.INNODB_TRX. In OLTP, any transaction older than 5 minutes is suspicious.
• Keep transactions short. Do not hold transactions open across user interactions, network calls, or long computations.
• Index foreign keys and join columns. This shrinks the lock range and prevents gap locks from expanding.
• For queue-like patterns in MySQL 8.0+, use SELECT ... FOR UPDATE SKIP LOCKED to skip locked rows, or NOWAIT to fail fast instead of queueing.
• Review application retry logic. After ERROR 1205, the application must issue ROLLBACK unless innodb_rollback_on_timeout=ON is set.

How Netdata helps

• Correlate Innodb_row_lock_waits with Threads_running: if lock waits spike while CPU stays flat, you have contention, not saturation.
• Alert when Innodb_row_lock_current_waits stays above zero while Threads_connected rises. This catches cascades before pool exhaustion.
• Track the oldest transaction in INNODB_TRX. Idle blockers often appear here before history list length grows.
• Cross-reference lock wait spikes with the slow query rate. A new slow query often triggers the cascade.
• Watch for a drop in Questions rate during a cascade. Falling throughput with high Threads_connected confirms user-facing impact.

Related guides

• How MySQL actually works in production: a mental model for operators
• MySQL Aborted_connects and Aborted_clients climbing: diagnosis
• MySQL connection exhaustion: detection, diagnosis, and prevention
• MySQL: got a packet bigger than ‘max_allowed_packet’ bytes — causes and fixes
• MySQL monitoring checklist: the signals every production instance needs
• MySQL monitoring maturity model: from survival to expert
• MySQL Threads_created climbing: thread cache churn and missing pooling
• MySQL Threads_connected vs Threads_running: which one to actually alert on
• MySQL ERROR 1040 (HY000): Too many connections - causes and fixes