What is InnoDB Lock Wait Timeout in MySQL and How It Works

The innodb_lock_wait_timeout is a MySQL setting that controls how long a transaction waits to acquire a row lock before giving up and returning an error. It helps prevent long waits caused by locked rows in InnoDB tables, ensuring your database stays responsive.

How It Works

Imagine you are waiting in line to use a shared tool, but someone else is using it. You can wait only so long before you decide to leave and try again later. In MySQL, when a transaction tries to change data that another transaction has locked, it must wait until the lock is released.

The innodb_lock_wait_timeout setting tells MySQL how many seconds to wait for that lock before stopping and returning an error. This prevents your application from hanging forever if the lock is held too long.

It works like a timer: if the lock is released before the timeout, your transaction continues smoothly. If not, MySQL stops waiting and reports a lock wait timeout error, so you can handle it in your application.

Example

This example shows how to check and set the innodb_lock_wait_timeout value, and what happens when a lock wait timeout occurs.

sql

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

SET innodb_lock_wait_timeout = 5;

-- Transaction 1: Lock a row
START TRANSACTION;
SELECT * FROM employees WHERE id = 1 FOR UPDATE;
-- Keep this transaction open to hold the lock

-- Transaction 2: Try to lock the same row
START TRANSACTION;
SELECT * FROM employees WHERE id = 1 FOR UPDATE;
-- This will wait up to 5 seconds before timing out

-- After 5 seconds, Transaction 2 will get an error:
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction;

Output

Variable_name       Value
innodb_lock_wait_timeout 50

-- After setting to 5, the second transaction waits 5 seconds then errors:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

When to Use

Use innodb_lock_wait_timeout to control how long your application waits for locked rows in InnoDB tables. It is helpful when you want to avoid long delays caused by deadlocks or slow transactions holding locks.

For example, in a busy web application, setting a reasonable timeout prevents users from waiting too long when data is locked. It also helps detect and handle locking problems early by returning an error you can catch and retry.

Adjust the timeout based on your workload: shorter for fast, interactive apps; longer for batch jobs that need more time.

Key Points

• innodb_lock_wait_timeout sets the max seconds to wait for a row lock in InnoDB.
• It prevents transactions from waiting forever on locked rows.
• If timeout is reached, MySQL returns an error to the client.
• Adjust the timeout based on your application's needs and workload.
• Use error handling to retry or handle lock wait timeout errors gracefully.

Key Takeaways

innodb_lock_wait_timeout controls how long a transaction waits for a row lock before timing out.

Setting a proper timeout prevents long delays and keeps your database responsive.

If the timeout is exceeded, MySQL returns a lock wait timeout error (error 1205).

Adjust the timeout value based on your application's workload and locking behavior.

Handle lock wait timeout errors in your application to retry or fail gracefully.