1205ERRORTier 2 — Caution✅ HIGH confidence

Lock wait timeout exceeded; try restarting transaction

Category: Locking & ConcurrencyVersions: All MariaDB / MySQL versions

🔴 Production Risk Error

Persistent 1205 errors in production indicate a locking bottleneck. Use SHOW ENGINE INNODB STATUS to identify the blocking transaction and its trx_id, then investigate which connection is holding the lock.

What this means

Error 1205 (SQLSTATE HY000) is raised when a transaction waits longer than innodb_lock_wait_timeout seconds to acquire a row-level or table-level lock. The waiting transaction is rolled back (statement-level rollback by default) to resolve the impasse. This is distinct from error 1213 (deadlock) where a cycle of locks is detected; 1205 is a simple timeout.

Why it happens

1A long-running transaction holds a row lock and another transaction is waiting to modify the same row
2An uncommitted transaction is holding locks on many rows, blocking other transactions
3A batch import or update is holding locks for longer than innodb_lock_wait_timeout
4Application code opened a transaction and then made an external API call before committing, holding locks during the round-trip

How to reproduce

Transaction A holds a row lock; Transaction B waits past the timeout.

trigger — this will ERROR

-- Connection A:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- (does not COMMIT)

-- Connection B (after innodb_lock_wait_timeout seconds):
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- ERROR 1205: Lock wait timeout exceeded

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Fix 1: Keep transactions short — commit as soon as possible

As a general best practice for all write transactions.

fix

-- Bad: hold lock during external call
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- ... external API call (seconds) ...
COMMIT;

-- Good: commit before the slow operation
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- ... external API call ...

Why this works

Row locks are held for the duration of the transaction. Minimising the time between acquiring a lock and committing reduces the window during which other transactions are blocked.

Fix 2: Increase innodb_lock_wait_timeout for long-running migrations

For known-long maintenance operations where a larger wait is acceptable.

fix

SET SESSION innodb_lock_wait_timeout = 300;  -- 5 minutes
-- Run the migration
SET SESSION innodb_lock_wait_timeout = 50;   -- restore default

Why this works

innodb_lock_wait_timeout controls how long a transaction waits for a lock in seconds. Increasing it at the session level for a specific operation avoids 1205 without affecting other connections.

What not to do

✗

Set innodb_lock_wait_timeout to a very large value globally

Why it's wrong: A large global timeout means stuck transactions block other work for longer before being rolled back, degrading throughput across the entire application.

Version notes

MariaDB 10.3+innodb_lock_wait_timeout default is 50 seconds. The innodb_deadlock_detect variable (default ON) controls automatic deadlock detection which is separate from this timeout.