COMMIT saves your changes permanently. ROLLBACK throws them away as if they never happened. That distinction sounds obvious written out like this, and yet a surprising number of production incidents trace back to someone expecting one of these commands to behave like the other — expecting a ROLLBACK to undo something a COMMIT already locked in, or assuming changes were saved when no COMMIT was ever issued at all.
Transactions are one of those SQL topics that seem simple in theory and cause real damage in practice. Rather than explain the concept top-down, this guide works through it the way you’d actually encounter it: as a series of symptoms, the misunderstanding behind each one, and the specific fix that resolves it.
Symptom: “My changes were there a minute ago, and now they’re gone”
You ran an UPDATE, queried the table again, saw your change reflected — and then, after closing your session or reconnecting, the old data was back as if nothing happened.
Cause: No COMMIT was ever issued. Depending on your client and your database’s default settings, you may have been sitting inside an open transaction the entire time, with your changes visible to you but not yet made permanent. Closing the connection without committing triggered an automatic ROLLBACK, and everything you’d done quietly reverted.
Fix: Get in the habit of ending every transaction explicitly, one way or the other. If the change is correct, run COMMIT. If your client has autocommit enabled by default (many do, including most GUI tools in their default configuration), confirm that setting rather than assuming it. When autocommit is off — common in tools like psql or when a BEGIN statement has been issued manually — nothing is durable until you say so, and that’s a feature, not a bug, once you know to look for it.
Symptom: “I ran a DELETE without a WHERE clause and my stomach dropped”
This is the scenario every SQL writer eventually lives through: a DELETE or UPDATE statement fires against every row in a table because a WHERE clause got left off, forgotten, or commented out by accident.
Cause: This isn’t really a transaction problem — it’s a timing problem. Whether this mistake is recoverable in seconds or catastrophic depends entirely on whether you’re still inside an open transaction when you notice.
Fix: If autocommit is off and you haven’t run COMMIT yet, ROLLBACK undoes the damage completely, as though the statement never executed. If autocommit is on, or you already committed before noticing, ROLLBACK can’t help you anymore — you’re now in restore-from-backup territory. The practical lesson here is procedural: before running any DELETE or UPDATE against a table you care about, confirm you’re inside an explicit transaction (BEGIN first), run the statement, check the result with a SELECT, and only then decide between COMMIT and ROLLBACK. Building that pause into your habits turns a career-defining mistake into a non-event.
Symptom: “Two people updated the same row and one change disappeared”
A common report: two processes update the same account balance around the same time, and instead of both changes applying, the result reflects only one of them — the other silently vanished.
Cause: This is the classic lost update problem, and it happens when two transactions read the same row, calculate a new value based on that read, and write it back without any mechanism preventing them from overwriting each other. Neither transaction did anything wrong in isolation; the collision happens because of how their timing overlapped.
Fix: There are a few valid approaches, and which one fits depends on your workload. Using SELECT FOR UPDATE locks the row for the duration of the transaction, forcing the second process to wait until the first one finishes. Alternatively, raising your isolation level (to SERIALIZABLE, for instance) can prevent this pattern outright, at the cost of more contention under heavy concurrent load. For simple counters or balances, writing the update as an arithmetic operation directly in SQL — “set balance equal to balance minus the amount,” rather than reading the value into application code, subtracting, and writing it back — sidesteps the entire problem, since the database handles the read-and-write as one atomic step.
Symptom: “The database seems frozen, and nobody is running a slow query”
Everything looks fine on paper. No query is taking unusually long. And yet other sessions are stuck waiting, sometimes for minutes at a time, on tables that should be perfectly available.
Cause: Somewhere, a transaction was opened and never closed. Maybe a developer ran BEGIN in a terminal, stepped away, and forgot about it. Maybe an application has a bug where an exception path skips the COMMIT or ROLLBACK it should be calling. Either way, that open transaction is still holding locks on whatever rows it touched, and every other session that needs those same rows just has to wait.
Fix: Most databases expose a way to see currently open transactions and how long they’ve been idle — commands like checking pg_stat_activity in PostgreSQL, or the information_schema.innodb_trx table in MySQL, will show you exactly what’s stuck and for how long. Once you’ve identified the offending session, it can usually be terminated safely, which forces a rollback and releases its locks. Long term, the fix is making sure every code path that opens a transaction has a guaranteed COMMIT or ROLLBACK, including in error-handling branches — a try/finally block, or your language’s equivalent, is the usual mechanism for guaranteeing that.
Symptom: “I ran ROLLBACK and got an error, or it seemed to do nothing”
You typed ROLLBACK, expecting your recent changes to disappear, and either the database complained that there was no transaction to roll back, or the data stayed exactly as it was.
Cause: ROLLBACK can only undo work inside an active, uncommitted transaction. If autocommit already saved your last statement the moment it ran, there is nothing left for ROLLBACK to reverse — the transaction it would have undone already closed on its own. This trips up people coming from a mental model where every statement is provisional until they say otherwise, when in fact many database clients commit each statement immediately by default.
Fix: Check whether you’re operating in autocommit mode before assuming ROLLBACK is available to you as a safety net. If you need that safety net for a batch of statements, start the batch with an explicit BEGIN (or START TRANSACTION), run your statements, and only commit once you’re satisfied — this is the only way to guarantee ROLLBACK has something to work with if you need it.
Symptom: “Some of my inserts went through, and others didn’t”
You ran a script meant to insert five related rows across two or three tables — an order, its line items, an updated inventory count — and after a failure partway through, only some of that data made it in, leaving the database in an inconsistent state.
Cause: Each statement was likely running as its own independent unit, with no transaction tying them together. When the third or fourth statement failed, the ones before it had already committed on their own, and there was nothing in place to undo them.
Fix: Wrap the entire related set of statements in a single transaction: BEGIN, then each INSERT or UPDATE in sequence, then COMMIT only after every statement has succeeded. If any statement in that sequence fails, catch that failure and issue a ROLLBACK instead, which undoes everything done since BEGIN — leaving you with either all of the related changes applied, or none of them, rather than the inconsistent in-between state that caused the original problem. This all-or-nothing guarantee is usually described as atomicity, and it’s the core reason transactions exist in the first place.
Symptom: “A SAVEPOINT rollback undid more than I expected, or less”
You’re using SAVEPOINT to undo one part of a larger transaction without throwing away everything else, and the result didn’t match what you rolled back to.
Cause: A plain ROLLBACK always undoes the entire transaction back to its start, not just your most recent statement — that surprises people who assume it works one step at a time, like an undo button in a text editor. SAVEPOINT exists precisely to solve that, by letting you mark a specific point inside a transaction that you can return to selectively. Confusion usually comes from calling ROLLBACK when ROLLBACK TO SAVEPOINT was needed, or from assuming a savepoint survives past the transaction’s own COMMIT or ROLLBACK, which it does not.
Fix: Inside a transaction, mark a point with SAVEPOINT some_name before a risky statement. If that statement causes a problem, ROLLBACK TO SAVEPOINT some_name undoes everything after that point while keeping everything before it intact, and the transaction stays open so you can continue. Only a full COMMIT or a full ROLLBACK ends the transaction itself; a savepoint rollback just rewinds part of it.
A Quick Reference for These Symptoms
| Symptom | Likely Cause | Fix |
|---|---|---|
| Changes vanish after disconnecting | No COMMIT issued | Always end transactions explicitly |
| Accidental mass DELETE/UPDATE | Statement ran without a transaction wrapper | BEGIN before risky statements, check before COMMIT |
| Updates silently overwrite each other | Lost update from concurrent transactions | SELECT FOR UPDATE, higher isolation, or atomic SQL updates |
| Database seems frozen | Idle open transaction holding locks | Find and terminate it; guarantee COMMIT/ROLLBACK in code |
| ROLLBACK errors or does nothing | Autocommit already closed the transaction | Use explicit BEGIN when you need a safety net |
| Partial multi-table writes | No transaction tying related statements together | Wrap the full sequence in BEGIN…COMMIT |
| SAVEPOINT rollback undoes too much | Confusing ROLLBACK with ROLLBACK TO SAVEPOINT | Use named savepoints for partial undo |
If you’re debugging a transaction-related issue that isn’t covered by one of these symptoms, describe exactly what you expected versus what happened, along with whether autocommit is on — that detail alone resolves most transaction confusion before we even get to isolation levels.