Troubleshooting MySQL Transaction Lock Waits: Diagnosis and Resolution

The article describes a common MySQL problem where some transactional statements cannot be committed and the associated sessions remain active for a long time, making it difficult to identify the offending SQL using the show processlist command.

1. Fault Background

During database operation, certain transactions stay in a waiting state, and the usual show processlist output does not reveal the SQL that caused the lock.

2. Fault Reproduction

2.1 Simulate Two Sessions

-- 会话 1
mysql> begin;
mysql> delete from db02.order_info where id in (12,13);

-- 会话 2
mysql> begin;
mysql> update db02.order_info set create_time='2025-02-10 10:00:00' where id=12;
-- The statement exceeds innodb_lock_wait_timeout and rolls back.
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- Set session lock‑wait timeout for testing
mysql> set session innodb_lock_wait_timeout=3600;
mysql> update db02.order_info set create_time='2025-02-10 10:00:00' where id=12;

2.2 Retrieve Running Statements

Query the currently executing statements that are not in the Sleep state:

select * from information_schema.processlist where COMMAND <> 'Sleep';

The result does not show the UPDATE statement that is waiting.

3. Investigation Steps

3.1 View Uncommitted Transactions

SELECT trx_id,trx_state,trx_started,trx_tables_locked,trx_rows_locked FROM information_schema.innodb_trx\G;

This reveals two transactions: one in LOCK WAIT (the UPDATE) and one in RUNNING (the DELETE).

3.2 View Lock‑Wait Information

SELECT wait_started, locked_table, waiting_trx_id, blocking_trx_id, sql_kill_blocking_connection FROM sys.innodb_lock_waits\G;

The query shows that transaction 3600172 is waiting for a lock held by transaction 3600069, and provides the KILL command ( KILL 376283) to terminate the blocker.

3.3 Find the Blocking SQL

SELECT a.thread_id,a.sql_text FROM performance_schema.events_statements_history a WHERE THREAD_ID = (SELECT THREAD_ID FROM performance_schema.threads b WHERE b.PROCESSLIST_ID = 376283);

The result includes the DELETE statement delete from db02.order_info where id in(12,13), which is the lock source.

4. Solution

Terminate the blocking session using the KILL command:

KILL 376283

5. Summary

5.1 Relevant System Tables

Table Name

Purpose information_schema.processlist Shows current SQL statements and session status. information_schema.innodb_trx Details of uncommitted InnoDB transactions. sys.innodb_lock_waits Records lock‑wait information and the SQL to kill the blocker. performance_schema.events_statements_current Current executing SQL statements. performance_schema.events_statements_history Historical SQL statements per thread. performance_schema.threads Thread metadata, used to map PROCESSLIST_ID to THREAD_ID.

5.2 Quick‑Check Query

For urgent production incidents, the following query aggregates the essential information:

SELECT a.THREAD_ID, a.SQL_TEXT, b.PROCESSLIST_ID, DATE_FORMAT(c.trx_started, '%Y-%m-%d %H:%i:%s') AS transaction_start_time FROM performance_schema.events_statements_history a JOIN performance_schema.threads b ON a.THREAD_ID = b.THREAD_ID JOIN information_schema.innodb_trx c ON b.PROCESSLIST_ID = c.trx_mysql_thread_id;

The result lists thread IDs, SQL text, processlist IDs and transaction start times, making it easy to spot the blocking statement.

5.3 InnoDB Lock‑Wait Parameters

Comparison Item

lock_wait_timeout

innodb_lock_wait_timeout

Scope

All storage engines (table‑level locks)

Only InnoDB internal locks

Default Value

31,536,000 seconds (1 year)

Usually 50 seconds

Mechanism

Non‑InnoDB operations wait; exceed limit → error

InnoDB transactions wait; exceed limit → automatic rollback and error

Adjusting innodb_lock_wait_timeout can help control lock‑wait timeout behavior and improve concurrency.