Say you are trying to move money between two accounts in a single operation: subtract 500 from Account A, add 500 to Account B. Two separate UPDATE statements, run one after the other. Now imagine the connection drops, or the server crashes, right after the first statement finishes but before the second one runs. Account A is short 500 dollars. Account B never received it. The money didn’t go anywhere — it just vanished from your data, and no error message in the world will tell you where.
This is the exact problem transactions were built to solve, and it’s worth understanding at two different depths. There’s a beginner version of this concept that gets you through 90% of everyday work, and an advanced version that matters the moment multiple people touch the same rows at the same time. This guide walks through both, and draws a clear line showing where one ends and the other begins.
The Core Idea, Before Any Syntax
A transaction groups multiple SQL statements into a single all-or-nothing unit. Either every statement in that group succeeds and gets saved together, or none of them do. There’s no in-between state where the first UPDATE went through and the second one didn’t — a properly used transaction makes that in-between state impossible.
Two commands sit at the center of this: COMMIT, which saves every change made since the transaction started, and ROLLBACK, which discards every change made since the transaction started, as if none of it ever happened. Everything else in this guide — savepoints, isolation levels, locking — builds on top of that one pairing.
Beginner Level: The Three Commands You Actually Type
At the beginner level, three commands cover the entire concept: BEGIN (or START TRANSACTION, depending on your database), COMMIT, and ROLLBACK.
BEGIN tells the database you’re starting a group of statements that should be treated as one unit. Everything after it — every INSERT, UPDATE, or DELETE — gets held in a kind of pending state rather than being finalized immediately. COMMIT then finalizes all of it at once. ROLLBACK throws all of it away at once. Nothing else needs to be memorized to start using transactions safely in ordinary application code.
Here’s the money-transfer example properly wrapped:
BEGIN; subtract 500 from Account A’s balance; add 500 to Account B’s balance; COMMIT.
If the connection drops between the two UPDATE statements, nothing gets committed, and the whole pending set of changes rolls back automatically once the database notices the broken connection. Account A never actually loses its 500 dollars in any way that survives past the failure. That’s the entire guarantee, and it’s the reason multi-statement operations that must succeed or fail together should never be run outside a transaction.
Beginner Level: When ROLLBACK Gets Triggered Manually
COMMIT and automatic rollback-on-failure cover a lot of ground, but beginners often miss the third case: rolling back on purpose, based on application logic rather than a crash.
Picture a transaction that checks Account A’s balance before subtracting anything. If the balance is too low, the application shouldn’t commit the subtraction at all — it should issue an explicit ROLLBACK and cancel the whole transaction, leaving both accounts untouched. This is normal, expected behavior, not an error state. Most application code wraps a transaction in a try/catch-style structure specifically so that any failed validation check, any thrown exception, or any unmet business rule triggers a ROLLBACK instead of accidentally falling through to COMMIT.
A rule worth internalizing early: never structure code so that COMMIT is the default outcome and ROLLBACK only happens if you remember to add it. Structure it the other way — ROLLBACK by default, COMMIT only once every check has passed.
Advanced Level: Savepoints for Partial Rollbacks
Beginner-level transactions treat ROLLBACK as all-or-nothing: undo everything since BEGIN, no exceptions. Savepoints introduce a middle option.
A savepoint marks a checkpoint partway through a transaction. SAVEPOINT before_step_three, for instance, names a specific point in the sequence. If something goes wrong later in that same transaction, ROLLBACK TO before_step_three undoes only the statements run after that savepoint, leaving everything before it intact and still pending inside the transaction. The transaction itself stays open — you haven’t rolled back to the very beginning, just to that named checkpoint.
This matters in longer transactions with several independent stages, where failing at stage four shouldn’t necessarily throw away the work done in stages one through three. A batch import that processes records in groups might set a savepoint before each group, so a bad record in group four rolls back only group four instead of the entire import. Beginner-level ROLLBACK can’t express that distinction; savepoints exist specifically to add it.
Advanced Level: Isolation Levels and Why They Exist
A beginner transaction assumes it’s the only thing touching the database at that moment. Production systems rarely offer that luxury — dozens or thousands of transactions can be running concurrently, sometimes against the exact same rows.
Isolation levels control how much one in-progress transaction can see of another in-progress transaction’s uncommitted changes. READ UNCOMMITTED, the loosest level, lets a transaction see another transaction’s changes before they’re even committed — a phenomenon called a dirty read, and one that most applications should avoid entirely. READ COMMITTED, the default in many database systems, only lets a transaction see changes that have already been committed elsewhere, which closes off dirty reads but still permits a couple of subtler inconsistencies across repeated queries within the same transaction. REPEATABLE READ and SERIALIZABLE tighten things further, each one trading some concurrency performance for a stronger guarantee that your transaction sees a consistent snapshot of the data throughout its lifetime.
None of this comes up when you’re writing a simple two-statement transfer and testing it alone on a laptop. It comes up the moment two customers try to book the last seat on a flight within the same second, and the database has to decide which transaction gets to see the seat as available.
Advanced Level: Locking and Deadlocks
Isolation levels describe what a transaction is allowed to see. Locking describes what a transaction is allowed to touch while another transaction is mid-flight.
When a transaction updates a row, most databases lock that row until the transaction commits or rolls back, preventing a second transaction from modifying — sometimes even reading — the same row in the meantime. This is what keeps two concurrent transfers from both reading Account A’s balance, both calculating a valid-looking subtraction, and both committing in a way that leaves the balance wrong.
Locking introduces its own failure mode: the deadlock. Transaction one locks Row A and then waits to lock Row B. Transaction two, running at nearly the same moment, has already locked Row B and is now waiting to lock Row A. Neither can proceed, and neither will ever release its lock, because each is waiting on the other. Databases detect this pattern and resolve it by forcibly rolling back one of the two transactions, which is why production code touching multiple rows across concurrent transactions typically needs retry logic — catch the deadlock error, roll back cleanly, and try the whole transaction again a moment later.
Beginner vs Advanced: Side-by-Side
| Concept | Beginner Level | Advanced Level |
|---|---|---|
| Core commands | BEGIN, COMMIT, ROLLBACK | Same commands, plus SAVEPOINT, ROLLBACK TO |
| Rollback granularity | All-or-nothing, back to BEGIN | Partial, back to a named savepoint |
| Assumption about concurrency | Transaction runs alone | Many transactions run at once against shared rows |
| Main risk being managed | Partial writes from a crash or bug | Dirty reads, lost updates, deadlocks |
| Typical fix | Wrap related statements in BEGIN/COMMIT | Choose an isolation level, add retry logic for deadlocks |
| Where it shows up | Any multi-statement write operation | High-traffic tables, financial systems, booking systems |
Which Level Actually Applies to Your Query
If you’re writing a script that inserts a handful of related rows, or an application feature that updates two or three tables together, the beginner model is the whole answer: wrap it in BEGIN and COMMIT, roll back on any failure, and move on. Most transactions written in most applications never need a savepoint or a non-default isolation level.
The advanced layer earns its place once concurrency enters the picture — once you can no longer assume your transaction is the only one running against those rows at that moment. Payment processing, inventory counts, seat or ticket reservations, anything with a limited shared resource that multiple users might grab at once: that’s the territory where isolation levels and deadlock handling stop being optional reading and start being the difference between a system that holds up under load and one that quietly corrupts its own numbers on a busy afternoon.
Start with COMMIT and ROLLBACK. Add the rest only once your data has a reason to need it — and by the time it does, you’ll already recognize exactly which piece is missing.