Lesson 14 · Transactions · Module 2

What ACID Promises

ACID is four separate guarantees that got welded into one marketing word, and the word now means roughly "safe" to most engineers. It is worth prising the four apart, because each one fails differently, each one is bought at a different price, and two of them do not promise anything like what the word suggests.

The win in this lesson: when something goes wrong with your data, you will be able to name which letter was actually violated — and, more often, notice that none of them were, because the failure was never inside a transaction's scope in the first place.

1. The problem: a write that half-happened

Two statements, one intent. Debit one account, credit another. Between those two statements sit a dozen ways for the world to interrupt you: the process is killed, the connection drops, a constraint fires, the machine loses power. The documentation's framing of the stakes is blunt:

"It would certainly not do for a system failure to result in Bob receiving $100.00 that was not debited from Alice."

PostgreSQL 18 documentation, Transactions

Note what the example is not about. It is not about two concurrent transfers racing each other. It is one transfer, alone on the machine, interrupted. That distinction is the spine of this lesson: the failure of "half of my writes landed" and the failure of "two concurrent operations trod on each other" are different problems with different letters attached, and conflating them is how people end up believing a transaction protects them from something it never touched.

2. What a transaction actually gives you

Start from the definition rather than the acronym:

"The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all."

PostgreSQL 18, Transactions

Two promises in that paragraph, not one. There is a promise about failure — none of the steps affect the database — and a promise about visibility — the intermediate states are not visible to others. The docs make the pairing explicit:

"So transactions must be all-or-nothing not only in terms of their permanent effect on the database, but also in terms of their visibility as they happen. The updates made so far by an open transaction are invisible to other transactions until the transaction completes, whereupon all the updates become visible simultaneously."

PostgreSQL 18, Transactions

Hold on to simultaneously. A transaction does not leak out gradually. Everything it did arrives for everyone else at one instant, and before that instant nothing it did exists. That is the whole product. Everything below is an examination of how far that product actually stretches.

3. A — atomicity is about aborting, not about concurrency

Atomicity is the most misread letter, because the word sounds like "indivisible, therefore nothing can interleave with it". That is not what it means here. The docs define it in terms of an outcome, not a lock:

"A transaction is said to be atomic: from the point of view of other transactions, it either happens completely or not at all."

PostgreSQL 18, Transactions

The mechanism underneath is abortability. A transaction accumulates changes that are not yet anyone else's business, and it retains, until the last moment, the ability to throw the whole pile away. That is the capability you are buying — and the API for it is exactly one command:

"If, partway through the transaction, we decide we do not want to commit (perhaps we just noticed that Alice's balance went negative), we can issue the command ROLLBACK instead of COMMIT, and all our updates so far will be canceled."

PostgreSQL 18, Transactions

"Abortability" is, honestly, the better name. It says what you get: a retractable write. A crash mid-way is just a rollback nobody typed. An error that aborts the transaction is a rollback the server typed for you. All three paths are the same path.

Atomicity also comes with a finer-grained control that most people never reach for, and which is worth knowing exists because it changes how you write long procedures:

"It's possible to control the statements in a transaction in a more granular fashion through the use of savepoints. Savepoints allow you to selectively discard parts of the transaction, while committing the rest."

PostgreSQL 18, Transactions · Savepoints

4. C — consistency is mostly your job

Here is the deflationary one. A, I and D are properties the database implements. C is, for the most part, a property you assert, and the database's contribution is narrow: it will enforce the specific rules you managed to express as constraints, and nothing else.

"Consistent" in ACID means "the database moves from one valid state to another valid state". But valid according to whom? A foreign key, a NOT NULL, a CHECK, a unique index — those are invariants the database can check, because you wrote them down in a language it understands. Everything else in your notion of a correct system lives in application code:

The bottom three rows are the interesting ones. If your ledger stops balancing, no ACID property was violated. Every transaction was atomic, isolated to whatever level you configured, and durable. The database did exactly what it promised and wrote down exactly the wrong numbers, because "the ledger balances" was never a thing it was told about.

So the useful reading of C is: C is the letter that describes the goal, while A, I and D describe the tools. It got into the acronym partly because ACID spells a word and ADI does not. Treating it as a guarantee the database hands you is how teams end up surprised that a perfectly transactional system is full of nonsense data.

5. I — isolation is a dial, not a state

Isolation is the letter with a hidden parameter. A and D are on or off; I has settings, and the setting you are running is almost certainly not the strongest one. That is a large enough subject that it gets its own lesson next — here the point is only that the dial exists, and what mechanism it turns.

In PostgreSQL, isolation is not built out of read locks. It is built out of snapshots:

"Internally, data consistency is maintained by using a multiversion model (Multiversion Concurrency Control, MVCC). This means that each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data."

PostgreSQL 18 documentation, Concurrency Control · Introduction

Read that phrase carefully — as it was some time ago. Your query is not looking at the database. It is looking at a version of the database, and the real one has moved on. That is not a defect; it is the entire trick, and it is what buys the property that makes Postgres pleasant under load:

"The main advantage of using the MVCC model of concurrency control rather than locking is that in MVCC locks acquired for querying (reading) data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading."

PostgreSQL 18, Concurrency Control · Introduction

The purpose of all this is stated in the same paragraph, and it is worth noticing how carefully hedged it is: the model "prevents statements from viewing inconsistent data produced by concurrent transactions performing updates on the same data rows, providing transaction isolation for each database session." Statements do not view inconsistent data. It does not say your transaction's decisions are safe from concurrent ones — only that what you read is a coherent picture of some moment.

And the docs point straight at the escape hatch for when the dial is not enough:

"Table- and row-level locking facilities are also available in PostgreSQL for applications which don't generally need full transaction isolation and prefer to explicitly manage particular points of conflict."

PostgreSQL 18, Concurrency Control · Introduction

Which tells you the shape of the real design space: a default level that is cheap and permits certain anomalies, stronger levels that cost more, and explicit locks for the two or three places in your system where you want to pay only at that spot. Choosing among those is the same kind of decision as Lesson 05's — a dial with a price on it, where the default was chosen for throughput and nobody asked you.

6. D — durability means the WAL reached disk

Durability sounds absolute and is in fact extremely specific. It means: a particular record, describing your change, was flushed to permanent storage before the server said "committed". That is the mechanism, and it is worth being able to state it in one sentence, because the sentence is where the caveats live.

"WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after WAL records describing the changes have been flushed to permanent storage."

PostgreSQL 18 documentation, Reliability and the Write-Ahead Log · WAL Introduction

Your table's pages are not on disk when the commit returns. That is the part people get wrong. The data files can lag arbitrarily far behind, and the system is still perfectly durable, because the log is the authority:

"If we follow this procedure, we do not need to flush data pages to disk on every transaction commit, because we know that in the event of a crash we will be able to recover the database using the log: any changes that have not been applied to the data pages can be redone from the WAL records."

PostgreSQL 18, WAL Introduction

This is also why durability is cheap enough to have by default. One sequential write, not a scatter of random page writes:

"Using WAL results in a significantly reduced number of disk writes, because only the WAL file needs to be flushed to disk to guarantee that a transaction is committed, rather than every data file changed by the transaction. The WAL file is written sequentially, and so the cost of syncing the WAL is much less than the cost of flushing the data pages."

PostgreSQL 18, WAL Introduction

And the throughput trick that follows from it: "when the server is processing many small concurrent transactions, one fsync of the WAL file may suffice to commit many transactions." Many commits, one physical flush. Durability at scale is a shared purchase.

Where this collides with Lesson 01

Now the question that matters at design-review scale: flushed to whose disk?

By default, durability in ACID means one machine's disk. That machine can be destroyed. Which is precisely the ladder Lesson 01 laid out — the WAL record that makes your commit durable is the same record that streams to a standby, and synchronous_commit is the setting that decides how far along that journey the server waits before telling you "done".

synchronous_commit = off            no wait at all — a recent commit can vanish on crash
                   = local          local WAL flush — classic single-node "durable"
                   = remote_write   a standby has written the commit record
                   = on             a standby has flushed it durably   (default)
                   = remote_apply   a standby has applied it, so it is visible there

So "D" is not one guarantee, it is a position on that ladder — and note the top rung, off, where D is simply switched off and the other three letters carry on working normally. A transaction can be atomic and isolated and still evaporate. That combination is legal, configurable, and occasionally the right choice.

7. What ACID does not cover

The most useful thing about knowing the four letters precisely is the list of failures that none of them address. These are the ones that actually bite production systems.

Rows five and six are where most real incidents live, and they are the reason "our database is ACID" is never an answer to "is this operation safe". The moment your unit of work spans a database and anything else — another service, a queue, an email, a second database — the transaction boundary ends at your database's edge and every guarantee stops there with it. Nothing inside ACID was ever about that, and no isolation level will buy it for you.

Row eight is Lesson 04's territory: the commit is durable, the acknowledgement is lost, the client retries, and you get the write twice. Atomicity guaranteed that each attempt was all-or-nothing. It said nothing about how many attempts there would be. Idempotence is a separate property you build, not a letter you get.

8. Residual risk

Even with all four letters working exactly as documented, these remain live:

• The dial you did not set. Your isolation level permits some set of anomalies. Not knowing which set is not the same as there being none — lesson 15 enumerates them.
• The invariants you did not declare. Every rule living only in application code is one bad code path away from being violated, permanently and durably.
• Hardware that lies. Durability assumes fsync means what it says. Consumer drives with volatile write caches and some virtualised storage have historically not honoured it.
• The one-machine boundary. A durable commit on a server that no longer exists is not a durable commit in any sense your users care about.
• The cross-system boundary. The most common data-integrity incident in a service-oriented system involves no ACID violation whatsoever.

9. Check yourself

10. Back to your world

Take the last data-integrity bug your team fixed and run it through the table in section 7. Most of the time the honest answer is the bottom half: the database did precisely what it promised, and the promise did not reach as far as the bug. That reframing is the whole point — it moves the fix from "add a transaction" to the thing that would actually have helped, which is usually an idempotency key, a declared constraint, or an outbox.

Then do the cheap audit. List the invariants your system depends on, and mark each one as declared or defended in code. The declared ones are safe from every code path that will ever exist. The others are a standing obligation. Moving even one across that line is usually a smaller change than it looks, and it is permanent.