How do I fix "Lock wait timeout exceeded; try restarting transaction" in MySQL?

Or use the ready-made view: SELECT * FROM sys.innodb_lock_waits\G.

If blocking_query is NULL, the blocker is idle inside an open transaction: it ran its statement and is now waiting on the application. List long-running transactions:

SELECT trx_mysql_thread_id, trx_started, trx_rows_locked, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

Then use SHOW PROCESSLIST to find the host and user, and KILL <thread_id> if you need to clear it now.

Missing indexes lock far more than you think

This is the cause people overlook most. InnoDB locks the index records it scans, not only the rows it changes. Without a usable index, this statement:

UPDATE orders SET status = 'shipped' WHERE tracking_code = 'ZX81';

scans (and locks) every row in the clustered index under REPEATABLE READ. Every other writer to orders then waits. Add the index and the same statement locks one row:

CREATE INDEX idx_orders_tracking ON orders (tracking_code);

Check with EXPLAIN: type: ALL on an UPDATE or DELETE is a warning sign.

Fixes

1. Keep transactions short. Do no network I/O, no user think-time and no sleeping while a transaction is open. Commit as soon as the writes are done.

2. Index the WHERE clause of every UPDATE and DELETE. Fewer rows scanned means fewer rows locked.

3. Batch big writes.

   -- repeat until 0 rows affected
   DELETE FROM events WHERE created_at < '2026-01-01' ORDER BY id LIMIT 10000;
   

4. Consider READ COMMITTED. It drops most gap locking, which cuts contention for many OLTP workloads. Check that your code doesn't rely on repeatable-read semantics first.

5. Retry deliberately. A 1205 on a hot row may be legitimate contention. Retry the whole transaction with backoff a limited number of times.

6. Use SKIP LOCKED or NOWAIT for queue-style tables so workers don't pile up on the same row:

   SELECT id FROM jobs WHERE status = 'ready'
   ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
   

Raising innodb_lock_wait_timeout only makes users wait longer for the same failure. Lowering it for interactive sessions (SET SESSION innodb_lock_wait_timeout = 5) often makes failures quicker and easier to see.

Checklist

1. Find the blocker with sys.innodb_lock_waits. A NULL query means an idle open transaction.
2. Check for a missing index on the blocked or blocking UPDATE/DELETE.
3. Shorten transactions and batch bulk writes.
4. Retry the whole transaction on purpose; don't just raise the timeout.

InnoDB and the clustered primary key: why your PK choice matters

In MySQL's InnoDB, the table is the primary key index. That single fact shapes insert speed, index size and how you should pick primary keys.

#indexing#internals#schema-design