Lock wait timeout exceeded; try restarting transaction means your query waited too long for a row lock held by another transaction.

Why this happens

InnoDB uses row-level locking for transactional integrity. When transaction A holds a lock on rows that transaction B needs, B waits up to innodb_lock_wait_timeout seconds (default 50). If A doesn’t commit or rollback in time, B fails with this error. This is different from a deadlock — it’s a one-way wait.

What causes this error

1. A long-running transaction is holding locks
2. A query is updating many rows without an index (table lock)
3. An uncommitted transaction from a crashed connection

Fix 1: Find the blocking transaction

-- Show which transactions are blocking
SELECT * FROM information_schema.innodb_lock_waits;

-- Show all running transactions
SHOW ENGINE INNODB STATUS;

-- Kill the blocking transaction
KILL <process_id>;

Fix 2: Increase the timeout (temporary)

SET innodb_lock_wait_timeout = 120; -- default is 50 seconds

Fix 3: Add missing indexes

If your UPDATE or DELETE doesn’t use an index, MySQL locks the entire table:

-- ❌ No index on email — locks all rows
UPDATE users SET name = 'John' WHERE email = 'john@example.com';

-- ✅ Add an index
CREATE INDEX idx_users_email ON users(email);

Fix 4: Keep transactions short

Commit or rollback as soon as possible. Don’t hold transactions open while waiting for user input or external API calls.

Alternative solutions

Use SELECT ... FOR UPDATE NOWAIT (MySQL 8.0+) to fail immediately instead of waiting:

SELECT * FROM users WHERE id = 1 FOR UPDATE NOWAIT;

Break large updates into batches to reduce lock duration and release locks sooner.

Prevention

• Always ensure WHERE clauses in UPDATE/DELETE statements use indexed columns.
• Set innodb_lock_wait_timeout per-session for known long operations rather than globally.

Related: PostgreSQL: Connection Refused fix · MongoDB: Duplicate Key fix