Fix Lock Wait Timeout Exceeded in MySQL (Quick Steps)

Why This Error Happens

You're running a query, and after 50 seconds (default) MySQL says: "Lock wait timeout exceeded; try restarting transaction". This happens when your transaction tries to update a row that another transaction has locked and isn't releasing fast enough.

Real-world example: You're updating a user's balance in a busy e-commerce site, and another open transaction from a checkout process is holding the lock on that same row. Your query waits, waits, then times out.

Here's the fix for each skill level. Stop when your issue is resolved.

Simple Fix (30 seconds) — Kill the Blocking Transaction

This is the fastest fix. You find the transaction that's holding the lock and kill it.

Step 1: Find the blocking transaction

Open MySQL command line or any SQL client. Run this:

SHOW PROCESSLIST;

Look for queries with a State column that says Updating or locked. Note the Id number. Those are transactions holding locks.

Step 2: Kill the blocking transaction

Run this command, replacing 1234 with the actual Id:

KILL 1234;

After you kill the blocking transaction, your waiting query should complete immediately. You'll see the output change from waiting to success.

Important: Killing a transaction can leave data in an inconsistent state. The transaction will roll back, so any partial writes are undone. This is safe for the database integrity.

Moderate Fix (5 minutes) — Find and Investigate All Locks

If the simple fix worked but the problem comes back, you need to understand which transactions are competing for locks.

Step 1: Check for active locks

Run this to see what's locked and who's waiting:

SELECT * FROM performance_schema.data_locks;

You'll see a table. Look at the LOCK_TYPE column. RECORD means a row lock. TABLE means a table lock. The LOCK_STATUS column tells you if it's WAITING or GRANTED.

Step 2: Identify the blocking transaction ID

Then run:

SELECT * FROM performance_schema.data_lock_waits;

This shows which transaction (in REQUESTING_ENGINE_TRANSACTION_ID) is waiting on which transaction (in BLOCKING_ENGINE_TRANSACTION_ID).

Step 3: Kill the blocking transaction

Use the blocking transaction ID to find its MySQL process ID:

SELECT THREAD_ID, PROCESSLIST_ID FROM performance_schema.threads WHERE PROCESSLIST_ID IS NOT NULL;

Match the transaction ID to a thread, then kill the process as before.

Step 4: Check your application code

Often the real problem is a transaction that opens but never commits. Look in your code for:

• Missing COMMIT or ROLLBACK after a BEGIN
• Long-running loops inside a transaction
• User input that pauses a transaction waiting for confirmation

Fix those to prevent future lock waits.

Advanced Fix (15+ minutes) — Tune the Timeout Setting

If you've killed blocking transactions and fixed the code but still get timeouts, it's time to adjust MySQL's timeout setting.

Step 1: Check current timeout

Run:

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

Default is 50 (seconds). If your queries are legitimate and take longer than 50 seconds to run inside transactions, you need to increase this.

Step 2: Change timeout temporarily (without restart)

Run this to set it to 100 seconds for the current session:

SET SESSION innodb_lock_wait_timeout = 100;

Run your query again. If it completes, you know the timeout was too short.

Step 3: Make the change permanent

Edit your MySQL config file. Common locations:

• Linux: /etc/mysql/my.cnf or /etc/my.cnf
• Windows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

Add this line under the [mysqld] section:

innodb_lock_wait_timeout=100

Save the file. Restart MySQL (non-production servers only, or schedule a maintenance window):

sudo systemctl restart mysql

After restart, verify with SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; — it should show 100.

One More Thing — Deadlock vs Timeout

Don't confuse this error with a deadlock (error 1213). A deadlock happens when two transactions each hold a lock the other needs. MySQL kills one transaction automatically. A lock wait timeout means one transaction waited too long for a single lock. The fix is different: for deadlocks, retry the transaction; for timeouts, kill the blocker or increase the timeout.

When All Else Fails

If you still get timeouts after all this, check if your table is too large or your queries are missing indexes. An update on a table with no index will lock the whole table instead of a single row, causing contention. Add indexes on the columns used in WHERE clauses of your UPDATE and DELETE statements.

Example: If you update users by email, make sure email column has an index:

ALTER TABLE users ADD INDEX idx_email (email);

That simple change can turn a table-level lock into a row-level lock, fixing your timeout issue permanently.