Fix: MySQL ERROR 1205: Lock wait timeout exceeded

The Error

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.

Why This Happens

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 information_schema.innodb_lock_waits query in Fix 1 — the 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:

-- Show current InnoDB lock waits (MySQL 8.0+)
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+ with performance_schema:

SELECT
    waiting.THREAD_ID AS waiting_thread,
    waiting.EVENT_NAME AS waiting_event,
    blocking.THREAD_ID AS blocking_thread,
    blocking.EVENT_NAME AS blocking_event
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;

Kill the blocking transaction if it is stuck:

-- Find the thread ID from the query above
KILL <blocking_thread_id>;

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.

Pro Tip: A common pattern is a transaction that started minutes ago with trx_query = NULL. This means the application opened a transaction, executed some queries, but never committed. The connection might be leaked. Check your application’s connection pool and transaction management.

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 = '[email protected]';

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 = '[email protected]';

Check which indexes are used:

EXPLAIN UPDATE orders SET status = 'shipped' WHERE customer_email = '[email protected]';

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

Common Mistake: Running UPDATE or DELETE with a WHERE clause on an unindexed column in a busy table. Without an index, InnoDB locks the entire table to ensure consistency. Always ensure your WHERE columns are indexed.

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:

-- Update 1000 rows at a time
SET @batch_size = 1000;
SET @last_id = 0;

REPEAT
    UPDATE users SET last_seen = NOW()
    WHERE active = 1 AND id > @last_id
    ORDER BY id LIMIT @batch_size;

    SET @last_id = @last_id + @batch_size;
UNTIL ROW_COUNT() = 0 END REPEAT;

In application code (Python):

batch_size = 1000
offset = 0

while True:
    cursor.execute("""
        UPDATE users SET last_seen = NOW()
        WHERE active = 1
        ORDER BY id
        LIMIT %s OFFSET %s
    """, (batch_size, offset))

    conn.commit()  # Release locks between batches

    if cursor.rowcount == 0:
        break
    offset += batch_size
    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 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:

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

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:

-- Kill transactions idle for more than 5 minutes
SET GLOBAL innodb_rollback_on_timeout = ON;

-- Or use event scheduler
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 tid;
        END LOOP;
        CLOSE cur;
    END;

Still Not Working?

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.