Investigation of Hanging Transactions and Lock Wait Timeout in MySQL InnoDB

This article shares the investigation of an IDB (MySQL InnoDB) transaction problem, covering both theoretical principles and practical troubleshooting.

Background : In a production environment the error

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

appeared, caused by hanging (suspended) transactions.

Principles : A transaction is an atomic unit of work that must be fully committed or rolled back. The ACID properties (Atomicity, Consistency, Isolation, Durability) are enforced in InnoDB by lock mechanisms + MVCC for isolation, and by redo/undo logs for the other properties. InnoDB uses shared (S) locks for reads and exclusive (X) locks for writes. Important logs include redo log (physical changes for durability), undo log (logical changes for rollback), and binlog (binary log for replication and point‑in‑time recovery).

Typical transaction flow (update) :

Read data from Buffer Pool or disk.

Write old values to undo log (ensuring atomicity).

Update Buffer Pool.

Write changes to Redo Log Buffer.

Prepare commit – flush Redo Log Buffer to disk (fsync).

Write binlog to disk.

Mark redo log as committed.

Eventually flush Buffer Pool pages to disk.

What is a hanging transaction? If a transaction is started but neither COMMIT nor ROLLBACK is executed, the transaction remains open, holding its locks. Other sessions wait for those locks, eventually hitting the lock‑wait timeout.

Consequences :

Accumulated hanging transactions can lock an entire table, causing system‑wide lock‑wait timeouts.

If the hanging transaction is rolled back, all writes performed during its lifetime are lost.

Normal transaction example :

beginTransaction();</code><code>----一顿操作-------</code><code>if(操作成功) {</code><code>  commit();</code><code>} else {</code><code>  rollback();</code><code>}

Problematic code (missing exception handling leads to a hanging transaction):

//参数校验</code><code>DefaultTransactionDefinition definition = new DefaultTransactionDefinition();</code><code>TransactionStatus status = transactionManager.getTransaction(definition);</code><code>Long quota = jsonObject.getLong("quota");</code><code>transactionManager.commit(status);

Investigation steps :

Checked recent releases for manual getTransaction usage – none found.

Queried MySQL process list to locate long‑running sleeping transactions.

//查询mysql当前的所有进程</code><code>SHOW PROCESSLIST;</code><code></code><code>//查询出执行时间超过10s未提交的事务</code><code>SELECT t.trx_mysql_thread_id,</code><code>       t.trx_state,</code><code>       t.trx_tables_in_use,</code><code>       t.trx_tables_locked,</code><code>       t.trx_query,</code><code>       t.trx_rows_locked,</code><code>       t.trx_rows_modified,</code><code>       t.trx_lock_structs,</code><code>       t.trx_started,</code><code>       t.trx_isolation_level,</code><code>       p.time,</code><code>       p.user,</code><code>       p.host,</code><code>       p.db,</code><code>       p.command</code><code>FROM information_schema.innodb_trx t</code><code>INNER JOIN information_schema.processlist p</code><code>ON t.trx_mysql_thread_id = p.id</code><code>WHERE t.trx_state = 'RUNNING'</code><code>AND p.time > 10</code><code>AND p.command = 'Sleep';

The identified sleeping transactions were killed, temporarily restoring normal operation, but the issue recurred.

Further analysis revealed that Spring’s transaction management relies on ThreadLocal to bind a connection to the current thread. If a transaction is not properly committed or rolled back, the ThreadLocal entry remains, and subsequent requests re‑use the same thread (from a pool), causing new SQL statements to execute within the stale transaction and lock additional rows.

Recommendations :

Avoid manual getTransaction control; prefer @Transactional and always wrap transactional code in try‑catch to guarantee commit or rollback.

Validate all parameter types before conversion to prevent unexpected RuntimeException that aborts transaction finalization.

The article concludes with a brief team introduction.