Fix MySQL Lock Wait Timeout Exceeded: Root Causes and Minimal Checks

Quick Answer

• Primary cause: A transaction holds a row-level lock that another transaction needs, and the waiting transaction exceeds innodb_lock_wait_timeout (default 50 seconds).
• First checks: Run SHOW PROCESSLIST to find long-running transactions, then query information_schema.innodb_trx and performance_schema.data_lock_waits to identify the blocking transaction.
• Minimal fix: Optimize indexes on WHERE clauses to reduce lock range, move non-database operations outside transactions, and implement application-level retry logic with exponential backoff.
• Environment boundary: Applies to MySQL/InnoDB with row-level locking; critical for high-concurrency OLTP systems (e-commerce, finance, social platforms) using Node.js, Python, or Java.

What Problem It Solves

The MySQL Lock wait timeout exceeded error (ERROR 1205) occurs when a transaction cannot acquire a row-level lock within the configured timeout period. This typically happens in high-concurrency environments where multiple transactions compete for the same rows. The problem manifests as failed API requests, stalled background jobs, and degraded application throughput. The root cause is almost always one of: missing indexes causing excessive lock ranges, long-running transactions holding locks too long, or deadlocks that are not properly handled.

Root Cause Analysis

The error originates from InnoDB's row-level locking mechanism. When Transaction A holds a lock on a row, Transaction B must wait for that lock to be released. If Transaction B waits longer than innodb_lock_wait_timeout seconds, MySQL kills Transaction B and returns ERROR 1205.

The most common scenarios that trigger this:

1. Missing or poor indexes: A WHERE clause without an index causes InnoDB to scan and lock many rows (table-level lock behavior), even if only one row is updated.
2. Long transactions: Transactions that include slow queries, API calls, or user input processing hold locks for extended periods.
3. Non-sequential lock acquisition: Different transactions acquire locks in different orders, leading to deadlocks and cascading timeouts.
4. High concurrency without retry logic: Applications that do not retry failed transactions leave users with errors.

Minimal Working Configuration

Start with these diagnostic queries to identify the blocking transaction:

SQL
-- Find all current transactions
SELECT * FROM information_schema.innodb_trx\G

-- Find lock waits (MySQL 5.7+)
SELECT * FROM performance_schema.data_lock_waits\G

-- Find blocking queries (MySQL 8.0+)
SELECT
  r.trx_id AS waiting_trx_id,
  r.trx_mysql_thread_id AS waiting_thread,
  r.trx_query AS waiting_query,
  b.trx_id AS blocking_trx_id,
  b.trx_mysql_thread_id AS blocking_thread,
  b.trx_query AS blocking_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx b
  ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
JOIN information_schema.innodb_trx r
  ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID;

To kill a blocking transaction (use with caution in production):

SQL
-- Find the thread ID from the query above, then:
KILL <blocking_thread_id>;

Parameters and Environment Variables

Set these dynamically:

SQL
-- Session-level (current connection only)
SET SESSION innodb_lock_wait_timeout = 10;

-- Global-level (requires SYSTEM_VARIABLES_ADMIN or SUPER privilege)
SET GLOBAL innodb_lock_wait_timeout = 20;
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 3;

Common Errors and Fixes

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

Immediate fix: Identify and kill the blocking transaction using the diagnostic queries above.

Long-term fixes:

1. Add indexes to WHERE clauses in UPDATE and DELETE statements
2. Break large batch operations into smaller chunks (e.g., 100-500 rows per transaction)
3. Move non-database operations (API calls, file I/O) outside the transaction
4. Implement retry logic in the application

Node.js retry example:

JAVASCRIPT
async function executeWithRetry(query, params, maxRetries = 3) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      const [result] = await pool.execute(query, params);
      return result;
    } catch (error) {
      if (error.errno === 1205 && attempt < maxRetries) {
        const delay = Math.min(100 * Math.pow(2, attempt - 1), 2000);
        await new Promise(resolve => setTimeout(resolve, delay));
        continue;
      }
      throw error;
    }
  }
}

Python retry example:

PYTHON
import time
from mysql.connector.errors import DatabaseError

def execute_with_retry(cursor, query, params, max_retries=3):
    for attempt in range(1, max_retries + 1):
        try:
            cursor.execute(query, params)
            return
        except DatabaseError as e:
            if e.errno == 1205 and attempt < max_retries:
                delay = min(0.1 * (2 ** (attempt - 1)), 2.0)
                time.sleep(delay)
                continue
            raise

ERROR 1213 (40001): Deadlock found when trying to restart transaction

Fix: Ensure all transactions acquire locks in the same order (e.g., by primary key ascending). Use SELECT ... FOR UPDATE to pre-lock all needed rows.

MySQL connection timeout: Lost connection to MySQL server during query

Fix: Configure connection pool with appropriate maxLifetime (e.g., 30 minutes) and implement heartbeat queries (SELECT 1).

Slow query log filling disk space

Fix: Disable log_slow_admin_statements and log_queries_not_using_indexes after diagnosis. Use log rotation.

Production Notes and Security Checks

1. Privileges: Dynamic global variable changes require SYSTEM_VARIABLES_ADMIN or SUPER privilege. Use a dedicated monitoring user with minimal permissions.
2. Deadlock detection overhead: At very high transaction rates (>1000 TPS), innodb_deadlock_detect adds CPU overhead. If disabled, ensure innodb_lock_wait_timeout is set appropriately and application retry logic is robust.
3. Replication lag: Batch processing with SLEEP() in stored procedures can cause replication lag. Use application-level batching instead.
4. Connection pooling: Use HikariCP (Java), PyMySQL connection pool (Python), or mysql2 pool (Node.js) with maxLifetime of 30 minutes and idleTimeout of 10 minutes.
5. Network security: Never expose MySQL port (3306) to the public internet. Use VPC, private subnets, or SSH tunnels.
6. Storage: Use high-performance SSDs with O_DIRECT enabled in MySQL config to avoid filesystem cache contention.

FAQ

Q: Why does my UPDATE statement lock many rows even though I only update one row?

A: This usually happens when the WHERE clause lacks an index, causing a full table scan (type: ALL in EXPLAIN). InnoDB locks all rows it scans during the search, even if only one row is updated. Fix: Create an index on the WHERE column (e.g., CREATE INDEX idx_status ON orders(status)) and verify with EXPLAIN that the type changes to ref or range.

Q: Should I increase or decrease innodb_lock_wait_timeout in high-concurrency scenarios?

A: It depends on your business tolerance. Increasing the timeout (e.g., 50 seconds) reduces timeout errors but can cause transaction pileup. Decreasing it (e.g., 10 seconds) fails fast and frees resources, but requires robust application retry logic. Recommended: 10-20 seconds for critical OLTP with retry logic; 30-50 seconds for background batch jobs.

Q: What are the risks of disabling innodb_deadlock_detect?

A: Without deadlock detection, MySQL relies on innodb_lock_wait_timeout to release locks, which can leave deadlocks unresolved for the full timeout duration. This causes transaction accumulation and throughput degradation. Only disable at very high TPS (>1000) with reliable application retry logic and a reasonable timeout setting.