In this lesson, you will learn how to identify and terminate runaway transactions in MySQL and MariaDB to prevent resource exhaustion and blocking.
The Danger of "Zombies"
In a high-concurrency database, a single transaction that hangs—perhaps due to poor application logic, an uncommitted update, or a network timeout—can hold row or table locks indefinitely. This causes other queries to queue up behind it, leading to a "cascading failure" where your entire application grinds to a halt while waiting for locks to be released.
Identifying the Culprit
To find these transactions, you should query the information_schema.innodb_trx table. This system table provides a real-time snapshot of every transaction currently executing within the InnoDB storage engine.
You can run the following query to identify transactions that have been running for longer than 60 seconds:
SELECT
trx_id,
trx_mysql_thread_id,
trx_started,
trx_query
FROM information_schema.innodb_trx
WHERE trx_started < NOW() - INTERVAL 60 SECOND;
This output tells you exactly which thread ID is involved and what the last executed query was. If you see a trx_query that is a long-running report or a stuck update, you have identified your bottleneck.
Terminating the Process
Once you have identified the thread ID (trx_mysql_thread_id) responsible for the block, you can safely terminate it using the KILL command. This forces the server to roll back the transaction and release any locks it was holding:
KILL 12345; -- Replace 12345 with the trx_mysql_thread_id found above
Proactive Management
Rather than waiting for a system outage, consider setting the innodb_lock_wait_timeout variable to a reasonable value (like 50 seconds). This prevents transactions from waiting indefinitely for a lock. Furthermore, in production environments, many DBAs implement an automated script or a monitoring tool that periodically checks innodb_trx and alerts—or even auto-kills—transactions that exceed a specific duration threshold. By cleaning up these runaway processes early, you preserve the availability and performance of your cluster.
Key Takeaway: Monitor information_schema.innodb_trx to identify stale transactions, and use the KILL command to immediately release locks held by runaway queries before they trigger widespread system contention.