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.

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.

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.

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.