Fix: MySQL ERROR 1205: Lock wait timeout exceeded

ERROR 1205: Lock wait timeout exceeded

The first time I hit this, I was sure the database was broken: the exact same query ran instantly in a quiet console and then timed out under load. ERROR 1205 is not a broken query, it is a queue. Your transaction waited 50 seconds for a row lock that another transaction would not release, and InnoDB gave up on it. I learned to stop tuning the timeout and start asking who holds the lock and why they hold it so long, because in my experience the timeout is the symptom, never the cause.

Your application throws:

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

Or from an ORM:

django.db.utils.OperationalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')

java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded

A transaction tried to acquire a row lock but another transaction held that lock for longer than the configured timeout (default: 50 seconds). MySQL gave up waiting and aborted the blocked transaction.

What InnoDB Is Actually Waiting For

InnoDB uses row-level locking for UPDATE, DELETE, and SELECT ... FOR UPDATE statements. When Transaction A locks a row, Transaction B must wait if it needs the same lock. If Transaction A holds the lock longer than innodb_lock_wait_timeout, Transaction B is aborted with ERROR 1205. This is different from a deadlock, a deadlock is a cycle that InnoDB detects and breaks immediately, while a lock wait timeout is one transaction simply waiting too long for another to release.

Locks in InnoDB are richer than “this row is locked.” InnoDB takes shared (S) locks for reads under SELECT ... LOCK IN SHARE MODE, exclusive (X) locks for writes, gap locks to prevent phantom inserts in REPEATABLE READ, and next-key locks (a row lock plus the gap before it). A statement that you intuitively expect to lock one row may actually lock a range. Index choice determines what gets locked: a query that scans a secondary index also locks the corresponding primary-key rows it visits. Without the right index, UPDATE ... WHERE column = 'x' can escalate to a full-table lock because InnoDB locks every row it scans, not just the matches.

The timeout itself is configurable via innodb_lock_wait_timeout (default 50 seconds). On healthy OLTP workloads, lock waits should be measured in milliseconds, a 50-second wait is already a serious incident, and the timeout exists primarily to prevent indefinite blocking, not as a normal wait budget. If you regularly see ERROR 1205, you have a contention problem that no amount of timeout tuning will fix.

Common causes:

• Long-running transactions. A transaction holds locks for too long (slow queries, application delays, or uncommitted transactions).
• Missing indexes. Without an index, UPDATE and DELETE lock more rows than necessary (potentially the entire table).
• Uncommitted transactions. A session started a transaction but never committed or rolled back.
• Deadlock-adjacent contention. Two transactions compete for overlapping rows but do not form a full deadlock cycle.
• Bulk updates. Large UPDATE or DELETE statements lock many rows simultaneously.
• Application bugs. Connections are leaked without closing transactions.
• Hot row contention. A counter row (sequence, inventory total, balance) is updated by every transaction, serializing all writes.

In Production: Incident Lens

Lock wait timeouts are a load-driven incident. The system runs cleanly under normal traffic and falls over the moment concurrency rises, a marketing campaign launches, a webhook batch arrives, a cron job overlaps with peak hours. The signature is a sudden cluster of ERROR 1205 events all pointing at the same table, often the same handful of rows.

How it surfaces: application logs fill with Lock wait timeout exceeded; try restarting transaction. Sentry or your APM shows an exception spike on the write path, checkout submission, order creation, inventory deduction. Database latency rises but CPU and IOPS look normal, because the workers are blocked waiting on locks, not doing work. P99 transaction duration shoots up while throughput drops. If your application retries blindly, you see write amplification: each timed-out transaction comes back and joins the queue, making the contention worse.

Blast radius: scoped to the table (sometimes the row) under contention, but the user-visible impact depends on what that table does. A locked inventory table during a flash sale stops all checkouts. A locked users table during a permission update blocks login. If the locked rows are foreign-key targets, child-table inserts also wait. In the worst case, every connection in the pool ends up parked on the same lock and the application stops accepting requests because there are no free connections, a full outage triggered by a single slow transaction.

Monitoring signal: the layered alerts are innodb_row_lock_waits (counter, alert on rate of change), innodb_row_lock_time_avg (average ms per wait, alert above 100 ms), and longest active transaction duration from information_schema.innodb_trx (alert above 30 seconds). For deeper diagnosis, enable performance_schema.events_transactions_history_long and ship the data to your APM. The leading indicator is transaction duration p99, not lock waits themselves, a transaction that holds locks for 5 seconds is a problem even before anyone times out behind it.

Recovery sequence: find the blocking transaction and decide whether to kill it. Run the lock-wait query in Fix 1 (sys.innodb_lock_waits on 8.0+), the blocking_pid / blocking_thread column points at the connection holding the locks. If the transaction is idle (trx_query = NULL and trx_started is minutes old), it is almost certainly a leaked transaction from a crashed application worker; KILL <thread_id> releases the locks immediately. If the transaction is active and legitimate (a long backfill, a migration), the question is whether you can wait for it to finish, usually you cannot, and you kill it anyway. After the immediate recovery, throttle whatever pushed contention over the edge: pause the worker pool, lower a feature flag, scale down the consumers of the hot row.

Postmortem preventive: the durable fix is short transactions and correct indexes. Every transaction should do exactly one write-and-commit; no external API calls, no slow application logic, no waiting for user input. Audit UPDATE and DELETE statements with EXPLAIN and confirm type is not ALL (full scan) on any production table, add a missing index if it is. For hot-row contention, redesign: replace a single counter with a sharded counter (N rows, sum on read), or push the update through a queue that serializes naturally. Set innodb_lock_wait_timeout to a low value (10-20 seconds) so leaked transactions self-clear before they wedge the application. Add an APM alarm on transaction duration p99 above 1 second, and review every alert, sustained slow transactions are a leading indicator of the next ERROR 1205 outage.

Fix 1: Find and Kill the Blocking Transaction

Identify what is holding the lock. On MySQL 8.0+, the sys schema view is the fastest path. blocking_pid is the exact processlist id you pass to KILL, and it even hands you a ready-made kill statement:

-- MySQL 8.0+ (sys schema)
SELECT waiting_pid, waiting_query, blocking_pid, blocking_query,
       sql_kill_blocking_connection
FROM sys.innodb_lock_waits\G

MySQL 5.7 and earlier. The INNODB_LOCKS and INNODB_LOCK_WAITS tables this query relies on were removed in MySQL 8.0.1, so it runs only on 5.7 and older. Here blocking_thread (trx_mysql_thread_id) is the processlist id for KILL:

SELECT
    r.trx_id AS waiting_trx,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query,
    b.trx_started AS blocking_started
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

MySQL 8.0+ raw performance_schema. This is what the sys view reads under the hood. One gotcha: THREAD_ID here is the Performance Schema thread id, not the processlist id, so join performance_schema.threads to recover the PROCESSLIST_ID before you KILL:

SELECT
    waiting.THREAD_ID AS waiting_thread,
    pt.PROCESSLIST_ID AS waiting_pid,
    blocking.THREAD_ID AS blocking_thread,
    pb.PROCESSLIST_ID AS blocking_pid
FROM performance_schema.data_lock_waits dlw
JOIN performance_schema.data_locks waiting ON dlw.REQUESTING_ENGINE_LOCK_ID = waiting.ENGINE_LOCK_ID
JOIN performance_schema.data_locks blocking ON dlw.BLOCKING_ENGINE_LOCK_ID = blocking.ENGINE_LOCK_ID
JOIN performance_schema.threads pt ON pt.THREAD_ID = waiting.THREAD_ID
JOIN performance_schema.threads pb ON pb.THREAD_ID = blocking.THREAD_ID;

Kill the blocking transaction if it is stuck:

-- Use the blocking_pid (processlist id) from the query above
KILL <blocking_pid>;

Check for idle transactions holding locks:

SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
WHERE trx_state = 'RUNNING'
ORDER BY trx_started;

Transactions with trx_query = NULL and old trx_started times are idle sessions holding locks.

The signature I look for first is a transaction that started minutes ago with trx_query = NULL. That means the application opened a transaction, ran a few queries, and then never committed, so the connection is most likely leaked. When I see it, the bug is almost always in the connection pool or transaction management, not in the query that timed out behind it.

Fix 2: Increase the Lock Wait Timeout

If the blocking transactions are legitimate and just need more time:

-- Check current value
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
-- Default: 50 (seconds)

-- Increase for current session
SET innodb_lock_wait_timeout = 120;

-- Increase globally
SET GLOBAL innodb_lock_wait_timeout = 120;

In my.cnf:

[mysqld]
innodb_lock_wait_timeout = 120

Per-transaction override:

SET SESSION innodb_lock_wait_timeout = 300;
START TRANSACTION;
-- Long operation here
COMMIT;
SET SESSION innodb_lock_wait_timeout = 50;  -- Reset

Note: Increasing the timeout only delays the error. Fix the root cause (long-running transactions) instead.

Fix 3: Add Missing Indexes

Without proper indexes, UPDATE and DELETE lock more rows than intended:

Broken, no index on the WHERE column:

-- Locks ALL rows in the table (full table scan)
UPDATE orders SET status = 'shipped' WHERE customer_email = 'alice@example.com';

Fixed, add an index:

CREATE INDEX idx_orders_customer_email ON orders(customer_email);

-- Now only locks the matching rows
UPDATE orders SET status = 'shipped' WHERE customer_email = 'alice@example.com';

Check which indexes are used:

EXPLAIN UPDATE orders SET status = 'shipped' WHERE customer_email = 'alice@example.com';

If the type column shows ALL, it is a full table scan and InnoDB may lock every row.

The mistake I see most is an UPDATE or DELETE whose WHERE clause hits an unindexed column on a busy table. With no index to narrow the scan, InnoDB locks every row it touches, which on a full scan is effectively the whole table. Make sure the columns in your WHERE clause are indexed before you run anything that writes under load.

Fix 4: Shorten Transaction Duration

Keep transactions as brief as possible:

Broken, long transaction with external call:

cursor.execute("BEGIN")
cursor.execute("SELECT * FROM inventory WHERE id = 1 FOR UPDATE")
# Long external API call while holding the lock
result = call_payment_api(amount)
cursor.execute("UPDATE inventory SET quantity = quantity - 1 WHERE id = 1")
cursor.execute("COMMIT")

Fixed, do external work outside the transaction:

# Do slow work first
result = call_payment_api(amount)

# Quick transaction
cursor.execute("BEGIN")
cursor.execute("UPDATE inventory SET quantity = quantity - 1 WHERE id = 1")
cursor.execute("COMMIT")

Broken, autocommit off with forgotten commits:

conn.autocommit = False
cursor.execute("UPDATE orders SET status = 'processing' WHERE id = 1")
# ... more code, but commit is never called
# The lock is held until the connection closes or times out!

Fixed, always commit or rollback:

try:
    conn.autocommit = False
    cursor.execute("UPDATE orders SET status = 'processing' WHERE id = 1")
    conn.commit()
except Exception:
    conn.rollback()
    raise

Fix 5: Break Up Bulk Operations

Large bulk updates lock many rows simultaneously:

Broken, updating millions of rows in one transaction:

UPDATE users SET last_seen = NOW() WHERE active = 1;
-- Locks all active users for the duration

Fixed, process in batches:

-- Walk the primary key in fixed id ranges, 1000 ids per pass. Using a range
-- (not LIMIT/OFFSET) stays correct even when ids are non-contiguous.
SET @batch_size = 1000;
SET @last_id = 0;
SET @max_id = (SELECT MAX(id) FROM users);

WHILE @last_id < @max_id DO
    UPDATE users SET last_seen = NOW()
    WHERE active = 1 AND id > @last_id AND id <= @last_id + @batch_size;
    COMMIT;  -- release locks before the next range
    SET @last_id = @last_id + @batch_size;
END WHILE;

WHILE only runs inside a stored program, so this is a procedure body, wrap it with DELIMITER and CREATE PROCEDURE (or just paste it into one) to run it. For a one-off backfill, driving the same loop from application code is simpler and gives you per-batch error handling:

In application code (Python):

batch_size = 1000
last_id = 0

while True:
    # Keyset pagination on the primary key. MySQL's UPDATE has no OFFSET, so
    # select the next batch of ids first, then update exactly those rows.
    cursor.execute(
        "SELECT id FROM users WHERE active = 1 AND id > %s ORDER BY id LIMIT %s",
        (last_id, batch_size),
    )
    ids = [row[0] for row in cursor.fetchall()]
    if not ids:
        break

    placeholders = ",".join(["%s"] * len(ids))
    cursor.execute(
        f"UPDATE users SET last_seen = NOW() WHERE id IN ({placeholders})", ids
    )
    conn.commit()  # release locks between batches

    last_id = ids[-1]
    time.sleep(0.1)  # brief pause to let other transactions proceed

Fix 6: Use READ COMMITTED Isolation

The default REPEATABLE READ isolation level holds locks longer. READ COMMITTED releases locks for non-matching rows earlier:

-- Per session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Or set it globally in my.cnf:

[mysqld]
transaction-isolation = READ-COMMITTED

Caution: Changing isolation level affects consistency guarantees. Test thoroughly. READ COMMITTED is safe for most applications and is the default in PostgreSQL and Oracle.

Fix 7: Implement Application-Level Retry

Retry the transaction when the lock timeout occurs:

import time
from mysql.connector import Error

MAX_RETRIES = 3

def execute_with_retry(func):
    for attempt in range(MAX_RETRIES):
        try:
            return func()
        except Error as e:
            if e.errno == 1205 and attempt < MAX_RETRIES - 1:
                time.sleep(0.5 * (attempt + 1))
                continue
            raise

Laravel:

use Illuminate\Support\Facades\DB;

DB::transaction(function () {
    // Your queries here
}, 3);  // Retry up to 3 times on deadlock/lock timeout

Java with Spring:

// Spring's exception translator maps MySQL 1205 to CannotAcquireLockException
// and 1213 (deadlock) to DeadlockLoserDataAccessException — both extend
// PessimisticLockingFailureException, so retry on that to cover both.
// (Do NOT use JPA's LockTimeoutException here; a JDBC 1205 is not translated to it.)
import org.springframework.dao.PessimisticLockingFailureException;

@Retryable(value = PessimisticLockingFailureException.class,
           maxAttempts = 3, backoff = @Backoff(delay = 500))
@Transactional
public void processOrder(Long orderId) {
    // Your logic here
}

One subtlety that quietly breaks naive retry: by default (innodb_rollback_on_timeout = OFF), a 1205 rolls back only the statement that timed out, not the whole transaction. The rest of the transaction stays open and keeps holding its locks, so a retry that re-runs just the failed statement only deepens the contention. Either explicitly roll back the whole transaction before retrying, or set innodb_rollback_on_timeout = ON in my.cnf so MySQL aborts the entire transaction on a lock timeout and your retry starts from a clean slate. Note this variable is read-only at runtime, it must go in the config file and the server has to restart.

Fix 8: Monitor Lock Contention

Set up monitoring to catch lock issues early:

-- Check InnoDB status for lock information
SHOW ENGINE INNODB STATUS\G

-- Monitor lock waits over time
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE '%lock%'
ORDER BY SUM_TIMER_WAIT DESC;

-- Check for long-running transactions
SELECT trx_id, trx_started, NOW() - trx_started AS duration_seconds, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

Set up automatic killing of idle transactions with the event scheduler (kills any transaction running longer than 5 minutes):

CREATE EVENT kill_idle_transactions
ON SCHEDULE EVERY 1 MINUTE
DO
    BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE tid BIGINT;
        DECLARE cur CURSOR FOR
            SELECT trx_mysql_thread_id
            FROM information_schema.innodb_trx
            WHERE trx_state = 'RUNNING'
            AND trx_started < NOW() - INTERVAL 5 MINUTE;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

        OPEN cur;
        read_loop: LOOP
            FETCH cur INTO tid;
            IF done THEN LEAVE read_loop; END IF;
            -- KILL does not accept a local variable directly; build it dynamically.
            SET @kill_sql = CONCAT('KILL ', tid);
            PREPARE stmt FROM @kill_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END LOOP;
        CLOSE cur;
    END;

Two things this needs to actually run: the event scheduler has to be on (SET GLOBAL event_scheduler = ON;), and because the body is a compound statement, wrap it with DELIMITER $$ ... END$$ DELIMITER ; in the mysql client so the inner semicolons do not end the CREATE EVENT early. Be deliberate with this one, it kills every transaction older than 5 minutes, including legitimate migrations and backfills, so scope the WHERE clause if you run long jobs.

Lock-Timeout Cases That Hide From EXPLAIN

Check for gap locks. InnoDB uses gap locks in REPEATABLE READ to prevent phantom reads. These can lock ranges of rows you did not expect. Use READ COMMITTED to reduce gap locking.

Check for foreign key locks. Inserting or updating rows with foreign keys causes shared locks on the referenced parent rows.

Check your connection pool settings. If connections are returned to the pool without committing, open transactions persist and hold locks.

Check for orphaned XA transactions. Two-phase commit XA transactions can be left in PREPARED state if the coordinator crashes. They hold locks indefinitely. XA RECOVER lists them; XA ROLLBACK '<xid>' releases them.

Check for InnoDB history list length growth. A long-running read transaction in REPEATABLE READ keeps old row versions alive in the undo log. This does not cause ERROR 1205 directly, but it correlates with contention because purge cannot reclaim space. Monitor Innodb_history_list_length and investigate when it grows unbounded.

Check for auto-increment lock mode. With innodb_autoinc_lock_mode = 0 (traditional), large INSERT ... SELECT statements hold an AUTO-INC lock for the duration. Switch to 2 (interleaved) for binary log row format to allow concurrent inserts.

Check for deadlocks misreported as timeouts. A real deadlock returns ERROR 1213, not 1205. But under heavy contention, you can see both. Enable innodb_print_all_deadlocks = ON and inspect the error log to confirm whether the symptom is actually a deadlock pattern.

Check for explicit lock waits in your ORM. Frameworks like Django and Rails often issue SELECT ... FOR UPDATE implicitly when using select_for_update() or lock!. Audit your ORM call sites to know exactly where exclusive locks are taken.

For MySQL access issues, see Fix: MySQL ERROR 1045: Access denied for user. For MySQL syntax errors, see Fix: MySQL ERROR 1064: syntax error. For PostgreSQL deadlock issues, see Fix: PostgreSQL ERROR: deadlock detected. For MySQL-side deadlocks specifically, see Fix: MySQL deadlock detected.