Knowledge

#Databases

1. About the error
2. Why do I see this error
3. Solution
4. Find the blocking transaction
5. Keep transactions short
6. Raise the timeout (last resort)

About the error

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded;
try restarting transaction

InnoDB uses row-level locks. When one transaction holds a lock on a row and another wants it, the second waits, up to innodb_lock_wait_timeout (default 50 seconds). If the lock isn't released in time, the waiting transaction is rolled back with error 1205.

Why do I see this error

• A long-running transaction holding locks while it does slow work (or waits on something external).
• A transaction that was never committed or rolled back, left open by a crashed process or an interactive session.
• High contention: many requests updating the same rows at once.
• Deadlock-adjacent patterns where transactions acquire locks in different orders.

Solution

Find the blocking transaction

See which transactions are running and how long they've been open:

SELECT * FROM information_schema.innodb_trx\G

On MySQL 8 you can see exactly who blocks whom:

SELECT * FROM sys.innodb_lock_waits\G

If you find a transaction stuck open, kill it by its thread id:

KILL <trx_mysql_thread_id>;

Keep transactions short

The durable fix is to hold locks for as little time as possible. Don't do slow work inside a transaction, no external API calls, no waiting on user input, no heavy computation between BEGIN and COMMIT. In Laravel:

// Do the slow part first, outside the transaction
$data = $api->fetch();

DB::transaction(function () use ($data) {
    // only fast writes in here
    Order::create($data);
});

Raise the timeout (last resort)

If a particular job legitimately needs longer, raise the wait timeout rather than leaving it default:

[mysqld]
innodb_lock_wait_timeout = 120

But treat this as a stopgap, a growing lock-wait time usually points at transactions that are too long or contention that needs a schema or indexing fix. Related: MySQL 1040 too many connections when stuck transactions also exhaust connections.