MySQL Error 1205: Lock Wait Timeout — 3 Fixes That Work

Quick Fix (30 seconds): Retry the Transaction

When you see ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction, the simplest thing is to just retry. This sounds dumb, but it works more often than you'd think. The lock that blocked you probably got released a second later. You don't need to change anything yet.

Here's what to do:

1. Look at your application code—find the transaction that failed.
2. Wrap the transaction in a retry loop (3 attempts is reasonable).
3. Make sure your code commits or rolls back properly on each attempt.

Real-world trigger: A cron job that updates a user's balance at the same time as a web request that reads the same row. The web request holds the lock for 100ms, your cron times out. A retry fixes it.

If retrying doesn't help, or the error keeps appearing, move to the moderate fix.

Moderate Fix (5 minutes): Tune the Timeout and Transaction Scope

If retrying isn't enough, you have two options: increase the timeout or reduce the time your transactions hold locks. Both are valid, but I'd start with reducing lock time—that's the real fix. Increasing the timeout just postpones the problem.

Option A: Increase innodb_lock_wait_timeout

Run this in your MySQL session to see the current value:

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

Default is 50 seconds. If you want to bump it to 120 seconds globally, edit your my.cnf (or my.ini on Windows) and add:

[mysqld]
innodb_lock_wait_timeout = 120

Then restart MySQL. You can also change it dynamically without a restart:

SET GLOBAL innodb_lock_wait_timeout = 120;

But that only affects new connections. For the current session:

SET SESSION innodb_lock_wait_timeout = 120;

I prefer setting it per-session when I'm debugging, not globally. Saves you from masking deeper issues.

Option B: Shrink Your Transaction Scope

The real culprit is usually a transaction that does too much. If you're updating 10,000 rows in one go, you're holding locks on all of them. Break it into batches of 500 or 1000. That shortens lock hold time dramatically.

Also, look for accidental long-running transactions. A classic mistake: starting a transaction, doing some application logic (like an HTTP call), then committing. That 5-second HTTP call holds the lock hostage. Move commits as early as possible.

Here's a pattern to avoid:

START TRANSACTION;
SELECT ... FOR UPDATE;
-- application does slow stuff (network call, file read)
UPDATE ...;
COMMIT;

Instead, do the slow stuff before START TRANSACTION.

Advanced Fix (15+ minutes): Find and Kill the Blocking Transaction

If the error still persists, something is holding locks for a long time. You need to identify it. Here's a systematic way.

Step 1: Check InnoDB Status

Run this in MySQL:

SHOW ENGINE INNODB STATUS\G

Look for the LATEST DETECTED DEADLOCK section, but more importantly, look at TRANSACTIONS. You'll see a list of transactions, their state (RUNNING, LOCK WAIT, etc.), and what locks they hold. If you see a transaction that's been RUNNING for minutes, that's your blocker.

Step 2: Query the Information Schema

Use these queries to find blocking transactions:

SELECT * FROM information_schema.INNODB_TRX\G

This shows all running transactions, including trx_started and trx_state. The one with the oldest trx_started is likely the culprit.

To see what locks are being waited on:

SELECT * FROM sys.innodb_lock_waits\G

This gives you the blocking transaction ID and the waiting transaction ID.

Step 3: Kill the Blocking Transaction

Once you have the transaction ID, get its process ID from INNODB_TRX (column trx_mysql_thread_id). Then kill it:

KILL <thread_id>;

That's a blunt instrument. If the blocking transaction is a legitimate long-running process, killing it might cause issues. But if it's stuck (like a user who left a transaction open), killing it is the right move.

Step 4: Fix the Root Cause

Killing the blocker is temporary. You need to find out why transactions are holding locks so long. Common culprits:

• Missing indexes—causing full table scans and row locks on every row.
• Unused but open transactions due to application bugs (forgotten commit()).
• Locks being escalated due to large updates.

To check for missing indexes, run EXPLAIN on your slow queries. Look for type=ALL or rows being much higher than expected. Adding an index on the columns used in WHERE clauses often fixes lock contention instantly.

Also, enable the slow query log if you haven't already:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;

Then check which queries are slow and lock-heavy. Those are your targets.

One More Thing: Deadlock vs. Lock Timeout

Sometimes people confuse error 1205 with deadlock errors (error 1213). They're different. A deadlock happens when two transactions wait on each other, and InnoDB detects it immediately and rolls one back. A lock timeout means one transaction waits longer than innodb_lock_wait_timeout for another to release a lock. The fix for deadlocks is often different (like ensuring consistent lock ordering). For 1205, the fixes above are your best bets.

In my experience, most 1205 errors come from application code that holds transactions open too long. Start with the retry, then shorten your transactions, and only touch the timeout if you really need to. You'll save yourself a lot of headaches.