ACID, one letter at a time

A hundred left Alice’s account and never arrived in Bob’s. The video this post goes with spends ninety seconds on the four promises that exist to stop that, one failure per letter: the transfer that debited and never credited, the write that broke a rule, the reader who saw a number that was rolled back a second later, and “committed” followed by a power cut.

What follows is what it had to leave out, and it starts with the one omission that is actually dangerous. The video says the isolation level is a setting and stops there. A reader who turns that setting up without reading the next section will ship something worse than what they had. Everything here is PostgreSQL 18.

Turning isolation up without a retry loop is a downgrade

PostgreSQL’s docs, on the Serializable level, in their second sentence: “applications using this level must be prepared to retry transactions due to serialization failures.” That is not a caveat. It is the mechanism. Serializable Snapshot Isolation does not stop a conflicting transaction at the door; it lets transactions run without blocking, watches for patterns that could produce a result no serial ordering explains, and rolls one of them back — with SQLSTATE 40001, serialization_failure.

Raising the level without a retry loop makes an application less reliable, not more. Under Read Committed the transfer never asks you to retry; under Serializable, some transactions get rolled back on purpose, at a rate that rises with concurrency, in ways the docs say are “very hard to predict exactly” — and the failure can arrive at COMMIT itself, after every statement ran without complaint, so code that treats a failed commit as fatal turns a correct-by-design rollback into a user-facing 500. Worse: “it is important that any data read from a permanent user table not be considered valid until the transaction which read it has successfully committed.” An application that reads a balance, sends an email, and then gets 40001 has emailed about a balance the database never actually observed.

Repeatable Read has the same requirement with a narrower trigger: it rolls back with could not serialize access due to concurrent update when you try to modify a row a concurrent transaction already changed, and the docs are blunt about the fix — “abort the current transaction and retry the whole transaction from the beginning.” Re-running just the failed statement is not a retry; the snapshot is stale.

The loop

Retry the whole unit of work, from the top, on exactly the SQLSTATEs that mean the database aborted this on purpose and it will probably work next time: 40001 and 40P01 (deadlock_detected). Bound the attempts, back off with jitter, re-raise everything else.

RETRYABLE = {"40001", "40P01"}

def run_serializable(conn, work, attempts=5):
    for attempt in range(1, attempts + 1):
        try:
            with conn.transaction():
                with conn.cursor() as cur:
                    cur.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                    return work(cur)  # trusted only once COMMIT returns
        except psycopg.Error as e:
            if e.sqlstate not in RETRYABLE or attempt == attempts:
                raise
            time.sleep(random.uniform(0, 0.05 * 2**attempt))

run_serializable(conn, transfer)
send_receipt()  # side effects go here, after the commit — never inside work()

Three things that shape enforces: the transaction boundary is the retry boundary, so a 40001 from COMMIT itself is caught by the same except; SET TRANSACTION has to be the first statement, because PostgreSQL refuses to change the level once a query has run; and side effects happen only after the loop returns, for the reason above. One more that bites people running replicas: Serializable’s integrity guarantee “does not yet extend to hot standby mode or logical replicas.”

A: what atomic says, and what it doesn’t

The video’s frame draws “the first update ran, then the process died” as a lost update. Without a transaction block, “each statement is executed in its own transaction,” so the first UPDATE was itself atomic and durable. What was lost is not a write; it is the relationship between two writes, which only a transaction block expresses.

And “there is no third ending” has an exception: a transaction can be prepared — parked on disk, committed to nothing, survivable across a restart — which is two-phase commit, covered below. One more thing worth knowing: sequences are not transactional. A value taken from a serial column “is immediately visible to all other transactions and is not rolled back if the transaction that made the changes aborts.” Gaps in an id column are atomicity working, not failing.

C: the letter everyone defines in a circle

“The database goes from one consistent state to another” says nothing, because it defines the word with itself. Here is the version with content: consistency is a property of a transaction — it takes the database from a state where every rule holds to another where every rule holds — and it only means something once you say whose rules, checked when.

There are three kinds. A declared constraint (CHECK, NOT NULL, UNIQUE, foreign key) is enforced by the database because you declared it, checked as each row is written — except foreign keys and constraints marked DEFERRABLE, which can wait until COMMIT. A trigger is enforced by the database running code you wrote, inside the same transaction as the statement that fired it — “if either the statement or the trigger causes an error, the effects of both will be rolled back.” An invariant the schema cannot express — the sum of all credits equals the sum of all debits — is enforced by the application, or nobody, and checking it across concurrent transactions is not free.

CREATE TABLE products (
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

CHECK can only see the row being checked, not other rows or other tables. For a rule that spans rows, the docs are direct: “It is very difficult to enforce business rules regarding data integrity using Read Committed transactions because the view of the data is shifting with each statement.” There are exactly two routes — Serializable with the retry loop above, or an explicit lock (SELECT FOR UPDATE, below) — and every other rule is a contract between you and the database, not something it enforces for you.

Locks, and the deadlocks they create

MVCC means reads never take blocking locks. Sometimes you want one anyway — “I am about to change this row based on what I just read, and nobody else may change it in between”:

SELECT balance FROM accounts WHERE name = 'Alice' FOR UPDATE;

This “prevents them from being locked, modified or deleted by other transactions until the current transaction ends.” The trap for people arriving from other databases: SELECT FOR UPDATE does not stop a concurrent transaction from updating the row once you commit without actually writing it — “to do that in PostgreSQL you must actually update the row, even if no values need to be changed.” Locking a row to “reserve” it and committing without touching it reserved nothing.

Two transactions each holding a lock the other wants is a deadlock, found by timeout rather than by watching every wait — deadlock_timeout (default one second) is how long a wait runs before PostgreSQL checks, because “the check for deadlock is relatively expensive.” The loser gets 40P01, which is why it’s in the retry set above. The defence is ordering: “acquire locks on multiple objects in a consistent order.” Debit the lower account number first, always, and the classic opposite-direction transfer deadlock cannot happen.

D: what “committed” costs

The video draws one log and one flush: the change goes to the write-ahead log, the log is flushed, and only then does COMMIT return. The rule: “changes to data files must be written only after those changes have been logged.” The payoff: “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.” Table pages get written whenever convenient, and if the power goes first, “any changes that have not been applied to the data pages can be redone from the WAL records.”

That’s true, and it has a knob most people don’t know exists. synchronous_commit has five values, not two: off means the transaction is logically complete with no flush at all; local flushes to local disk; on (the default) also waits for a synchronous standby to flush it. With no standbys configured, “the only meaningful settings are on and off” — and off is precisely bounded: “an operating system or database crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly.” It is loss, never corruption, which is the difference from disabling fsync entirely — that “can result in unrecoverable data corruption” and is for throwaway databases only. SET LOCAL synchronous_commit TO OFF makes one transaction’s commit asynchronous; a transaction recording an ATM’s cash dispensing should never be the one you pick.

Two-phase commit: the third ending

PREPARE TRANSACTION is how one transaction becomes part of an atomic commit across several databases. After it runs, “the transaction is no longer associated with the current session; instead, its state is fully stored on disk,” survivable across a crash. From any session, COMMIT PREPARED or ROLLBACK PREPARED finishes it:

BEGIN;
UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice';
PREPARE TRANSACTION 'transfer-8841';
-- an external coordinator confirms every other participant is prepared
COMMIT PREPARED 'transfer-8841';

Prepare is the promise; commit prepared is the act, issued once every participant has made the promise. The docs’ own warning: “Unless you’re writing a transaction manager, you probably shouldn’t be using PREPARE TRANSACTION” — a prepared transaction nobody finishes holds its locks forever and blocks VACUUM, which is why the feature is off by default until you have a coordinator that will never forget one.

The C in CAP is not the C in ACID

Gilbert and Lynch’s CAP consistency is “each server returns the right response to each request” for a service that emulates “a single (emulated) register” — one value, replicated across machines. Nothing in that register is constrained: no CHECK, no foreign key, no sum that must balance. CAP’s consistency is about every copy agreeing on the last write. If you want the ACID letter it most resembles, it isn’t the C — it’s the I, the “as if one at a time” story, applied across machines instead of sessions. A CHECK constraint is never in doubt regardless of which side of that trade-off a system sits on; whether a query against a standby has seen the latest write is a different question entirely, and the two get confused because they share a letter.