In 09-01 you saw what a transaction does. Now it's time for what exactly it guarantees, and the answer fits in four letters: ACID — atomicity, consistency, isolation and durability. They aren't a marketing label: they're four concrete promises, each with a mechanism behind it, each with a known way of breaking, and each with a limit it's worth knowing the location of.

And there's a reward at the end of the lesson. The fourth letter will take us to the WAL, the log that makes your data survive a power cut and that closes the promise 05-04 left open about recovering a deletion. And the third will take us at last to MVCC, the model with which PostgreSQL lets one session read without blocking anybody while another writes the same row — the mechanism that explains, precisely, the dead rows, the bloat and the need for VACUUM that 08-05 left pending.

1. The four guarantees at a glance
2. Atomicity: all or nothing
3. Consistency: from one valid state to another valid state
4. Isolation: as if they were sequential
5. Durability: the WAL, fsync and synchronous_commit
6. MVCC: isolation without blocking reads
7. From MVCC to bloat: why VACUUM exists
8. How each engine implements isolation
9. What ACID does not guarantee you
10. Common Mistakes and Tips
11. Exercises
12. Conclusion

A useful way of remembering them: A and D protect you against failures (errors, crashes, power cuts); C and I protect you against the others (your own rules and the other sessions).

It's the one you've already seen: the four steps of confirming an order are a single one. But the interesting question is how the engine does it, because PostgreSQL's answer surprises anyone coming from other systems.

In an engine with an undo log —InnoDB, Oracle— the UPDATE overwrites the row in place and stores a copy of the previous value elsewhere. The ROLLBACK consists of rereading that log and physically restoring the old values: the bigger the transaction, the more expensive undoing it is.

PostgreSQL doesn't do that. An UPDATE overwrites nothing: it writes a new version of the row and leaves the old one where it was, marked with the identifier of the transaction that replaced it. Undoing is then trivial:

In PostgreSQL, a ROLLBACK restores nothing: it just notes in pg_xact that this transaction aborted. From that instant, every row version it wrote becomes invisible to everybody, and the old ones are the good ones again.

The practical consequences of this design decision are large and you'll notice them at work:

That's why the BEGIN ... ROLLBACK that 08-05 recommended for analysing a DELETE with EXPLAIN ANALYZE is so cheap in PostgreSQL: undoing costs nothing. And that's why, in exchange, PostgreSQL needs a VACUUM the others don't — section 7.

The C is the worst-explained letter of the four, because it sounds like "the data is correct" and it isn't that. What it promises is more modest and more useful:

If the database met every declared rule before the transaction, it will still meet them afterwards. The transaction may violate them during its execution; what it can't do is leave them violated on committing.

And there's the small print: "declared rules". The database guarantees exactly what you've told it, not one rule more. Everything from 05-01 counts:

And nothing in the schema prevents these: a customer reviewing a product they never bought; a delivered order having no lines at all; the sum of the lines not matching the amount charged; a product with active = FALSE being sold. They're undeclared business rules, and the database doesn't know they exist.

"Stock can never go negative" can be enforced in two ways, and it's worth understanding that they aren't equivalent.

Way 1 — declarative. It's the one GreenStore already has:

Advantage: it's inviolable. It doesn't matter who writes, from which application, in which language or at three in the morning. Drawback: the only possible response is an error, which additionally aborts the whole transaction (09-01) and forces your code to interpret a message.

Way 2 — in the statement. A conditional UPDATE that doesn't buy if there's no stock:

Zero rows, with no error and nothing aborted. Your application reads the affected-row counter —05-03's UPDATE N, 09-01's :ROW_COUNT— and if it's 0 it replies "out of stock" to the customer. And there's an enormous property hidden in there: that UPDATE is also correct in the face of concurrency, because the check (stock >= 1) and the write happen in the same atomic statement. It's the same lesson as 05-05's upsert: checking and then acting in two statements is never safe.

The golden rule of the C: use both. The CHECK is the net that guarantees the invariant come what may; the conditional WHERE is what turns "exception" into "normal business flow". The first protects the data, the second protects the user experience.

What the database can't do for you is the part you haven't told it about. Rules like "a paid order must have at least one line" or "you can't review what you haven't bought" need a declared constraint that doesn't exist today, a trigger (10-05) or application code inside the same transaction. Consistency is a shared responsibility, and the database only signs its half.

The promise, in one sentence:

Several transactions running at the same time must produce the same result as if they'd run one after another, in some order.

You already saw it working in 09-01: while A had the matcha's stock at 39 uncommitted, B carried on reading 40. As far as B was concerned, A's transaction hadn't started yet; after the COMMIT, it had happened in its entirety. Never halfway.

And here's the nuance that makes this the most interesting of the four letters: isolation is negotiable. The other three are all or nothing, but of this one you can ask for more or less:

That negotiation is the isolation levels, with their four rungs and their four anomalies, and it's the entire content of 09-04. Here it's enough to know the lever exists and that PostgreSQL ships with it set to READ COMMITTED by default.

The promise: if the server told you COMMIT, the data is there, even if you rip the cable out a microsecond later.

The problem is that writing to disk is slow and the data pages are scattered around the file. If every COMMIT had to write every modified page to disk, at their random positions, and wait for them to land, performance would be unacceptable. The solution, universal across all serious engines, is the write-ahead log: WAL (Write-Ahead Log).

The key idea is the order: the log first, the data afterwards (hence "write-ahead"). The WAL is a sequential file, and writing sequentially is orders of magnitude faster than writing at random positions. At the instant of the COMMIT the data files may be completely out of date; it doesn't matter, because the WAL already contains the recipe for rebuilding them.

And fsync is the critical word: writing isn't enough, because the operating system and the disk itself have their own caches. fsync is the call that forces the data to be physically on the persistent medium before carrying on.

That fsync is the only thing standing between your COMMIT and an instantaneous reply. PostgreSQL lets you negotiate it:

It's a legitimate —and very profitable— setting on a re-runnable data load or on a metrics table. It's unacceptable on an order or a payment.

⚠️ What genuinely is outrageous: fsync = off. Don't confuse the two parameters. With synchronous_commit = off you can lose the last transactions, but the database stays intact. With fsync = off you can lose the entire database: the writes reach disk in any order and recovery leaves a corrupt, unrepairable data file. It's meant for disposable test benches and nothing else.

The WAL isn't only good for recovering from a crash. Since it's a complete, ordered log of everything that has changed, it's good for two more things we'll only name here, because they're administration material:

• Replication. If you send the WAL to a second server and it applies it as it goes, you have a live copy of the database. It's the foundation of high availability and of read-only replicas for reports.
• PITR (Point-In-Time Recovery). With a base backup plus all the subsequent archived WAL, you can restore the database to any specific instant, for example 2026-03-05 11:59:58, two seconds before that DELETE FROM orders; with no WHERE. This is the promise 05-04 left open: there's no "undo" for something already committed, but there is a time machine, provided somebody had configured archiving before the accident.

We've mentioned it three times already; time to open it up. MVCC stands for Multi-Version Concurrency Control.

The idea, in one sentence: each row can exist in several versions at once, and each transaction sees the version that corresponds to it according to when it started.

From that comes PostgreSQL's most valuable property in concurrency:

Readers never block writers, and writers never block readers.

That's why in 09-01 session B could read the matcha's stock without waiting a single millisecond while A was modifying it: A wrote a new version, and B carried on reading the old one, which as far as B was concerned was the good one.

Every row version carries two system columns you can query even though they don't appear in SELECT *:

Look at it live. Reload the database and run:

(The transaction numbers depend on your installation; what matters is the relationships between them.) The row was created by transaction 748 —the loading INSERT— and nobody has touched it since (xmax = 0). Now modify it:

Three things have changed at once, and they're the whole explanation of MVCC:

1. The xmin is different: this is a new row, created by transaction 812.
2. The ctid is different: it's at another physical spot on the page. The old row is still there, at (0,15), with its xmax now set to 812.
3. The table takes up one more row on disk, even though SELECT COUNT(*) still returns 20.

And that's literally what that sentence in 08-05 meant: "an UPDATE doesn't modify the row: it writes a new version and marks the old one as dead". Now you know what it marks it with: its xmax.

When a transaction needs to decide which versions it sees, it takes a snapshot: the list of which transactions were committed at that moment. With it, each version's visibility rule is straightforward:

A version is visible if its xmin is committed and earlier than my snapshot, and its xmax is empty, aborted, or later than my snapshot.

There's everything you saw in 09-01 with no explanation:

• A sees its own change because the new version's xmin is its own transaction.
• B doesn't see it because that xmin corresponds to a transaction not yet committed, and for B that's equivalent to non-existent.
• After the COMMIT, B's next snapshot does include A, and the new version becomes visible.
• And a ROLLBACK needs to delete nothing: it's enough for the transaction to be marked as aborted in pg_xact for its xmin to validate none of its versions.

When the snapshot is taken is exactly the difference between the isolation levels: in READ COMMITTED a new one is taken on every statement; in REPEATABLE READ and SERIALIZABLE, a single one for the whole transaction. That one sentence explains 90 % of 09-04.

And now the bill. If every UPDATE leaves a dead version and every DELETE just sets an xmax, the table only grows. The versions no longer visible to any transaction are the dead rows (dead tuples), and the space they occupy is 08-05's bloat.

Twenty live rows and five corpses, after five updates of a single row. There's the full circle, and this is the whole chain 08-05 left half-finished:

And that explains the three things 08-05 announced and couldn't justify:

1. Why there are dead rows. Because they're the old versions MVCC needed in order not to block anybody.
2. Why an open transaction prevents cleaning. Because VACUUM can only remove a version if no live transaction can need it. A session idle in transaction for three hours holds a three-hour-old snapshot, and with it every dead version in the whole database. It's 09-01's section 11 damage, now with its mechanism.
3. Why CREATE INDEX CONCURRENTLY exists. Because building a normal index needs to block the table's writes, and MVCC allows doing it without that lock in exchange for walking the table twice. The detail is in 09-05.

Dialect note: the vocabulary is deceptive. "REPEATABLE READ" means different things in PostgreSQL and in InnoDB, and "READ COMMITTED" doesn't behave the same in PostgreSQL as in SQL Server without snapshot. The isolation level isn't portable: it's the first thing to verify when migrating an application between engines, and the detail is in 09-04.

Four honest limits, because ACID is cited far more often than it's understood:

1. It doesn't guarantee your logic is correct. A perfectly atomic, consistent, isolated and durable transaction can reduce the wrong product's stock. ACID guarantees it will be done whole and forever; whether it was the right thing to do is your business.
2. It doesn't guarantee the rules you didn't declare. The C only signs off what's in the schema (section 3).
3. It doesn't extend beyond the database. This is the important one in the real world: if your transaction reduces the stock and also calls the payment gateway, the gateway doesn't take part in your ROLLBACK. You can undo the order; you can't undo the charge. That's why 09-03 will insist that no external call should live inside an open transaction, and that's why patterns like the outbox or sagas exist.
4. It doesn't survive intact when spread across several databases. Coordinating a COMMIT between two servers demands a two-phase protocol, which is slow and fragile.

That last point is why the acronym BASE (Basically Available, Soft state, Eventually consistent) appears in distributed systems: instead of guaranteeing coherence at all times, you guarantee that the system converges to a coherent state. It's a deliberate trade-off, not a defective version of ACID — but it's a trade-off that only makes sense when the spreading is unavoidable. In a single-server relational database, ACID is complete, free and there's no reason at all to give it up.

• Believing the C means "the data is correct". It means "the declared rules are respected". Whatever isn't in the schema isn't guaranteed.
• Trusting validation to the application alone. Tomorrow there'll be a migration script, an intern with psql or a second application. The CHECK is always there; your if is only in your code.
• Trusting the CHECK alone. It aborts the whole transaction and turns a normal business case —"out of stock"— into an exception. Add the WHERE stock >= 1 and read the affected rows.
• Confusing synchronous_commit = off with fsync = off. The first risks the last transactions; the second risks the entire database.
• Assuming a big ROLLBACK is expensive. In PostgreSQL it's practically free; in InnoDB and Oracle it can take longer than the operation itself. It's the reverse of what almost everybody expects.
• Believing DELETE frees space, or that UPDATE rewrites the row. Neither: they leave dead versions, and the space is only reused after VACUUM.
• Leaving a transaction open and then complaining about bloat. They're the same thing: the old snapshot prevents cleaning.
• Putting an HTTP call inside a transaction. ACID ends at the database's edge; the card charge isn't undone by a ROLLBACK.
• Tip: declare the rules twice, in the schema and in the statement. The first protects the data; the second, the user experience.
• Tip: look at xmin, xmax and ctid once in your life. Physically seeing how an UPDATE creates a new row is worth ten explanations of MVCC.
• Tip: if you care about your data, check today that you have backups and WAL archiving. PITR can't be improvised after the accident.

On the freshly reloaded database, demonstrate the MVCC model experimentally.

1. Query xmin, xmax, ctid and stock of product 15 and note them down.
2. Run UPDATE products SET stock = stock - 1 WHERE id = 15; three times in a row (under autocommit), querying the same columns after each one.
3. Query n_live_tup and n_dead_tup in pg_stat_user_tables for products. Explain both numbers.
4. Run VACUUM products; and look at them again. Has the table file's size (pg_relation_size('products')) gone down? Why?
5. Now repeat step 2 inside BEGIN … ROLLBACK. How many dead rows are left after the ROLLBACK? And why was the ROLLBACK instantaneous?

GreenStore wants a new rule: a return's amount can't exceed its order's total billed. Nothing prevents it today, and in fact you can check it:

1. Does it get inserted? Which ACID letter is at stake and why doesn't the engine protest?
2. Can that rule be expressed with a CHECK? Reason it out by looking at what information the check needs.
3. List three ways of enforcing it, saying in each case who guarantees it and what hole it leaves.
4. Write the query that detects today whether any GreenStore return violates the rule.

A colleague proposes this configuration for GreenStore's production server, "because it goes faster":

And adds: "anyway, we have a replica and we back up every night".

1. Explain, parameter by parameter, exactly what is lost in a power cut.
2. Why does the replica not solve the fsync = off problem?
3. In what specific scenario would setting synchronous_commit = off be reasonable, and in which would it be unacceptable? Give an example of each with GreenStore tables.
4. What mechanism would allow recovering the database to the instant before the failure, and what has to have been configured beforehand to be able to use it?

Solution 1

1 and 2. The xmin changes on every UPDATE (each is a different transaction creating a new version) and so does the ctid, because each version occupies a new physical position. The visible version's xmax is always 0; the one set to the new transaction's value is the previous version's, which you no longer see.

3. n_live_tup = 20 and n_dead_tup = 3: twenty live rows and three dead versions, one per UPDATE. The table now has 23 versions stored for 20 logical rows.

4. After VACUUM products;, n_dead_tup drops to 0, but pg_relation_size doesn't change. And that's the important part of the exercise: VACUUM marks the space as reusable by the table itself, it doesn't give it back to the operating system. For that you need VACUUM FULL, which rewrites the whole table with an exclusive lock (08-05). That's why bloat is prevented and not cured.

5. There are still three dead rows: the versions the transaction wrote before being undone are still physically there, only now they're invisible because their xmin corresponds to an aborted transaction. And the ROLLBACK was instantaneous precisely because of that: it restored nothing, it just noted "aborted" in pg_xact (section 2). Undoing doesn't erase the work done: it leaves it invisible and hands it over to VACUUM.

Solution 2

1. Yes, it gets inserted. INSERT 0 1, with no complaint at all. The letter at stake is the C: the database guarantees the declared rules, and that rule isn't declared anywhere. returns' constraints only require that the order_id exists and that the amount is >= 0. Nine thousand euros satisfies both.

2. No, it can't be done with a CHECK. A CHECK can only look at the columns of the row itself that's being inserted. To know whether €9,999.00 is too much you have to add up order 1's lines, that is, query another table, and a CHECK doesn't allow that (PostgreSQL rejects subqueries in a CHECK, precisely because the result could change afterwards and the constraint would stop being satisfied without anybody touching the row).

3. Three ways:

4. The query that audits the current state:

On the freshly reloaded database it returns 0 rows: the three returns (€26.75, €34.02 and €19.80 on orders 6, 10 and 13) are all below their order's total. With the €9,999.00 row inserted, it returns one.

Solution 3

1. Parameter by parameter:

2. The replica saves nothing because it replicates what the primary says happened. If the primary corrupts its data, it propagates corrupt data; and if the failure is detected days later, the corruption is already in the replica and in the last few nights' backups. A replica protects against one server's hardware failure, not against logical corruption.

3. Reasonable on a bulk, re-runnable load: filling a metrics or analytics table with SET LOCAL synchronous_commit = off, where losing the last few seconds only means running the process again. Unacceptable on orders, order_lines and returns: losing a confirmed order means the customer paid and there's nothing in the system, and no latency saving makes up for that.

4. PITR. With a base backup and continuous WAL archiving the database can be restored to any instant before the failure. What has to be configured beforehand —and this is the whole moral— is wal_level = replica (or higher), archive_mode = on with its archive_command, a reliable archive destination and periodic, tested base backups. A backup that has never been restored isn't a backup: it's a hope.

You now know exactly what the four letters promise and what's underneath each one:

• Atomicity: all or nothing. And in PostgreSQL the ROLLBACK restores nothing: it marks the transaction as aborted and its row versions stop being visible. That's why undoing is free here and expensive in InnoDB or Oracle.
• Consistency: from one valid state to another according to the declared rules. CHECK, FK, UNIQUE and NOT NULL are the half the database signs; the other half is yours. And the stock case teaches you to use both routes: the CHECK (stock >= 0) as an inviolable net and the UPDATE ... WHERE stock >= 1 as the business flow, which is additionally safe against concurrency by being a single statement.
• Isolation: concurrent transactions behave as if they were sequential. It's the only negotiable letter, and that negotiation is 09-04's isolation levels.
• Durability: the WAL is written and synced before the data, which is why a COMMIT survives a power cut. synchronous_commit is the legitimate lever; fsync = off isn't. And from the WAL come replication and PITR too, which is the answer 05-04 left pending on how to recover a deletion.
• MVCC: each row lives in several versions, marked with xmin and xmax, and each transaction sees the ones its snapshot allows. Hence readers don't block writers, and hence an UPDATE creates a new row with a different ctid and leaves the old one dead.
• And hence the bloat: the dead versions pile up, VACUUM marks them as reusable, and an open transaction prevents that. The chain 08-05 left half-finished is closed, including the reason CREATE INDEX CONCURRENTLY exists.
• The limits of ACID: it doesn't validate your logic, it doesn't guess your rules, it doesn't extend to the payment gateway and it doesn't cross a server's boundary for free — hence BASE and distributed systems.

With the theory in place, it's time for the full set of controls. In Transaction Control Statements you'll see all of BEGIN's options (ISOLATION LEVEL, READ ONLY, DEFERRABLE), the SAVEPOINTs —which let you undo only part of a transaction and, along the way, rescue you from 09-01's aborted state—, how to set the default level, how psycopg's, JDBC's and SQLAlchemy's autocommit behaves, why in PostgreSQL you can ROLLBACK a CREATE TABLE and in MySQL you can't, and the complete application pattern: try / except / rollback, idempotent retries and the ban on calling external services with a transaction open. All of it building, from start to finish, the confirmation of a GreenStore order.