Chapter 26: Transaction Management — ACID Properties and Keeping Data Consistent

Where you are: Part IV, Chapter 26 of 40. Chapters 23–25 were about speed. This chapter and the next are about correctness when many things happen at once — the property (Chapter 1) that most distinguishes a database from a spreadsheet. Transactions are how databases stay correct under concurrency and failure.

Learning paths: 💻 🏗️ 🔬 especially; 📊 analysts benefit from understanding why reads can or can't see in-flight changes.

The problem: the world doesn't pause

A database serves many users and programs simultaneously, and machines crash at the worst moments. Two of the five problems from Chapter 1 — concurrency and durability — are about staying correct when the world doesn't politely take turns or stay powered on. Transactions are the mechanism that delivers both.

A transaction is a unit of work that is treated as a single, indivisible operation — it either happens completely or not at all. You met the syntax in Chapter 13:

BEGIN;                                 -- start a transaction
UPDATE accounts SET balance = balance - 100 WHERE id = 1;   -- debit
UPDATE accounts SET balance = balance + 100 WHERE id = 2;   -- credit
COMMIT;                                -- make it permanent (or ROLLBACK to undo all of it)

The promise: those two updates happen together or not at all. The system can never be left in a state where the money left account 1 but never arrived in account 2 — even if the server crashes between the two statements. That promise is formalized as ACID.

ACID: the four guarantees

A — Atomicity. A transaction is all-or-nothing. Every statement between BEGIN and COMMIT succeeds together, or the whole thing is rolled back as if it never happened. The money-transfer above can't half-happen. If anything fails (an error, a crash, a ROLLBACK), all its changes are undone.

C — Consistency. A transaction moves the database from one valid state to another — all constraints (Chapter 3) hold before and after. If a transaction would violate a CHECK, a foreign key, or a unique constraint, it's rejected (rolled back), never partially applied. The database's rules are never broken by a committed transaction.

I — Isolation. Concurrent transactions don't interfere with each other; each runs as if it were alone (to a degree set by the isolation level, below). Your transaction shouldn't see another's half-finished work. This is the subtle one, and most of the chapter.

D — Durability. Once a transaction COMMITs, its changes survive anything — power loss, crash, the building losing electricity one millisecond later. PostgreSQL guarantees this with the write-ahead log (Chapter 28): it records what it's about to do, durably on disk, before changing the data, so a crash can be recovered.

Why this matters. ACID is the contract that lets you trust a database with money, medical records, and inventory. The Chapter 13 habit — wrap risky changes in a transaction so you can ROLLBACK — is atomicity used as an undo button. Here we see the full guarantee: not just "I can undo," but "the system is never in a broken or half-finished state, even across crashes and concurrent users."

Atomicity in action, and savepoints

The classic example is a transfer (above): debit and credit must both happen or neither. Without a transaction, a crash between them loses money. With one, the WAL ensures recovery to a consistent state.

For finer control, savepoints let you roll back part of a transaction:

BEGIN;
INSERT INTO orders (...) VALUES (...);
SAVEPOINT after_order;
INSERT INTO order_items (...) VALUES (...);   -- suppose this fails validation
ROLLBACK TO SAVEPOINT after_order;            -- undo just the item, keep the order
INSERT INTO order_items (...) VALUES (...);    -- try again
COMMIT;

Savepoints are useful for "try this; if it fails, undo just this part and continue" within a larger transaction.

Isolation: the hard part

If transactions ran one at a time, isolation would be trivial. They don't — they overlap, and overlapping transactions can interfere in subtle ways. The SQL standard defines several phenomena (anomalies) that can occur, and isolation levels that prevent more or fewer of them (trading correctness guarantees for concurrency/performance).

The phenomena

Dirty read — reading another transaction's uncommitted changes (which might be rolled back). You read data that "never really existed."

   T1: UPDATE balance = 0  (not committed)
   T2:                       SELECT balance → reads 0   ← dirty! T1 might ROLLBACK
   T1: ROLLBACK             (the 0 was never real)

Non-repeatable read — reading the same row twice in one transaction and getting different values, because another transaction committed a change in between.

   T1: SELECT balance → 100
   T2:                    UPDATE balance = 50; COMMIT
   T1: SELECT balance → 50   ← same query, different answer within T1

Phantom read — re-running the same range query and getting different rows, because another transaction inserted/deleted rows matching the condition.

   T1: SELECT count(*) WHERE status='pending' → 5
   T2:                    INSERT a pending order; COMMIT
   T1: SELECT count(*) WHERE status='pending' → 6   ← a "phantom" row appeared

(There's also the lost update and serialization anomaly — more in Chapter 27.)

The isolation levels

PostgreSQL supports these levels, each preventing more phenomena:

Set the level per transaction:

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ...
COMMIT;

Key facts about PostgreSQL specifically:

• PostgreSQL's default is Read Committed. Each statement sees a snapshot of data committed before that statement began. (It never allows dirty reads — PostgreSQL doesn't really implement Read Uncommitted; it behaves like Read Committed.)
• Repeatable Read in PostgreSQL gives each transaction a single consistent snapshot for its whole duration — and (stronger than the standard requires) also prevents phantom reads.
• Serializable guarantees the result is as if transactions ran one after another (serially) — the strongest correctness. It can abort transactions with a serialization failure that the application must retry.

Higher isolation = stronger guarantees but more conflicts/retries and (sometimes) less concurrency. Read Committed (the default) is right for most workloads; step up to Repeatable Read or Serializable when a transaction needs a stable view or true serializability (financial calculations, inventory checks).

MVCC: how PostgreSQL isolates without blocking readers

How does PostgreSQL let many transactions read and write concurrently without them stepping on each other? Multi-Version Concurrency Control (MVCC). Instead of locking rows for readers, PostgreSQL keeps multiple versions of a row: when a transaction updates a row, it creates a new version and marks the old one as still-visible-to-transactions-that-started-earlier. Each transaction sees a snapshot consistent with its isolation level.

The crucial consequence: readers never block writers, and writers never block readers. A long-running SELECT doesn't freeze out UPDATEs, and vice versa — each sees its own consistent snapshot. (Two transactions writing the same row still conflict — that's Chapter 27.) This is why PostgreSQL handles mixed read/write workloads so gracefully.

MVCC's cost is that old row versions ("dead tuples") accumulate and must be cleaned up — that's what VACUUM does (Chapter 28). The trade is well worth it: high concurrency without read locks.

Theory → Practice. MVCC is why the isolation levels behave as they do in PostgreSQL: each level is a rule about which snapshot (and which versions) your transaction sees. Read Committed = a fresh snapshot per statement; Repeatable Read = one snapshot for the whole transaction. Understanding "you see a snapshot" demystifies the whole isolation table.

ACID, property by property

ACID is one of the most-cited acronyms in databases, and understanding each property deeply — not just its name but what it guarantees, how it's achieved, and what fails without it — is what lets you reason about correctness under concurrency and failure. Each property addresses a specific way data could go wrong.

Atomicity is the all-or-nothing guarantee, and its purpose is to make multi-step operations safe against partial completion. The canonical example — debit one account, credit another — shows the danger: if the operation could half-complete (debit succeeds, credit fails or a crash intervenes), money vanishes. Atomicity guarantees that every statement between BEGIN and COMMIT either all takes effect or none does, so there's no "half-transferred" state ever, even if the server loses power mid-transaction. The mechanism is that PostgreSQL doesn't make changes visible until COMMIT, and can undo everything on ROLLBACK or recover to a clean state after a crash (via the WAL). Atomicity is what makes the Chapter 13 "wrap risky changes so you can undo them" habit work — but more deeply, it guarantees the database is never left in a partially-applied state, which is the foundation of trusting it with operations that must be indivisible.

Consistency guarantees that a transaction moves the database from one valid state to another, where "valid" means all constraints hold (Chapter 3's CHECK, foreign keys, unique constraints, and the data rules they encode). If a transaction would leave the database violating a constraint — an order referencing a nonexistent customer, a negative balance where CHECK forbids it — the transaction is rejected and rolled back, never partially applied. Consistency is where ACID connects to the integrity work of Part III: the constraints you designed are enforced transactionally, so no committed transaction can break them. Isolation guarantees that concurrent transactions don't interfere — each runs as if alone, to the degree set by its isolation level. This is the subtle property (most of this chapter), because "as if alone" admits degrees: the isolation level determines exactly which kinds of interference are prevented. Durability guarantees that once a transaction commits, its changes survive anything — power loss, crash, hardware failure a millisecond after the commit returns. PostgreSQL achieves this with the write-ahead log (Chapter 28): it writes a durable record of the change to disk before reporting the commit as successful, so a crash can be recovered by replaying the log. Durability is what lets "the system told me it saved" actually mean "it's saved, permanently."

Together, these four properties are the contract that makes a database trustworthy for data that matters — money, medical records, inventory, anything where a half-applied operation, a broken constraint, a concurrency glitch, or a lost commit would be unacceptable. This is the deepest answer to Chapter 1's "why a database, not a spreadsheet": the spreadsheet offers none of these guarantees, while ACID provides all four, transactionally, for every operation. When you understand ACID property by property — what each guarantees, what goes wrong without it, how PostgreSQL delivers it — you understand precisely why databases can be trusted with critical data in a way no file or spreadsheet can. That trust, formalized as ACID and delivered by the transaction mechanism, is the relational database's defining strength.

The isolation phenomena and levels, in depth

Isolation is the ACID property that admits degrees, and understanding those degrees requires understanding the phenomena (the kinds of interference between concurrent transactions) and the levels (which phenomena each prevents). This is genuinely subtle, but it's the heart of correctness under concurrency, so it rewards careful study.

The phenomena are specific ways one transaction can be affected by another's concurrent activity. A dirty read is reading another transaction's uncommitted changes — data that might be rolled back and thus "never really existed." Reading a balance that a not-yet-committed transaction set to zero, when that transaction will roll back, means you acted on a phantom value. A non-repeatable read is reading the same row twice in one transaction and getting different values, because another transaction committed a change between your two reads — your transaction's view of that row is unstable. A phantom read is re-running the same range query and getting different rows, because another transaction inserted or deleted rows matching your condition between your two queries — the set of matching rows changed under you. Each phenomenon is a different way concurrency can make your transaction's view inconsistent, and each is prevented by a sufficiently strong isolation level.

The isolation levels prevent progressively more phenomena, at progressively higher cost. Read Uncommitted (which PostgreSQL doesn't truly implement — it behaves as Read Committed) would allow all phenomena including dirty reads. Read Committed, PostgreSQL's default, prevents dirty reads (you only ever see committed data) but allows non-repeatable and phantom reads — each statement sees a fresh snapshot of committed data, so two statements in one transaction can see different states. Repeatable Read prevents dirty and non-repeatable reads by giving the whole transaction a single consistent snapshot taken at its start — and PostgreSQL's implementation goes further than the SQL standard requires, also preventing phantom reads at this level. Serializable, the strongest, prevents all phenomena and guarantees the result is as if the concurrent transactions had run one after another in some serial order — catching even subtle anomalies that the other levels miss, at the cost of potentially aborting transactions with serialization failures the application must retry.

The practical guidance flows from this hierarchy. Read Committed (the default) is right for the great majority of workloads — it prevents the worst anomaly (dirty reads) cheaply, and most operations don't need a stable multi-read snapshot. Raise to Repeatable Read when a transaction reads the same data multiple times and needs consistent answers — a report computing several figures that must agree with each other, a multi-step calculation that must see a stable world. Raise to Serializable when concurrent transactions could produce an outcome impossible under any serial ordering — complex invariants spanning multiple rows where even Repeatable Read's snapshot isn't enough — accepting that you must retry serialization failures. The trade-off is always correctness-guarantee versus concurrency-and-retries: higher levels prevent more anomalies but allow less concurrency and require retry handling. Understanding the phenomena (what can go wrong) and the levels (what each prevents) lets you choose the minimum level that guarantees the correctness your operation needs — which is the skill of isolation: not always reaching for the strongest (it costs concurrency) nor always accepting the default (it may allow anomalies your operation can't tolerate), but matching the level to the operation's actual correctness requirement.

Choosing a level, and handling serialization failures

For most applications, leave it at the default (Read Committed) — it prevents the worst anomaly (dirty reads) cheaply. Raise it when correctness demands a stable view:

• Repeatable Read — when a transaction reads the same data multiple times and must get consistent answers (a report computing several figures that must agree; a multi-step calculation).
• Serializable — when concurrent transactions could produce an outcome impossible under any serial order (complex invariants across rows). With Serializable, be ready to retry: the database may abort a transaction with a serialization failure, and the app should simply re-run it.

   Application pattern for Serializable / Repeatable Read:
     loop:
       BEGIN ISOLATION LEVEL SERIALIZABLE
       ...do the work...
       try COMMIT
       on serialization_failure: ROLLBACK and retry the loop

A worked scenario: the transfer and the oversold item

Two classic scenarios show transactions and isolation doing their essential work, and walking through them makes the abstract guarantees concrete. The first is the money transfer — the canonical atomicity example — and the second is the last-item-in-stock race — the canonical isolation example. Together they cover the two halves of why transactions matter.

The transfer: move \$100 from account 1 to account 2. This is two updates — debit account 1, credit account 2 — and they *must* both happen or neither. Without a transaction, a crash or error between them is catastrophic: the money leaves account 1 but never arrives in account 2, and \$100 vanishes from the system. Wrapped in BEGIN ... COMMIT, atomicity guarantees both updates take effect together or neither does — the WAL ensures that even a power loss between the two updates recovers to a state where both or neither happened, never half. Consistency adds another guarantee: if account 1's balance would go negative and a CHECK (balance >= 0) constraint forbids it, the whole transaction rolls back — the transfer simply fails rather than leaving an invalid state. This scenario shows atomicity and consistency protecting a multi-step operation from partial completion and constraint violation — the bedrock of trusting a database with money.

The oversold item: two customers simultaneously try to buy the last unit of a product. This is an isolation problem. Naively, both transactions read "quantity = 1," both decide they can fulfill the order, both decrement to 0, and you've sold two units of a one-unit stock — overselling, a real and costly bug. The fix involves both transactions and the locking of Chapter 27, but the transaction framing is essential: the "check stock and decrement it" must be a single atomic, isolated operation, so that exactly one of the two concurrent buyers succeeds and the other is correctly told "out of stock." At Serializable isolation, PostgreSQL would detect that the two transactions can't both be valid under any serial ordering and abort one with a serialization failure (which the application retries, finding the item now gone). At lower levels, you'd use explicit row locking (SELECT ... FOR UPDATE, Chapter 27) so the second buyer waits for the first to finish and then sees quantity = 0. Either way, the principle is that concurrent access to shared mutable state must be coordinated through transactions and isolation, or you get races like overselling.

These two scenarios — the transfer (atomicity and consistency) and the oversold item (isolation) — are not contrived; they're the everyday reality of any system where operations span multiple steps or where multiple users touch the same data. Every e-commerce checkout, every banking operation, every inventory update, every booking system faces versions of them. And the answer is always transactions: wrap the multi-step operation for atomicity, choose the isolation level (or locking) that prevents the concurrency anomaly, and the database guarantees correctness even when the world doesn't take turns. This is why transactions are the property that most distinguishes a database from a spreadsheet (Chapter 1): a spreadsheet has no answer to "two people edit the last item at once" or "the power fails mid-transfer," while a database, through ACID transactions, handles both correctly. The scenarios make vivid what the acronym states abstractly — and they're worth keeping in mind as the concrete reasons the whole transaction machinery exists.

Transactions and durability: the WAL

The "D" in ACID — durability — deserves a closer look because it connects transactions to the internals you'll meet in Chapter 28, and because it's the guarantee that makes "committed" actually mean something. Durability promises that once a transaction commits, its changes survive anything: power loss, operating-system crash, hardware failure the instant after the commit returns. How can PostgreSQL promise this, given that writing to disk takes time and a crash could happen mid-write?

The answer is the write-ahead log (WAL), and its principle is in the name: log the change before applying it. Before PostgreSQL modifies the actual data pages, it writes a record of what it's about to do to the WAL — a sequential log on disk — and crucially, it ensures that WAL record is durably on disk (flushed, not just buffered) before it reports the commit as successful. So at the moment your transaction's COMMIT returns, the change is guaranteed to be recorded in the WAL on disk, even if the data pages themselves haven't been written yet. If the server crashes immediately after, on restart PostgreSQL replays the WAL — re-applying the logged changes — recovering every committed transaction. The change was safe the instant commit returned, because the WAL record was durable, even though the data file update came later.

This "write the log first, then the data" approach (write-ahead logging) is what makes durability both guaranteed and efficient. Guaranteed, because the commit doesn't return until the WAL is durable, so "committed" truly means "will survive a crash." Efficient, because writing to the WAL is a fast sequential append (disks handle sequential writes well), whereas updating the scattered data pages can happen later, in the background, batched — the slow random writes are deferred and amortized, while the fast sequential WAL write is what gates the commit. The WAL is also the foundation of more than durability: it's the source for replication (replicas replay the primary's WAL to stay in sync, Chapter 35) and point-in-time recovery (replaying WAL up to a chosen moment, Chapter 38). But its core job is durability — making the "D" in ACID real. Understanding that a commit's durability rests on the WAL being flushed before commit returns demystifies how a database can promise that committed data survives a crash one millisecond later: the promise is kept by the log, written first, recovered on restart. Chapter 28 goes deeper into the WAL's mechanics; here, it's enough to see that durability — the guarantee that lets you trust "saved" means saved — is delivered by writing the log ahead of the data.

Common mistakes

• Doing multi-step changes without a transaction — a crash or error leaves half-applied state (the transfer that debits but doesn't credit). Wrap related changes in BEGIN ... COMMIT.
• Assuming the default prevents all anomalies — Read Committed still allows non-repeatable and phantom reads. If you need a stable view, raise the level.
• Using Serializable without retry logic — serialization failures are expected; the app must catch and retry them, or it'll surface errors to users.
• Holding transactions open too long — a long-running transaction holds its snapshot (and blocks VACUUM from cleaning dead tuples, Chapter 28). Keep transactions short.

MVCC, deeper: how isolation is actually implemented

Understanding how PostgreSQL implements isolation — through MVCC — turns the isolation levels from rules to memorize into consequences you can derive, and it's one of the most illuminating pieces of database internals. The core idea is that PostgreSQL keeps multiple versions of each row, and each transaction sees the version appropriate to its snapshot.

When a transaction updates a row, PostgreSQL doesn't overwrite the existing row in place. Instead, it creates a new version of the row and marks the old version as superseded — but the old version remains physically present, still visible to transactions that should see the pre-update state. Each row version carries hidden system columns (xmin, the transaction that created it, and xmax, the transaction that superseded it) that record its visibility lifetime. A transaction's snapshot — essentially "which transactions had committed when my snapshot was taken" — determines which version of each row it sees: it sees the version created by a transaction that committed before its snapshot, and not yet superseded as of its snapshot. This is the mechanism: many versions coexist, and each transaction's snapshot picks the right version of each row for it.

This snapshot mechanism is the isolation levels, which is the beautiful insight. Read Committed takes a fresh snapshot at the start of each statement, so each statement sees the latest committed data — which is exactly why non-repeatable reads can happen (two statements have two snapshots, seeing two states). Repeatable Read takes one snapshot at the start of the transaction and uses it for the whole transaction, so every read sees the same consistent state — which is exactly why it prevents non-repeatable and phantom reads (one snapshot, one stable view). The isolation levels aren't separate mechanisms; they're different snapshot policies over the same MVCC machinery. Once you understand "you see a snapshot, and the level determines when the snapshot is taken," the whole isolation table becomes obvious rather than memorized.

The consequence that makes MVCC so valuable is that readers never block writers, and writers never block readers. Because a writer creates a new version rather than overwriting, a concurrent reader can still see the old version from its snapshot — the write doesn't block the read, and the read doesn't block the write. A long-running report (a reader) doesn't freeze out updates; ongoing updates don't freeze the report. Each proceeds on its own snapshot. This is why PostgreSQL handles mixed read/write workloads so gracefully — the bane of lock-based databases (where readers and writers contend) simply doesn't apply. (Two transactions writing the same row still conflict — that's the genuine contention Chapter 27 addresses — but the common reader/writer interaction is contention-free.) The cost of MVCC is that superseded row versions ("dead tuples") accumulate and must be cleaned up, which is what VACUUM does (Chapter 28) — a cost well worth paying for the concurrency MVCC enables. Understanding MVCC — multiple versions, snapshots selecting versions, isolation levels as snapshot policies, readers and writers not blocking — is the key that unlocks both the isolation levels of this chapter and the VACUUM internals of Chapter 28. It's the single most important internals concept in PostgreSQL, and it explains an enormous amount of the database's behavior.

Transaction best practices

Beyond choosing isolation levels, a few practices around transactions matter for both correctness and performance, and they're worth stating explicitly because violating them causes real problems. The overarching principle is keep transactions short, and several specific practices follow from it.

A transaction holds resources for its entire duration — its snapshot (which prevents VACUUM from cleaning up dead tuples newer than the snapshot), any locks it has acquired (which can block other transactions), and a connection. A long-running transaction therefore has outsized costs: it can cause table bloat (by holding back VACUUM, Chapter 28), block other transactions (by holding locks), and tie up a connection. So transactions should encompass exactly the work that must be atomic, and no more — don't open a transaction and then do slow work (network calls, user think-time, heavy computation) inside it while holding its snapshot and locks. The pattern to avoid is "begin transaction, fetch data, wait for the user to do something, then commit" — that holds the transaction open for human time, which is forever in database terms. Gather what you need, do the atomic work quickly, commit, and then do slow things.

Other practices: don't do non-database work inside a transaction if avoidable — calling an external API or sending an email inside a transaction means the transaction stays open for the duration of that external call (and if the call fails after commit, you can't un-send the email anyway). Handle errors properly — on any error within a transaction, roll back; a transaction left in a failed state (PostgreSQL marks it "aborted") rejects further statements until rolled back. Be ready to retry at Serializable and Repeatable Read levels, where serialization failures are expected and the correct response is to roll back and re-run the transaction. And wrap genuinely multi-step operations in transactions (the atomicity habit) while not wrapping single statements unnecessarily (each statement is already atomic on its own). These practices keep transactions doing their job — providing atomicity and isolation for the operations that need them — without the costs (bloat, blocking, held connections) that long or mishandled transactions impose. The discipline is simple to state: transactions should be as short as the atomicity requirement allows, contain only database work, handle errors with rollback, and retry when the isolation level requires it. Following it keeps both correctness and performance healthy.

Progressive project: make it atomic

In your domain, find an operation that changes multiple things that must succeed or fail together — and wrap it in a transaction:

1. Identify a multi-step change (place an order = insert order + insert items + decrement inventory; transfer = debit + credit; enroll = insert enrollment + increment count).
2. Wrap it in BEGIN ... COMMIT so it's atomic. Test that an error mid-way leaves nothing applied (use a deliberate failure + ROLLBACK).
3. Identify a read that needs a stable view (a report computing several totals that must agree) and consider Repeatable Read.
4. (Stretch) Find an invariant two concurrent transactions could violate (last-item-in-stock) and reason about whether Serializable + retry is warranted (you'll go deeper in Chapter 27).

Transactions in application code

Transactions aren't only a psql concept — they're how your application coordinates database work, and previewing that connection (developed in Part V) shows why understanding transactions matters for every developer, not just DBAs. When your application code performs a multi-step operation, it manages the transaction boundary: it tells the database to begin, performs its statements, and decides whether to commit or roll back based on whether everything succeeded.

The pattern in application code mirrors the SQL: open a transaction, do the work inside a try block, commit if all succeeds, roll back on any error. In Python with psycopg2 (Chapter 29), this looks like a connection used as a context manager that commits on success and rolls back on exception; in an ORM like SQLAlchemy (Chapter 30), it's a session that commits or rolls back. The crucial responsibility the application bears is owning the transaction boundary correctly — wrapping exactly the operations that must be atomic, committing only when all have succeeded, and rolling back on any failure. A common application bug is forgetting to manage the boundary properly: leaving a transaction uncommitted (so changes are lost or the connection stays in an open transaction), or not rolling back on error (leaving the transaction in an aborted state that rejects further work). The application is where transaction boundaries are decided, so getting them right is application code's responsibility.

Application code also bears the responsibility for the patterns this chapter described: retrying serialization failures (at Serializable/Repeatable Read, the app must catch the failure and re-run the transaction), keeping transactions short (not holding a transaction open across slow operations or user think-time), and handling errors by rolling back. These are application-level concerns that the database can't do for you — it provides the transaction mechanism, but the application decides how to use it. This is why transactions appear here in the internals part and in Part V's application integration: the mechanism is a database feature, but wielding it correctly is an application skill. The connection between them — the database guarantees ACID for the transactions the application defines, so the application must define them well — is essential to building correct database-backed systems. A perfect ACID database with badly-managed transaction boundaries in the application still produces incorrect results, because the atomicity is only as good as the boundaries the application draws around its operations.

ACID versus BASE: why these guarantees matter

It's worth situating ACID against its main alternative philosophy, because the contrast illuminates why ACID's guarantees are valuable and previews a decision you'll face in Part VI. Many NoSQL systems (Chapter 33) deliberately relax ACID in favor of what's called BASE — Basically Available, Soft state, Eventually consistent — trading strong consistency for availability and horizontal scalability. Understanding the trade-off clarifies what ACID buys you and when you might give it up.

The ACID approach (relational databases like PostgreSQL) prioritizes correctness: every transaction sees a consistent state, commits are durable, constraints always hold, and concurrent transactions don't corrupt each other. The cost is that providing these guarantees, especially across multiple machines, is hard and can limit availability and scalability (a theme Chapter 35's CAP theorem develops). The BASE approach (many distributed NoSQL systems) prioritizes availability and scale: the system stays responsive and scales across many machines, accepting that different parts may be temporarily inconsistent ("eventually consistent" — they converge over time) and that strong transactional guarantees are weakened or absent. For some workloads — a social media feed, a shopping cart, a like counter — eventual consistency is perfectly acceptable (it doesn't matter if a like count is briefly off), and the availability and scale are worth more than strict consistency.

But for many workloads — anything involving money, inventory, bookings, or invariants that must never be violated — ACID's guarantees are essential, and giving them up means rebuilding equivalent guarantees in application code, usually imperfectly. You cannot tolerate "eventually consistent" account balances or "basically available" but occasionally-double-booked reservations. This is why, despite the NoSQL movement, relational databases with full ACID remain the default for the vast majority of applications, and why even many NoSQL systems have added stronger consistency options over time: the guarantees turn out to matter more often than the "scale at any cost" enthusiasm of the early NoSQL era assumed. The judgment, which Chapter 37 develops, is to know what consistency your data actually needs — strict ACID for the operations where correctness is non-negotiable, eventual consistency acceptable where it isn't — and choose accordingly, rather than assuming either "always ACID" or "scale means giving up consistency." For now, the lesson is that ACID's guarantees, which this chapter detailed, are not free, not universal, but genuinely valuable — they're the reason you can trust a relational database with data where being wrong is unacceptable, and understanding them deeply lets you recognize when you need them (most of the time) and when you might trade them away (sometimes, deliberately).

This connects to theme #6 — the relational model is right for most problems — in a precise way. The relational database's ACID guarantees are a large part of why it's the right default: most data, most of the time, needs to be correct, and ACID makes correctness the baseline rather than something you must engineer yourself. The NoSQL systems that relax ACID are right for the specific cases where their trade-offs fit, but those cases are fewer than the early enthusiasm suggested, precisely because correctness matters so broadly. Knowing ACID deeply, then, isn't just transaction trivia — it's understanding the core guarantee that makes the relational database trustworthy, and the guarantee you'd be giving up if you chose an alternative. That understanding is what lets you make the choice deliberately rather than by fashion, which is exactly the judgment Part VI will ask of you. The next chapter goes one level deeper into the concurrency half of ACID — the locks, deadlocks, and strategies that coordinate writers against writers, the conflicts MVCC alone doesn't resolve — completing the picture of how a database stays correct when many hands touch the same data at once.

Summary

A transaction is an all-or-nothing unit of work, the mechanism for concurrency and durability. ACID formalizes its guarantees: Atomicity (all-or-nothing), Consistency (constraints always hold), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes, via the WAL). Isolation is the subtle part: concurrent transactions can suffer dirty reads, non-repeatable reads, and phantom reads, prevented by progressively stronger isolation levels — Read Committed (PostgreSQL's default, no dirty reads), Repeatable Read (a stable per-transaction snapshot, no phantoms in PostgreSQL), and Serializable (as-if-serial, with retry-on-failure). PostgreSQL achieves isolation with MVCC — keeping multiple row versions so readers never block writers and writers never block readers, each seeing a consistent snapshot. Use the default for most work; raise the level when you need a stable view or true serializability, and retry serialization failures.

You can now: - Define ACID and explain each property with an example. - Use BEGIN/COMMIT/ROLLBACK and savepoints for atomic, partially-revertible work. - Identify dirty/non-repeatable/phantom reads and which isolation level prevents each. - Explain MVCC and why readers and writers don't block each other. - Choose an isolation level and handle serialization failures with retry.

What's next. Chapter 27 — Concurrency Control — the deeper mechanics: locks (row/table/advisory), deadlocks, optimistic vs. pessimistic strategies, SELECT FOR UPDATE, the lost-update problem, and connection pooling — designing systems where thousands of users safely modify the same data.

Practice in exercises.md, test yourself with the quiz, apply it in the case studies, review the key takeaways, and go deeper with further reading.