Almost every MySQL locking incident I have written up shares one root cause: a transaction that stayed open far longer than anyone intended. Long transactions block DDL through metadata locks, hold row locks that pile up waiters, pin undo history so purge falls behind, and inflate the undo tablespaces. The transaction itself is usually invisible, a connection in Sleep state doing nothing, which is exactly why it survives long enough to hurt.
The good news is that MySQL exposes everything needed to catch these early. These are my field notes on detecting long and idle transactions on MySQL 8.0 and 8.4, deciding what is safe to kill, and fixing the application patterns that create them.
Why an open transaction is never free
An open InnoDB transaction costs the system in four distinct ways, and it pays them even if it executes nothing. First, any row locks it took remain held until commit or rollback, so waiters accumulate behind it. Second, any table it touched keeps a metadata lock until the transaction ends, which is how one sleeping session blocks an ALTER and then the whole table; I walked through that failure in detail in MySQL online DDL vs PostgreSQL DDL locks. Third, its read view forces InnoDB to keep old row versions in undo for consistent reads, so the purge thread cannot clean up anything newer than the oldest open transaction, and the history list grows. Fourth, on a busy system that history growth translates into slower reads of hot rows, because queries walk longer undo chains to reconstruct the version they are allowed to see, and into undo tablespace growth that does not shrink promptly.
None of these costs show up as CPU. They show up later, as an incident that looks unrelated.
The one query to run first
information_schema.innodb_trx lists every open InnoDB transaction with its age, state, and footprint. This is the first thing I look at on any locking or purge complaint:
SELECT trx_mysql_thread_id AS pid,
trx_state,
trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age_seconds,
trx_rows_modified,
trx_rows_locked,
trx_isolation_level,
LEFT(COALESCE(trx_query, ''), 120) AS current_query
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 20;Read it from the top: the oldest transaction is the one pinning purge and the most likely MDL holder. The trx_state column distinguishes RUNNING from LOCK WAIT, and trx_rows_modified tells you how expensive a rollback would be. A NULL or empty current_query on an old transaction is the classic signature: the session is idle between statements while holding everything it accumulated.
Catch idle-in-transaction sessions specifically
"Old transaction" and "idle transaction" overlap but are not the same, and the idle ones are almost always bugs. Join innodb_trx to the processlist and keep only sessions that are asleep while a transaction is open:
SELECT t.trx_mysql_thread_id AS pid,
p.user,
p.host,
p.db,
p.time AS idle_seconds,
TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS trx_age_seconds,
t.trx_rows_modified
FROM information_schema.innodb_trx t
JOIN information_schema.processlist p
ON p.id = t.trx_mysql_thread_id
WHERE p.command = 'Sleep'
AND t.trx_started < NOW() - INTERVAL 60 SECOND
ORDER BY trx_age_seconds DESC;The user and host columns are the accountability trail: they tell you which service and which pool owns the leak. In my experience the same handful of sources appear every time. A pool that turns off autocommit and hands out connections mid-transaction. An ORM session opened for a read path that never commits because "it only reads". A worker that opens a transaction, calls an external API that hangs, and holds its locks for the duration of someone else's timeout. A shell session where a human typed START TRANSACTION before lunch. The query above finds all of them; only the fixes differ.
Two implementation notes. On current 8.0 and 8.4 releases, prefer performance_schema.processlist over the information_schema version, which is deprecated; the columns used here are the same. And run these checks as a monitoring user with the PROCESS privilege, otherwise the processlist only shows that user's own sessions and the join silently hides exactly the leaked connections you are hunting.
Watch purge lag alongside this. The history list length is the count of undo log units not yet purged, and a sustained climb means some transaction is pinning history:
SELECT name, count
FROM information_schema.innodb_metrics
WHERE name = 'trx_rseg_history_len';There is no universal healthy number, it depends on write rate, so trend it. Flat or sawtooth is fine; monotonic growth over hours tracks back to the oldest row in innodb_trx nearly every time.
Decide what to kill, and how
Killing is a rollback, not an abort, and that changes the calculus. For an idle read-only transaction, KILL is free: nothing to roll back, locks and the read view release instantly, purge resumes. For a transaction with large trx_rows_modified, rollback can take as long as the original work or longer, and the locks stay held throughout, so killing a huge batch UPDATE mid-flight can extend the incident rather than end it. Check trx_rows_modified before every kill and make the big ones a deliberate decision.
The mechanics: KILL 8123 terminates the connection and rolls back its transaction; KILL QUERY 8123 stops only the running statement and leaves the transaction open, which is rarely what you want for this problem. For blocked-DDL incidents, sys.schema_table_lock_waits and sys.innodb_lock_waits both generate the exact kill statement for the blocker, and the deadlock-adjacent judgment calls are the same ones I covered in deadlock diagnosis in MySQL vs PostgreSQL.
For standing policy rather than incident response, keep two backstops. Set wait_timeout so abandoned connections eventually die on their own; the default 28800 seconds, eight hours, is far too generous for OLTP, and something in the range of a few minutes to an hour fits most services fronted by a pool. And run a periodic sweep, whether pt-kill, an event-scheduler job, or a small cron script, that kills idle-in-transaction sessions past a threshold your teams have agreed to, with an allowlist for known long batch users. The agreement matters more than the tool: a kill policy nobody knows about becomes its own incident.
Fix the leak, not just the symptom
Every killed transaction should produce a follow-up ticket, because the leak will reoffend. The fixes are boringly consistent. Make pools return connections in autocommit mode and verify the pool's reset-on-return behavior actually runs. Keep transactions wrapped around the database work only, never around HTTP calls, queue publishes, or file I/O. Give read-only paths explicit short transactions or plain autocommit statements instead of a long-lived session. Add a transaction age check to the service's own health reporting so the owning team sees the leak before the DBA does. And in code review, treat "START TRANSACTION" and "COMMIT" in different functions as a smell worth flagging.
Alert on transaction age continuously
All of the queries above are snapshots, and long-transaction incidents build slowly, which makes them ideal for alerting. Two thresholds cover it: warn when the oldest open transaction crosses something like five minutes on an OLTP system, and page when it crosses the point where your DDL windows and purge lag actually suffer. Pair that with a history-list-length trend and an idle-in-transaction count, and you will catch nearly every incident in this article while it is still a Slack message instead of an outage. These signals belong in the standing MySQL monitoring baseline next to replication lag and slow queries, sampled every few seconds, with history kept for the postmortem.
Where MonPG fits
Being direct about the tooling situation: MonPG is a PostgreSQL monitoring platform today, and it does not monitor MySQL yet. MySQL support is being built right now, and long-transaction detection, oldest-transaction age, idle-in-transaction attribution by user and host, and purge-lag trending are core signals in that work; the MySQL monitoring (coming soon) page is where availability will be announced. If your team also runs PostgreSQL, the same problem exists there as idle-in-transaction sessions holding back vacuum, and MonPG already handles that end to end, so you can start there while MySQL support ships.