# MySQL Lock Wait Timeout Exceeded

Your application fails with this error:

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

This error occurs when a transaction waits too long for a lock held by another transaction. The default timeout is 50 seconds. Understanding and resolving this requires investigating what's holding the locks.

Understanding Lock Wait Timeout

MySQL uses locks to ensure data consistency during concurrent access: - Row locks: Lock individual rows (InnoDB) - Table locks: Lock entire tables (MyISAM, or explicit LOCK TABLES) - Gap locks: Lock ranges between rows (InnoDB for REPEATABLE READ)

When Transaction A holds a lock and Transaction B needs the same lock, B waits. If B waits longer than innodb_lock_wait_timeout, it fails with ERROR 1205.

Step 1: Identify Blocking Transactions

Check current lock waits:

sql
-- MySQL 8.0+
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

Check all active transactions:

sql
SELECT 
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) as seconds_running,
    trx_mysql_thread_id,
    trx_query,
    trx_rows_locked,
    trx_lock_structs
FROM information_schema.innodb_trx
ORDER BY trx_started;

Check processlist for long-running transactions:

```sql SHOW PROCESSLIST;

-- Or more detailed SELECT id, user, host, db, command, time, state, info FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY time DESC; ```

Check InnoDB lock status:

sql
SHOW ENGINE INNODB STATUS\G

Look for the "TRANSACTIONS" section:

bash
------------
TRANSACTIONS
------------
Trx id counter 123456
Purge done for trx's n:o < 123455
History list length 100
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421123456789123, not started
---TRANSACTION 123456, ACTIVE 45 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 42, OS thread handle 12345, query id 123 localhost root updating
UPDATE users SET balance = balance - 100 WHERE id = 1
------- TRX HAS BEEN WAITING 45 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table `mydb`.`users`
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

Step 2: Find and Kill Blocking Transactions

Identify the blocker:

sql
-- Find transactions holding locks
SELECT 
    r.trx_id AS waiting_trx,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query,
    b.trx_started AS blocking_started,
    TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS blocking_seconds
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

Kill the blocking transaction:

```sql -- Kill by process ID KILL 42; -- Replace with actual process ID

-- Or kill all long-running transactions SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE time > 60 AND command = 'Sleep'; ```

Kill idle transactions with locks:

sql
-- Find transactions that are idle but holding locks
SELECT 
    p.id,
    p.user,
    p.host,
    p.db,
    p.time,
    t.trx_id,
    t.trx_started,
    TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS trx_age,
    t.trx_rows_locked
FROM information_schema.innodb_trx t
JOIN information_schema.processlist p ON p.id = t.trx_mysql_thread_id
WHERE p.command = 'Sleep' AND t.trx_rows_locked > 0
ORDER BY t.trx_started;

Step 3: Check for Deadlocks

Deadlocks occur when two transactions wait for each other. MySQL automatically detects and rolls back one transaction.

Check for recent deadlocks:

sql
SHOW ENGINE INNODB STATUS\G

Look for the "LATEST DETECTED DEADLOCK" section.

Enable deadlock monitoring:

sql
-- Enable deadlock logging
SET GLOBAL innodb_print_all_deadlocks = ON;

Add to configuration:

ini
[mysqld]
innodb_print_all_deadlocks = 1

Example deadlock analysis:

``` LATEST DETECTED DEADLOCK ------------------------ 2026-04-04 10:15:30 0x7f8b2c1b4700 *** (1) TRANSACTION: TRANSACTION 12345, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) UPDATE accounts SET balance = balance - 100 WHERE id = 1

*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table mydb.accounts

*** (2) TRANSACTION: TRANSACTION 12346, ACTIVE 3 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s) UPDATE accounts SET balance = balance + 50 WHERE id = 2

*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table mydb.accounts Record lock, heap no 2

*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table mydb.accounts Record lock, heap no 3 ```

Step 4: Adjust Timeout Settings

Check current timeout:

sql
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

Increase timeout temporarily:

```sql -- For current session SET innodb_lock_wait_timeout = 120;

-- Globally (for new connections) SET GLOBAL innodb_lock_wait_timeout = 120; ```

Permanent configuration:

ini
[mysqld]
innodb_lock_wait_timeout = 120  # Default 50, increase if needed

Transaction timeout (MySQL 5.7+):

sql
-- Kill transactions that run too long
SET SESSION max_execution_time = 60000;  -- 60 seconds

Step 5: Optimize Transaction Patterns

Lock wait timeouts often indicate transaction design problems:

Keep Transactions Short

```sql -- Bad: Long transaction with multiple operations START TRANSACTION; SELECT * FROM accounts WHERE id = 1; -- Application does slow processing here UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;

-- Good: Short, focused transaction -- Do all processing outside transaction -- Then commit quickly START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; ```

Access Tables in Consistent Order

```sql -- Bad: Different order causes deadlocks -- Transaction 1 UPDATE orders SET status = 'processed' WHERE id = 1; UPDATE order_items SET processed = 1 WHERE order_id = 1;

-- Transaction 2 (different order) UPDATE order_items SET processed = 0 WHERE order_id = 2; UPDATE orders SET status = 'pending' WHERE id = 2;

-- Good: Always same order (parent first, then children) -- Both transactions update orders first, then order_items ```

Use Appropriate Isolation Level

```sql -- Check current isolation level SELECT @@transaction_isolation;

-- READ COMMITTED has less locking than REPEATABLE READ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Start transaction with specific isolation START TRANSACTION; -- Your operations COMMIT; ```

Lock Rows Explicitly When Needed

```sql -- Lock rows you'll update SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- Now other transactions wait for this lock UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT;

-- Or use SKIP LOCKED for queue patterns SELECT * FROM jobs WHERE status = 'pending' FOR UPDATE SKIP LOCKED; ```

Step 6: Monitor and Alert on Lock Issues

Create a monitoring query:

```sql -- Check for long-running transactions SELECT id, user, host, db, command, time, state, info AS query FROM information_schema.processlist WHERE time > 30 AND command != 'Sleep' ORDER BY time DESC;

-- Check for lock waits SELECT COUNT(*) AS waiting_transactions FROM information_schema.innodb_trx WHERE trx_state = 'LOCK WAIT'; ```

Bash script for alerts:

```bash #!/bin/bash WAITING=$(mysql -N -e "SELECT COUNT(*) FROM information_schema.innodb_trx WHERE trx_state = 'LOCK WAIT'")

if [ "$WAITING" -gt 5 ]; then echo "$WAITING transactions waiting for locks" | mail -s "MySQL Lock Alert" admin@example.com fi ```

Performance Schema for detailed analysis (MySQL 5.7+):

```sql -- Enable lock monitoring UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'wait/lock%'; UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_waits%';

-- View lock waits SELECT EVENT_ID, SQL_TEXT, TIMER_WAIT/1000000000 AS wait_seconds, OBJECT_NAME, OPERATION FROM performance_schema.events_waits_current WHERE EVENT_NAME LIKE 'wait/lock%'; ```

Step 7: Handle Specific Scenarios

Batch Processing Locks

Large batch updates can hold locks for extended periods:

```sql -- Bad: Large single transaction START TRANSACTION; UPDATE large_table SET status = 'processed' WHERE status = 'pending'; COMMIT; -- Locks held for entire duration

-- Good: Small batches with commits -- Process in batches of 1000 UPDATE large_table SET status = 'processed' WHERE status = 'pending' LIMIT 1000; -- Repeat in loop until 0 rows affected ```

Foreign Key Locks

Foreign keys can cause additional locking:

```sql -- Check foreign keys SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'mydb';

-- Temporarily disable foreign key checks SET FOREIGN_KEY_CHECKS = 0; -- Your operations SET FOREIGN_KEY_CHECKS = 1; ```

Gap Locks

InnoDB uses gap locks in REPEATABLE READ isolation:

```sql -- Gap locks can block inserts -- Consider READ COMMITTED to reduce gap locks SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Or use unique indexes to reduce gap locking ALTER TABLE orders ADD UNIQUE INDEX idx_order_number (order_number); ```

Verification

After making changes:

```sql -- Check for waiting transactions SELECT COUNT(*) FROM information_schema.innodb_trx WHERE trx_state = 'LOCK WAIT';

-- Monitor timeout settings SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- Check recent deadlocks SHOW ENGINE INNODB STATUS\G ```

Test application to ensure lock timeouts are resolved.

Quick Reference

```sql -- Find blocking transactions SELECT * FROM information_schema.innodb_trx WHERE trx_state = 'LOCK WAIT';

-- Find what's blocking SELECT r.trx_id, b.trx_id, b.trx_query FROM information_schema.innodb_lock_waits w JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- Kill blocking transaction KILL process_id;

-- Change timeout SET innodb_lock_wait_timeout = 120;

-- Check locks SHOW ENGINE INNODB STATUS\G ```

Lock wait timeouts are usually symptoms of transaction design issues. Focus on keeping transactions short, accessing resources in consistent order, and proper indexing to minimize lock duration.