InnoDB表锁等待超时排查：操作报错但未检测到锁表

First off, let's clear up why show open tables where in_use>0; didn't show anything—that command only tracks table-level locks, which InnoDB barely uses by default (it relies on row-level locks for most operations). So we need to use InnoDB-specific tools to dig into the actual locks causing your timeout.

Here's a step-by-step breakdown to diagnose and fix the issue:

1. Check InnoDB's Detailed Transaction & Lock Status #

Run this command to get a full snapshot of InnoDB's internal state:

SHOW ENGINE INNODB STATUS;

Scroll down to the TRANSACTIONS section. Look for entries labeled WAITING FOR THIS LOCK TO BE GRANTED—this will tell you which transaction is waiting, and which transaction (by ID) is holding the lock it needs. You'll also see details about the locked row/resource here.

2. List All Active Transactions #

Use this query to get a structured view of all ongoing transactions:

SELECT trx_id, trx_state, trx_query, trx_mysql_thread_id 
FROM INFORMATION_SCHEMA.INNODB_TRX;

• Look for the transaction with trx_state = 'LOCK WAIT'—this is your stuck DELETE statement.
• The trx_wait_for_trx_id column will show you the ID of the transaction holding the lock.
• Find that holding transaction in the same results, check its trx_query to see what it's doing (e.g., an uncommitted UPDATE or DELETE on the same row), and note its trx_mysql_thread_id.

3. Inspect Lock Details (For Newer MySQL Versions) #

If INFORMATION_SCHEMA.INNODB_LOCKS returns no results (it's deprecated in newer MySQL versions), use the Performance Schema instead:

SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

These tables will show you exactly which rows are locked, which threads hold the locks, and which threads are waiting on them.

4. Resolve the Lock #

Once you've identified the thread holding the lock, kill it to release the lock immediately:

KILL [trx_mysql_thread_id];

Replace [trx_mysql_thread_id] with the thread ID you found in step 2. After killing the thread, re-run your DELETE statement—it should work now.

5. Prevent Future Timeouts #

• Check transaction logic: Make sure your application commits or rolls back transactions promptly. Long-running transactions (e.g., holding locks while doing external API calls) are a common culprit.
• Verify indexes: If your id column isn't indexed (or isn't the primary key), InnoDB will do a full table scan and apply row locks to every row it checks—this increases the chance of lock conflicts. Ensure id is a primary key or has a unique index.
• Adjust timeout (last resort): If you can't eliminate the lock conflicts, you can increase the innodb_lock_wait_timeout parameter (default is 50 seconds) in your MySQL config, but this is a band-aid, not a fix.

内容的提问来源于stack exchange，提问作者FireFalcon