The page that taught me this came in at 01:55 on a Sunday: replication lag climbing on both read replicas, p99 latency on the primary tripled, and one session in the processlist running an UPDATE with a missing WHERE clause that had been going for 26 minutes. A support engineer had fat-fingered a data fix in the MySQL CLI — UPDATE subscriptions SET status = 'paused' on a table with 60 million rows, no predicate. I did what every DBA does: found the thread id, ran KILL, and waited for relief. The session vanished from the processlist within seconds. The server did not recover. CPU stayed pinned, the redo log kept churning, and the replicas kept falling behind for another two and a half hours, because the transaction I had just killed was now rolling back forty-one million modified rows — and rollback in InnoDB is work, real work, frequently more expensive than the forward progress that produced it.
Nothing about that night was exotic, and that is the point. KILL feels like a cancel button and behaves like a polite suggestion followed by a cleanup job you cannot cancel. Here is what the command actually does, where the kill flag gets checked, how to watch a rollback grind forward, why rebooting the box trades one kind of pain for a worse one, and the discipline that means you never get paged by this twice.
What does KILL actually do to a running query?
It sets a flag on the session and, for a few specific wait states, wakes the thread up — nothing more. MySQL is cooperative here: the executing thread checks the killed flag at defined points in the execution path, not continuously. Between row loops in a large UPDATE it checks fairly often, which is why most killed queries die within seconds. But a statement parked inside an InnoDB lock wait, deep in filesort I/O, or blocked on disk may not look at the flag until the current operation completes, and some operations never check at all. KILL CONNECTION and KILL QUERY set different flags — one terminates the session after the statement unwinds, the other lets the connection live — but both share the same mechanism: a request, not a preempt.
The piece nobody reads until 02:00 is what happens next for a write. The moment the flag is noticed, the statement aborts and the transaction must roll back. InnoDB has already modified millions of rows, logged redo for all of them, and built an undo chain long enough to reconstruct every before-image. Rollback walks that chain in reverse, applying before-images row by row, generating new redo as it goes. The transaction is not gone; it has flipped direction, and it holds its locks until the walk finishes. That is why the processlist entry disappears — the client connection is dead — while information_schema.INNODB_TRX still shows the transaction, now with trx_state set to ROLLING BACK, doing its penance in the background. If you have read the history list and purge lag notes, you already know undo records do not vanish at commit; rollback is the same machinery running backwards under your feet.
How do you measure rollback progress instead of guessing?
SHOW ENGINE INNODB STATUS exposes the only reliable progress meter: the undo log entries counter on the rolling-back transaction, which counts down as before-images are applied. A single look tells you nothing; two looks thirty seconds apart give you a rate and an honest ETA. The relevant lines in the TRANSACTIONS section look like this while it grinds:
---TRANSACTION 42811603, ACTIVE 9187 sec rollback
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 18740233
MySQL thread id 88214, OS thread handle 281472993890112, query id 0
-- pair it with the dictionary view for the same story in row form
SELECT trx_id, trx_state, trx_started,
trx_rows_modified, trx_rows_locked,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds
FROM information_schema.INNODB_TRX
ORDER BY trx_started;Take a sample, wait thirty seconds, take another. In my incident the undo counter was falling by roughly 120,000 entries a minute against 18.7 million remaining, which put the ETA around two and a half hours — and the real finish landed within ten minutes of that estimate, because rollback rate on a quiet-otherwise system is remarkably steady. Two things the rate tells you beyond the ETA. First, whether anything is competing: if the rate collapses, some other workload is fighting for the same pages or the rollback is waiting on a lock held by a live transaction, and you have a second problem. Second, whether killing was even net-positive: if the original statement had nine-tenths of its rows already modified when you killed it, the rollback will often take longer than letting it finish would have. That arithmetic is cold comfort at 02:00, but it should inform the next decision, not just the postmortem.
Why is restarting the server the worst available option?
Because InnoDB crash recovery performs the same rollback with extra steps, and now the whole instance is down while it happens. When the server comes back up it scans the redo log, rolls forward committed changes, and then rolls back every transaction that was uncommitted at crash time — including your monster. There is no shortcut flag, no "abandon this transaction" switch. The startup log prints the queue depth honestly, something like InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 18740233 row operations to undo, and then it rolls back with the same single-transaction-at-a-time machinery, before the server accepts connections in some configurations and alongside them in others. I have watched a colleague reboot a primary mid-rollback expecting the transaction to evaporate; the box spent forty minutes in recovery doing exactly what it had been doing before, except now the application was down and the status page was red. The only escape hatch, innodb_force_recovery, skips rollback at levels 3 and above by refusing to run transactions at all — which is a corruption-triage tool for getting a logical dump off a dying instance, not an operational shortcut, and the force recovery field notes cover why it is a one-way door to a rebuild. Sit on your hands and watch the counter instead.
Which query shapes produce the worst rollbacks?
Unbounded bulk writes are the obvious one, but the nastiest surprises come from statements whose rollback cost is invisible until you are inside it. A DELETE with a broad predicate is the classic: it delete-marks every matched row, so rollback must un-mark all of them. An UPDATE that touches indexed columns rewrites secondary index entries, inflating both the forward work and the undo. Multi-table UPDATEs and INSERT ... SELECT hide their blast radius behind a single line in the processlist. DDL-adjacent surprises exist too: an ALTER that you kill mid-copy (the old COPY algorithm, still the fallback for plenty of operations) leaves a temp table and a metadata mess even though there is no row rollback to speak of, which is a different cleanup with its own sharp edges — the metadata lock notes cover the lock side of that mess. And batch jobs that run one enormous transaction instead of committing in chunks are a self-inflicted version of the same exposure: kill the job at hour three and you have manufactured a three-hour rollback on purpose.
The defensive patterns are boring and they work. Every hand-run data fix gets wrapped in a counted loop — DELETE ... LIMIT 5000 in a shell loop, commit between iterations, verify ROW_COUNT() each pass — so the worst case of any kill is five thousand rows of rollback, not forty million. Application bulk jobs chunk by primary key range and checkpoint progress, so a kill is a resume point rather than a disaster. SELECTs that run away get max_execution_time so the server kills them on a budget instead of an operator killing them in a panic; note that it only governs read-only SELECTs, which is precisely the class where killing is cheap. And anything mutating more rows than you can roll back in your patience window gets rehearsed on a replica clone first, where you can measure rows-modified per minute before you bet the primary on it. The long transaction detection setup matters here too: an alert on transactions older than a few minutes catches the runaway at minute four, when killing it costs seconds, instead of minute twenty-six, when killing it costs your Sunday.
What should you actually do while the rollback runs?
Protect the rest of the workload and communicate an ETA — those are the only two levers left. The rolling-back transaction holds its locks, so anything queuing behind those locks will pile up; if the pile blocks user-facing paths, you may need to fail traffic to a replica or shed the blocked feature rather than let lock waits cascade into the connection pool. Replicas will show lag because rollback generates redo that ships and applies downstream, so expect monitoring to scream about replication for exactly as long as the counter takes to reach zero; annotate the alert, do not chase it. Keep sampling the undo counter and publish the ETA to whoever is asking — a confident "back to normal at 04:40" defuses the incident channel better than any heroic action. Resist the temptation to run anything heavy against the affected tables "to check on them"; you will only add lock contention to a server that is already spending its I/O budget on penance. Then, when the counter hits zero and the locks release, write down two numbers: how many rows rolled back, and how long it took per million. Those numbers calibrate the next decision about whether to kill or let finish, and they turn the postmortem from blame into arithmetic.
Where MonPG stands on MySQL
I build MonPG, so to be plain: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. The signals in this piece — a transaction in ROLLING BACK state as a first-class status, undo log entries graphed as a countdown, lock waits piling up behind the rolling transaction, replica lag annotated against the same timeline — are exactly what the MySQL work is designed to surface, so a rollback storm reads as a chart with an ETA instead of a mystery at 02:00. The MySQL monitoring (coming soon) page tracks that work as it lands. Until it ships, the same evidence-first approach runs on the PostgreSQL side today, and the rest of these MySQL field notes live on the blog.