Inside one transaction
A transaction groups several statements into one unit of work: either all of them take effect, or none do. Our example moves 100 from Ann to Ben. Together they hold 800, and nobody should ever see 700 or 900.
The bank app sends BEGIN. From now on the DBMS, here PostgreSQL, treats every statement from this session as part of one transaction, until COMMIT or ROLLBACK ends it.
First, add 100 to Ben. PostgreSQL writes a new version of Ben’s row holding 400 and locks the row against other writers. A record of the change goes into the write-ahead log (WAL), still in memory.
Then take 100 from Ann: a new row version with 400, another lock, another log record. Inside this transaction the money has moved. Nobody else can see it yet.
Meanwhile a second session reads both balances. It gets Ann 500 and Ben 300, the last committed values, never the half-done transfer. PostgreSQL never shows a transaction’s uncommitted changes to anyone else. That is Isolation.
Commit and crash
COMMIT asks for the change to be permanent. The DBMS adds a commit record to the WAL and forces the log to disk. Only after the disk confirms does it reply. The table files can be updated later. That is Durability.
The row locks are released and the reply reaches the app. Both new balances appear to everyone at the same instant: the reader now gets 400 and 400. The total is still 800.
Now the power fails. Memory is wiped, and the table files on disk may still hold the old balances. On restart PostgreSQL replays the WAL from the last checkpoint and redoes every committed change: 400 and 400 again.
When it goes wrong
A second transfer tries to move 500. Ben is credited first: 900, inside the transaction. But Ann would drop to -100, and the rule CHECK (bal >= 0) refuses it. The DBMS reports an error.
After an error PostgreSQL aborts the whole transaction. ROLLBACK undoes every change it made, so Ben is back to 400. All or nothing: that is Atomicity. The CHECK rule kept the data valid: that is Consistency.
ACID names the four promises. Atomicity: all or nothing. Consistency: every rule holds after each transaction. Isolation: others never see work in progress. Durability: once committed, it survives a crash. Logs, locks and row versions keep them.
What happens
;; step 1/11 · unit CREATE TABLE acct (id integer PRIMARY KEY, name text, bal integer CHECK (bal >= 0)); INSERT INTO acct VALUES (1, 'ann', 500); INSERT INTO acct VALUES (2, 'ben', 300); id name bal 1 ann 500 2 ben 300 total 800 the transfer is two UPDATEs: Ben + 100, then Ann - 100 a crash or error between them must not leave 900 (or 700) in the table
Real WAL records describe changes to 8 kB table and index pages, not "ben 300→400"; the figure writes them as rows so you can read them. The card shows only T1’s records: the failed transfer also writes WAL (its change to Ben and an abort record), left out to keep the card short. COMMIT waits for the WAL flush because synchronous_commit is on by default. Turning it off makes commits faster but can lose the last few committed transactions in a crash (never corrupting the data).