How MySQL Lock Waits Differ From Deadlocks

In a production MySQL database, a transaction is a group of database statements that MySQL commits together or rolls back. A lock wait timeout means a statement waited too long for another transaction to release a lock. A deadlock means two or more transactions are waiting on one another in a cycle. InnoDB rolls back different amounts of work for these two failures.

By default, InnoDB rolls back only the statement that times out, leaving the rest of its transaction open. When InnoDB detects a deadlock, it rolls back the victim transaction in full. That difference determines whether your code should retry one statement or restart the transaction.

What happens in each failure

A lock wait timeout occurs when an InnoDB statement waits longer than innodb_lock_wait_timeout for a lock. The setting defaults to 50 seconds. For example, one transaction updates a row and stays open; another transaction tries to update that row and waits. If the first transaction does not release the lock before the timeout, the waiting statement fails with error 1205 (HY000). The MySQL manual documents this default and timeout behavior.

A deadlock occurs when transactions form a circular wait. For example, transaction A locks row 1 and requests row 2, while transaction B locks row 2 and requests row 1. Neither can proceed until the other releases a lock. InnoDB detects the cycle and chooses a transaction to roll back, returning error 1213 (40001). The MySQL manual explains how InnoDB detects deadlocks and rolls back a victim transaction.

The timeout behavior has an important setting: innodb_rollback_on_timeout. Its default value, 0, means InnoDB does not roll back the whole transaction when a statement times out. Setting it to 1 changes timeout handling so InnoDB rolls back the full transaction, as described in the MySQL manual’s error-handling rules. Check the value in the environment where the error occurred before deciding what work remains pending.

How to recognize and inspect the failure

Start with the error returned to the client. Error 1205 indicates that a lock wait exceeded its configured limit; error 1213 indicates that InnoDB detected a deadlock. The error messages identify the immediate cause. They do not show which transaction held the lock or what work remains in an open transaction.

For a deadlock, run SHOW ENGINE INNODB STATUS and inspect the LATEST DETECTED DEADLOCK section. The report identifies the transactions involved and the locks they held or requested. It also names the transaction InnoDB rolled back. The MySQL manual’s InnoDB monitor documentation describes this output. Because it reports the latest detected deadlock, capture it soon after the incident if you need to preserve the details.

To inspect a lock wait that is still happening, check performance_schema.data_lock_waits alongside performance_schema.data_locks. The wait table identifies which lock is blocking another; the lock table gives details about each lock. You can also inspect information_schema.innodb_trx to find active transactions. See the MySQL manual for data_lock_waits, data_locks, and InnoDB transaction information. These tables show current state, so they are most useful while the wait is still happening.

A timeout often points to a transaction that held a lock too long, a statement that scanned or updated more rows than intended, or transactions competing for locks while a large batch runs. A deadlock points to transactions acquiring locks in conflicting orders, though broad scans and long transactions can also increase the chance of overlap. Use the lock and transaction details to identify the statements involved. Raising the timeout does not fix the cause.

How to retry without repeating work

After a deadlock, InnoDB rolls back the chosen victim’s transaction, so retry the entire transaction. After a timeout, the failed statement has been rolled back by default, but earlier statements in the transaction have not. To preserve transaction-level atomicity, explicitly roll back the transaction and retry the whole unit of work. Retry only the failed statement when you understand what remains in the transaction and your code is meant to keep that work.

For example, suppose a transaction first records an order and then updates inventory. If the inventory update times out, the order insert remains part of the open transaction under the default timeout behavior. Rolling back before retrying prevents the next attempt from building on that partial state.

A database rollback does not undo an email, an external API request, or another action that has already happened outside MySQL. Keep such effects out of a retryable transaction where possible. A common design is to record the intended side effect in the same database transaction and publish it after commit; where an external request must be retried, use an idempotency key so the recipient can recognize duplicate requests. Retry only errors your code treats as transient, and bound retries so persistent contention does not keep requests waiting indefinitely.

Reduce repeated lock conflicts

Keep transactions short and acquire rows in a consistent order. If one code path updates a customer row before an order row, make other paths that need both rows follow the same order. Add indexes that let MySQL locate rows efficiently; broad scans can make statements touch more records and hold locks longer. These changes reduce competition for locks and make deadlock cycles less likely.

Increasing innodb_lock_wait_timeout extends how long a waiting statement waits. The blocking transaction keeps its locks, and conflicting access patterns remain unchanged. Investigate the transaction and statement shown in lock diagnostics before changing the timeout.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com