Database transactions
A Socratic walk-through of database transactions — reasoned out one step at a time, not lectured.
The question we started with
THE QUESTION #How can a database keep records consistent when many users change them at once?
Two people transfer money from the same account in the same instant. Each reads a balance of 100, each subtracts 60, each writes the result. Both operations were individually correct, and the account is now wrong. So consider: where exactly did the error enter? Not in either instruction — in the gap between reading and writing. That gap is what a transaction exists to close.
Reasoning it through
REASONING #What would the database have to promise to make that impossible? First, that a group of steps counts as one indivisible act: if the debit succeeds and the credit fails, neither may stand — which requires a record, written before the change, of how to undo it. Second, that the promise survives power loss, which needs the new value kept as well. Databases resolve both with a write-ahead log: the intention is appended to a log and forced to disk before the data pages are touched. Commit then means only "the log record is safely down", which is why committing is fast even while the pages are still in memory.
Third, and hardest: that concurrent transactions do not see each other's unfinished work. Suppose you read a value another transaction has written but not committed, and it then rolls back. What did you read? A number that never existed. Two broad strategies prevent it. One is locking: before touching a row you take a lock and hold it until you commit, so nobody can slip into your gap. The other is multi-version concurrency control, where a write creates a new version and each transaction reads the snapshot that existed when it began, so readers never block writers. Locking makes waiting, versioning makes garbage. And because isolation is expensive, databases sell it in grades — read committed, repeatable read, serializable — with most running below the top grade by default.
The analogy
THE ANALOGY #Think of a shared ledger kept in a room with one pencil. To make an entry you take the pencil, and while you hold it nobody else may write. You first jot your intended change on a slip in the doorway — so if you collapse mid-entry, someone can finish or erase your work — and only then touch the ledger, keeping the pencil until every line is done.
One pencil means one writer at a time, and a real database's whole difficulty is letting thousands write at once. Locks are per-row, not per-ledger; and under multi-version concurrency control there is no pencil to take at all — readers simply consult an older photograph of the page while a writer works on the new one.
Clarifying the model
THE MODEL #The misconception worth correcting is that "the database handles it" automatically. It handles it only inside the boundary you declare. Two statements sent separately are two transactions, and the gap between them is yours to defend — which is why the classic fix for the account above is not to read-then-write at all, but to say UPDATE accounts SET balance = balance - 60, letting the database perform the read and the write as one act under one lock.
A picture of it
THE PICTURE #How to readTime runs top to bottom, and the outer two participants are rival transactions competing for one row. The decisive moment is the second note: B's read is made to wait, so the dangerous gap between A's read and A's write never opens. The log participant shows why the promise survives a crash — the change is written to the log before it is applied, and the commit completes only once that log is forced to disk.
What became clearer
WHAT CLEARED #A transaction is a declared boundary. Inside it, a set of changes behaves as one act that either wholly happens or wholly does not, is durable once committed, and is shielded from other transactions' partial work — to a degree you select. Concurrency is made safe not by careful instructions, but by drawing that boundary in the right place.
Where to go next
ONWARD #- Why deadlock is unavoidable under locking, and how databases detect and break it.
- What snapshot isolation still permits (write skew) that true serializability does not.
- How the same reasoning stretches across machines, and why distributed commit is so much harder.
Key terms
TERMS #| Term | What it means |
|---|---|
| Write-ahead log | a sequential record of intended changes, forced to disk before the data itself, enabling undo and redo after a crash. |
| Dirty read | reading data written by a transaction that has not committed and may still roll back. |
| Two-phase locking | acquiring locks as needed and releasing none until the transaction ends. |
| MVCC | multi-version concurrency control — keeping several versions of a row so readers see a consistent snapshot without blocking writers. |
Every term the collection defines is gathered in the glossary.