Home / Database / MySQL InnoDB Lock Wait Timeout

Database

MySQL InnoDB Lock Wait Timeout

MySQL queries fail with lock wait timeout when transactions hold row locks too long, blocking other transactions.

Published: Dec 10, 20259 min readBy FixWikiHub Editorial Team

Abstract illustration for a troubleshooting knowledge base category.

Introduction

MySQL InnoDB uses row-level locking to ensure transaction isolation and data consistency. When a transaction holds locks on rows and another transaction attempts to access those same rows, the second transaction waits for the locks to be released. If the waiting time exceeds the innodb_lock_wait_timeout setting (default 50 seconds), MySQL returns "Lock wait timeout exceeded" error. This commonly occurs with long-running transactions, poorly optimized queries that scan many rows, or concurrent access patterns that create lock contention. Understanding lock types, identifying blocking transactions, and optimizing access patterns is essential for resolving lock wait timeouts.

Symptoms

When MySQL lock wait timeout occurs, you will observe:

SQL queries fail with error code 1205
Application reports "Lock wait timeout exceeded; try restarting transaction"
Long-running transactions blocking other operations
Dashboard or report queries timing out
Batch jobs failing intermittently

MySQL error message: ``ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

In application logs: ``SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

MySQL error log shows: ``[Warning] InnoDB: A long semaphore wait: --Thread 140123456789 has waited at buf0buf.cc line 123 for 51.00 seconds

Common Causes

1.Long-running transaction - Transaction holds locks for extended period
2.Uncommitted transaction - Transaction started but never committed
3.Lock contention hotspot - Multiple transactions competing for same rows
4.Table scans requiring locks - Full table scans lock many rows
5.Foreign key checks - FK constraints cause additional locking
6.Gap locks for inserts - Insert operations create gap locks
7.Deadlock not detected - Potential deadlock not caught by detector
8.Insufficient index - Query locks more rows than necessary
9.High concurrency - Too many simultaneous transactions
10.Slow queries in transaction - Slow queries extend lock duration

Step-by-Step Fix

Step 1: Identify Blocking Transactions

Find transactions currently holding locks:

sql

-- Check for transactions waiting for locks
SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    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_duration_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;

Check long-running transactions: ``sql -- Find transactions running longer than 10 seconds SELECT trx_id, trx_mysql_thread_id AS thread_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_seconds, trx_query, trx_rows_locked, trx_rows_modified FROM information_schema.innodb_trx WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 10 ORDER BY duration_seconds DESC;

View lock details: ``sql -- In MySQL 8.0+, use performance_schema SELECT OBJECT_NAME AS table_name, INDEX_NAME, LOCK_TYPE, LOCK_MODE, THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO FROM performance_schema.data_locks WHERE OBJECT_SCHEMA = 'your_database';

Step 2: Check Current Lock Timeout Setting

Review the timeout configuration:

```sql -- Check lock wait timeout SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- Check other relevant settings SHOW VARIABLES LIKE 'innodb_%lock%'; SHOW VARIABLES LIKE 'transaction%'; ```

Variable_name	Value
innodb_lock_wait_timeout	50

Step 3: Examine Blocking Query

Identify what the blocking transaction is doing:

```sql -- Get the blocking thread's processlist info SELECT * FROM information_schema.processlist WHERE id = <blocking_thread_id>;

-- Show full processlist for all active queries SHOW FULL PROCESSLIST; ```

If blocking query is running: ``sql -- In MySQL 8.0+, see currently executing statement SELECT THREAD_ID, PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST, PROCESSLIST_DB, PROCESSLIST_COMMAND, PROCESSLIST_TIME, PROCESSLIST_INFO, PROCESSLIST_STATE FROM performance_schema.threads WHERE PROCESSLIST_ID = <thread_id>;

Step 4: Kill Blocking Transaction (Immediate Fix)

If blocking transaction is stuck or runaway:

```sql -- First, identify the blocking thread SELECT trx_mysql_thread_id FROM information_schema.innodb_trx WHERE trx_id = <blocking_trx_id>;

-- Kill the blocking thread (use with caution!) KILL <thread_id>;

-- Or kill query but keep connection KILL QUERY <thread_id>; ```

From command line: ```bash # Get blocking thread IDs mysql -e "SELECT trx_mysql_thread_id FROM information_schema.innodb_trx WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;" -N

# Kill specific thread mysql -e "KILL 12345;" ```

Step 5: Increase Lock Wait Timeout

Adjust timeout for specific session or globally:

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

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

-- Per transaction (MySQL 8.0+) SET @@SESSION.innodb_lock_wait_timeout = 120; START TRANSACTION; -- Your queries here COMMIT; ```

For permanent change, edit configuration: ``bash sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Add: ``ini [mysqld] innodb_lock_wait_timeout = 120

Restart MySQL: ``bash sudo systemctl restart mysql

Step 6: Optimize Queries to Reduce Lock Time

Improve queries that hold locks too long:

Bad: Locking many rows during update ``sql -- This locks ALL rows matching condition UPDATE orders SET status = 'processed' WHERE status = 'pending';

Better: Process in smaller batches ``sql -- Process in batches of 1000 SET @batch_size = 1000; WHILE EXISTS (SELECT 1 FROM orders WHERE status = 'pending' LIMIT 1) DO UPDATE orders SET status = 'processed' WHERE status = 'pending' LIMIT @batch_size; COMMIT; -- Small delay to allow other transactions DO SLEEP(0.1); END WHILE;

Bad: Long transaction with multiple operations ``sql START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Many other operations... UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- Long pause here holds locks INSERT INTO transactions VALUES (...); COMMIT;

Better: Minimize transaction scope ``sql -- Keep transaction as short as possible START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; -- Do other work outside transaction INSERT INTO transactions VALUES (...);

Step 7: Add Indexes to Reduce Lock Scope

Missing indexes cause more rows to be locked:

```sql -- Check if proper indexes exist EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

-- If type is 'ALL' or 'range' with many rows, add index CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- Analyze table after adding index ANALYZE TABLE orders; ```

Step 8: Handle Foreign Key Locking

Foreign keys can cause additional locking:

```sql -- Check foreign key constraints SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'your_database';

-- Consider disabling FK checks for bulk operations SET FOREIGN_KEY_CHECKS = 0; -- Bulk operations SET FOREIGN_KEY_CHECKS = 1; ```

Step 9: Use Read Committed for Less Locking

If appropriate for your application:

```sql -- Check current isolation level SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;

-- Set to READ COMMITTED (less locking than REPEATABLE READ) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Or globally SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; ```

Verification

After implementing fixes, verify lock issues are resolved:

```sql -- Monitor for lock waits SELECT COUNT(*) AS lock_waits FROM information_schema.innodb_lock_waits; -- Should be 0 or minimal

-- Check for long transactions SELECT COUNT(*) AS long_transactions FROM information_schema.innodb_trx WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 10; -- Should be low

-- Monitor lock wait timeout errors SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_timeouts'; ```

Test queries that previously timed out: ``sql -- Test the problematic query SELECT ... FOR UPDATE; -- Should complete within timeout

Prevention

To prevent lock wait timeouts:

1.Keep transactions short:
2.```sql
3.-- Bad: Transaction spans user interaction
4.START TRANSACTION;
5.SELECT ... FOR UPDATE;
6.-- Wait for user input (BAD!)
7.UPDATE ...;
8.COMMIT;

-- Good: Transaction is atomic START TRANSACTION; UPDATE ...; COMMIT; ```

1.Use appropriate isolation levels:
2.```sql
3.-- READ COMMITTED for less locking
4.SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
5.`
6.Add indexes for queries that lock rows:
7.```sql
8.-- Ensure WHERE clause columns are indexed
9.CREATE INDEX idx_status ON orders(status);
10.`
11.Process in batches:
12.```sql
13.-- Don't lock entire table
14.DELETE FROM logs WHERE created_at < '2024-01-01' LIMIT 1000;
15.-- Repeat in loop
16.`
17.Monitor lock metrics:
18.```sql
19.-- Set up monitoring
20.SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';
21.`
22.Use optimistic locking:
23.```sql
24.-- Instead of SELECT FOR UPDATE
25.UPDATE products
26.SET stock = stock - 1, version = version + 1
27.WHERE id = 123 AND version = 5;
28.-- If affected rows = 0, version changed, retry
29.`
30.Set up alerts for long transactions:
31.```sql
32.-- Check periodically (e.g., every minute)
33.SELECT COUNT(*) FROM information_schema.innodb_trx
34.WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30;
35.-- Alert if > 0
36.`

[Database troubleshooting: Fix Backup Exclusive Lock Table Production Writes ](backup-exclusive-lock-table-production-writes)
[Fix Connection Pool Leak Application Not Closing Issue in Database](connection-pool-leak-application-not-closing)
[Fix Connection Reset Idle Timeout Firewall Issue in Database](connection-reset-idle-timeout-firewall)
[Fix Connection Reset Idle Timeout Serverless Database Issue in Database](connection-reset-idle-timeout-serverless-database)
[Fix Connection String Encoding Special Characters Issue in Database](connection-string-encoding-special-characters)

Was this guide helpful?

Related search paths

People also search for

If the symptom is close but not identical, these search paths usually surface the right neighboring fixes faster than scrolling the full archive.

MySQL InnoDB Lock Wait Timeout MySQL InnoDB Lock Wait Timeout Database MySQL InnoDB Lock Wait Timeout troubleshooting MySQL InnoDB Lock Wait Timeout fix MySQL queries fail with lock wait timeout when transactions hold row locks too long, blocking other transactions Database MySQL queries fail with lock wait timeout when transactions hold row locks too long, blocking other transactions

Explore Related Topics

Browse Guides from Other Categories

Discover troubleshooting guides from related categories to expand your knowledge.

FAQ

Database Troubleshooting FAQs

Common questions about troubleshooting and preventing similar issues

How do I know if this database-errors troubleshooting guide applies to my situation?

This guide is designed for database-errors issues. If you're experiencing similar symptoms described in the article, follow the step-by-step instructions. Start with the most common causes and work through the diagnostic process.

Is it safe to follow these database-errors troubleshooting steps?

Yes, all steps are designed to be safe and non-destructive. We recommend creating backups before making significant changes and testing each step before proceeding to the next.

How long does it typically take to resolve this type of database-errors issue?

Most database-errors issues can be resolved within 30 minutes to 2 hours, depending on the complexity and root cause. Follow the troubleshooting flow to identify and fix the problem efficiently.

How can I prevent this database-errors issue from happening again?

Regular maintenance, monitoring, and following best practices for database-errors configuration can help prevent recurrence. Consider implementing automated checks and alerts for early detection.

Written by

FixWikiHub Editorial Team

Our editorial team consists of experienced DevOps engineers, systems administrators, and cloud architects with hands-on experience in production environments across AWS, Azure, GCP, and on-premises infrastructure.

Every guide undergoes technical review for accuracy and is updated when software versions, commands, or best practices change.

Last updated: Dec 10, 2025

About our team

Important Notice

Disclaimer & Safety Guidelines

The troubleshooting steps in this guide are provided for educational and informational purposes. Before applying any changes to production systems:

Test in a staging environment first — Always verify commands and configurations in a non-production environment before deploying to live systems.
Create backups — Ensure you have current backups of databases, configurations, and critical files before making changes.
Understand the impact — Review how each step may affect your specific environment, dependencies, and users.
Consult official documentation — This guide supplements, but does not replace, official vendor documentation and best practices.

FixWikiHub is not responsible for any damages arising from the use of this content. See our Terms of Use for more information.

Resources

Official Documentation & Further Reading

For authoritative information, consult the official documentation for the technologies discussed in this guide. Our troubleshooting content supplements, but does not replace, vendor documentation.

AWS Documentation — Official Amazon Web Services guides and API references
Kubernetes Documentation — Official Kubernetes documentation
Nginx Documentation — Official Nginx web server documentation
Apache Documentation — Official Apache HTTP Server documentation
Docker Documentation — Official Docker container documentation

MySQL InnoDB Lock Wait Timeout

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Step 1: Identify Blocking Transactions

Step 2: Check Current Lock Timeout Setting

Step 3: Examine Blocking Query

Step 4: Kill Blocking Transaction (Immediate Fix)

Step 5: Increase Lock Wait Timeout

Step 6: Optimize Queries to Reduce Lock Time

Step 7: Add Indexes to Reduce Lock Scope

Step 8: Handle Foreign Key Locking

Step 9: Use Read Committed for Less Locking

Verification

Prevention

People also search for

Browse Guides from Other Categories

WordPress

SSL

DNS

Database Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading

MySQL InnoDB Lock Wait Timeout

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Step 1: Identify Blocking Transactions

Step 2: Check Current Lock Timeout Setting

Step 3: Examine Blocking Query

Step 4: Kill Blocking Transaction (Immediate Fix)

Step 5: Increase Lock Wait Timeout

Step 6: Optimize Queries to Reduce Lock Time

Step 7: Add Indexes to Reduce Lock Scope

Step 8: Handle Foreign Key Locking

Step 9: Use Read Committed for Less Locking

Verification

Prevention

Related Articles

People also search for

Share this guide

More Database Troubleshooting Guides

Browse Guides from Other Categories

Database Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading