EC
1205
MySQLERRORNotableLocking & ConcurrencyHIGH confidence

Lock wait timeout exceeded; try restarting transaction

Production Risk

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
  1. 1A long-running transaction holds a row lock and another transaction is waiting to modify the same row
  2. 2An uncommitted transaction is holding locks on many rows, blocking other transactions
  3. 3A batch import or update is holding locks for longer than innodb_lock_wait_timeout
  4. 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
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

expected output

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

Fix 1

Keep transactions short — commit as soon as possible

WHEN As a general best practice for all write transactions.

Keep transactions short — commit as soon as possible
-- 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

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

Increase innodb_lock_wait_timeout for long-running migrations
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

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.

Sources

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors