An operator kills a session running a huge UPDATE, and the database stays busy — and keeps blocking other sessions — for a long time afterwards. Why can aborting a transaction be as expensive as running it, and what follows for how you size write transactions?
answer
- abort cost ∝ work already done
- kill session = start rollback, not skip it
- locks held until rollback completes
- restart doesn't help; recovery resumes it
- chunk bulk writes into resumable batches
basics
~20 sRollback is real work: the engine must reverse every change already made, including index maintenance, and the transaction's locks are only released when that finishes. Killing the session starts the rollback; it does not skip it. So keep write transactions bounded and chunk bulk changes.
solid answer
~50 sIn an undo-based engine, rollback physically reverses each change — restoring row before-images and undoing index entries — so its cost is proportional to the work already done, and can exceed it because the undo pages may no longer be cached. The transaction's locks are held until rollback completes, so other sessions stay blocked; cancelling or killing the session only starts the rollback, and restarting the server does not skip it either, since recovery resumes the same work. Engines that keep old row versions in the table behave differently: abort is nearly instant because the aborted versions are simply never made visible. The cost reappears later as bloat that background cleanup must reclaim, and as index entries pointing at dead versions. The design consequence is the same either way: bound the size of write transactions. Chunk large deletes and backfills into batches that commit periodically, so a failure reverses one batch rather than hours of work, locks are released promptly, and progress is retained.
code
sql · 8 lines-- repeat until zero rows affected; each iteration is its own transaction
DELETE FROM events
WHERE id IN (
SELECT id FROM events
WHERE created_at < DATE '2025-01-01'
ORDER BY id
LIMIT 10000
);go deeper
Know that rolling back is real work, not an instant cancel, and that huge write statements are risky.
Explain that undo-based rollback reverses each change and that locks are held until it completes, so other sessions stay blocked.
Handle the incident correctly — do not restart, observe progress, shed load — and design bulk work as chunked resumable batches with an explicit tradeoff against atomicity.
Make it policy: bounded write transactions, resumable maintenance jobs, awareness of where each engine places the cost (abort time versus background reclamation), and capacity planning for cleanup load.
## Why rollback is not free A transaction's changes are already spread across data pages, index pages and possibly the data files on disk. Undoing them means touching all of that again. In an **undo-based** engine, rollback walks the transaction's chain of undo records from newest to oldest and applies each reversal: restore the row's before-image, remove the index entry that was inserted, reinsert the entry that was removed. That is roughly the same number of page modifications as the forward work — and often slower in practice, because: - the pages involved may have been evicted from the buffer pool and must be read back in; - the undo work is itself logged (so that a crash mid-rollback is recoverable), producing more log volume; - it typically runs with far less parallelism than the original statement had. So \"as expensive as running it\" is a floor, not an exaggeration. ## Killing the session does not help This is the operationally important part. Cancelling the statement or terminating the connection does not discard the work — it *initiates* the rollback. The session may disappear from the client's point of view while the server keeps grinding. Nor does restarting the database. On startup, recovery re-establishes that the transaction had no commit record and resumes rolling it back; some engines even make the database available while that proceeds in the background. Impatiently restarting usually makes the outage longer, because the buffer pool is now cold. ## Locks are held until the end Rollback ends the transaction, and locks are released when the transaction ends — not when the statement is cancelled. So every session waiting on a row the aborting transaction touched keeps waiting for the entire rollback. A ten-minute rollback is a ten-minute partial outage for anything contending with those rows, and can cascade: waiters pile up, the connection pool exhausts, unrelated requests start failing. ## The multi-version alternative and where its cost lands Engines that keep old row versions in the table have a very different profile. Aborting means marking the transaction's status as aborted; from then on, visibility rules hide the versions it created and the previous versions remain current. Rollback is close to constant time regardless of size, and locks are released immediately. The cost has moved, not vanished. The aborted versions still occupy space in the table and in every index, and a background cleanup process must reclaim them. A repeatedly aborted bulk update produces bloat, extra I/O for scans that must skip dead versions, and cleanup load that competes with the live workload. So the operational failure mode is space and background pressure rather than a long blocking abort. ## What follows for design **Bound write transactions.** The relevant question is not \"how long does this take when it works\" but \"what does it cost when it fails at 90%\". A single statement that rewrites a hundred million rows has a worst case measured in hours of rollback and blocking. **Chunk bulk work.** Delete or backfill in batches — a bounded number of rows per transaction, committing between batches, driven by a key range or a marker column so the job is resumable. Then a failure reverses one batch, locks are held briefly, and completed work is retained. The tradeoff is that the overall operation is no longer atomic, so the job must be written to be idempotent and restartable; that is usually the right trade for maintenance work, and the wrong one for a business transaction that genuinely must be all-or-nothing. **Keep transactions short in application code.** Long transactions are risky for reasons beyond abort cost — they pin undo or old versions, hold locks, and in some engines retain log — but abort cost is the one that turns a routine error into an incident. **Set expectations before acting.** During an incident, the useful facts are: the rollback cannot be skipped, its progress can usually be observed, restarting will not shorten it, and the fastest safe path is to let it finish while shedding load from the blocked paths. ## Interview framing \"Rollback reverses every change and holds the locks until it is done, so aborting a giant transaction is a giant piece of work you cannot cancel. That is why bulk changes get chunked into small committed batches with a resumable driver.\"
- During an incident, is restarting the database a way to escape a long rollback?No. Recovery on startup identifies the transaction as having no durable commit record and resumes rolling it back, so the same work still has to happen — now with a cold buffer pool, which usually makes it slower. The better response is to let the rollback complete while relieving pressure on the blocked code paths, and to monitor its progress rather than intervening.
- What do you give up by chunking a large delete into batches that each commit?Atomicity of the operation as a whole: after a failure the table is left partially processed, so the job must be idempotent and resumable, and any concurrent reader may see an intermediate state. That is acceptable for maintenance work such as purging old rows, but not for a business operation that must be all-or-nothing, which should instead be kept small enough to run as one transaction.
Unpacking a moving van you have half-loaded: the boxes do not vanish because you changed your mind, and the driveway stays blocked until they are all back in the house.
saying these in an interview costs you the question
- Thinking killing the session or restarting the server cancels the rollback work
- Believing locks are released as soon as the statement is cancelled
- Assuming rollback is always instant because one engine they used makes it so
- Not knowing that in version-based engines the cost reappears as bloat and cleanup load
- Proposing an unbounded single-statement purge of a huge table with no batching