The Error Scenario
You fire off an UPDATE, INSERT, or DELETE inside a transaction, wait roughly 50 seconds, then get:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
InnoDB locks rows, not tables. When a transaction modifies a row, it holds an exclusive lock on that row until it commits or rolls back — nothing else can touch that row in the meantime. Your query timed out because another transaction locked the same rows first and held on past the deadline. That deadline is innodb_lock_wait_timeout, which defaults to 50 seconds.
Find What Is Blocking You
Don't retry blind. Figure out who's holding the lock first.
Show lock waits and the blocking query
SELECT
r.trx_id AS waiting_trx_id,
r.trx_mysql_thread_id AS waiting_thread,
r.trx_query AS waiting_query,
b.trx_id AS blocking_trx_id,
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
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_trx_id;
Also check all running processes
SHOW FULL PROCESSLIST;
Look for connections in Sleep state with a large Time value — say, 200+ seconds. Those are open transactions sitting idle while holding locks that nothing else can acquire.
Quick Fix: Kill the Blocking Transaction
Grab the blocking_thread value from the query above, then kill it:
KILL 42; -- replace 42 with your actual blocking thread ID
Retry your original transaction right away. With the blocking thread gone, the locks are released and your query can proceed. That said, killing threads is a band-aid — read the sections below to understand why this keeps recurring.
Root Causes
- Long-running open transactions — A transaction starts, does slow work (or just sits there forgotten), and never commits. Locks stay open the entire time.
- No index on the WHERE column — Without an index, InnoDB locks far more rows than you intended. On a 5-million-row table, that can mean 5 million row locks instead of just the handful you actually need to update.
- Application code doing I/O inside a transaction — An HTTP request, file read, or even a 500 ms sleep between SQL statements holds locks open far longer than necessary.
- Circular lock ordering — Transaction A waits for a lock held by Transaction B, while B waits for a lock held by A. Neither can proceed. This is classic deadlock territory, and it can surface as ERROR 1205 too.
Permanent Fix 1: Keep Transactions Short
The rule is simple: open, write, commit — as fast as possible. Push every slow operation outside the transaction block.
-- BAD: slow work inside the transaction
START TRANSACTION;
-- ... external API call takes 3 seconds ...
UPDATE orders SET status = 'processed' WHERE id = 123;
COMMIT;
-- GOOD: prepare everything first, then open a fast transaction
-- (do the API call here, before any transaction)
START TRANSACTION;
UPDATE orders SET status = 'processed' WHERE id = 123;
COMMIT;
Permanent Fix 2: Add Indexes on Filtered Columns
A missing index forces a full table scan. Instead of locking 3 rows, InnoDB ends up locking thousands — sometimes the whole table. Check what indexes currently exist:
SHOW INDEX FROM orders;
If the column in your WHERE clause isn't indexed, add one:
ALTER TABLE orders ADD INDEX idx_status (status);
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
With the index in place, that same UPDATE locks only the matching rows instead of scanning every row in the table.
Permanent Fix 3: Tune the Lock Wait Timeout
Lowering the timeout doesn't eliminate lock contention, but it makes failing transactions fail faster. Resources get freed in 10 seconds instead of piling up for 50.
Current session only
SET innodb_lock_wait_timeout = 10;
Globally (until restart)
SET GLOBAL innodb_lock_wait_timeout = 10;
Persistent (add to my.cnf / my.ini)
[mysqld]
innodb_lock_wait_timeout = 10
Then restart MySQL:
sudo systemctl restart mysql
Permanent Fix 4: Use Consistent Lock Ordering
Any time multiple transactions touch the same rows, they must acquire locks in the same order. Mixed order creates circular waits. Here's the safe pattern:
-- Both Transaction A and B must lock row 1 BEFORE row 2
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- lock row 1 first
SELECT * FROM accounts WHERE id = 2 FOR UPDATE; -- then row 2
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
Say Transaction A locks row 1 then tries for row 2, while Transaction B locks row 2 and tries for row 1. Neither moves forward. Enforcing a consistent order breaks that cycle entirely.
Permanent Fix 5: Enable Deadlock Logging
Flip this on once and leave it running. MySQL writes every detected deadlock to the error log, which makes diagnosing future lock problems much less of a guessing game:
SET GLOBAL innodb_print_all_deadlocks = ON;
To inspect the most recent deadlock right now:
SHOW ENGINE INNODB STATUS\G
Scroll to the LATEST DETECTED DEADLOCK section. It shows the exact queries, tables, and lock types involved — enough to trace back to the problem code.
Verification Steps
1. Confirm no long-running open transactions
SELECT trx_id, trx_started, trx_query,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS seconds_running
FROM information_schema.innodb_trx
ORDER BY seconds_running DESC;
Healthy output: no rows, or all transactions under a few seconds.
2. Confirm no active lock waits
SELECT COUNT(*) FROM information_schema.innodb_lock_waits;
Should return 0.
3. Retry the failing query
Run the transaction that was erroring. It should complete cleanly without ERROR 1205.
4. Verify the timeout value is set correctly
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
Quick Reference
- Identify blocker: query
information_schema.innodb_lock_waitsjoined withinnodb_trx - Kill blocker:
KILL <thread_id> - Short transactions: commit fast, zero slow I/O inside transaction blocks
- Add indexes: prevent unnecessary row-range or full-table locks
- Consistent lock order: prevents circular waits between concurrent transactions
- Log deadlocks:
SET GLOBAL innodb_print_all_deadlocks = ON