Transactions and ACID

Move €100 from account A to account B: debit A, credit B. If the process dies between the two statements, €100 has vanished. If another process reads between them, it sees money that is in neither account. A transaction wraps both statements so that the world sees either both or neither, and so that a crash at any point leaves the database in a state where the transfer either fully happened or never started.

BEGIN opens it, COMMIT makes it permanent, ROLLBACK discards it. Every statement outside an explicit transaction is its own tiny one — auto-commit — which is why a single UPDATE is always atomic and a pair of them is not.

Atomicity: all or nothing. A rollback — explicit, or implied by a crash or an error — undoes every change the transaction made. Consistency: the database moves from one valid state to another; every constraint holds at commit. The database checks the constraints it knows about (keys, uniqueness, CHECK); business invariants it does not know about — "the total must not change" — are yours to express as constraints or to protect inside one transaction. Isolation: concurrent transactions do not see each other’s uncommitted work; how much of each other’s *committed* work they see is the isolation level, see Isolation Levels. Durability: once COMMIT returns, the change survives a crash.

They are independent. A system can be atomic without being durable (an in-memory database), durable without isolation (a log), consistent in the constraint sense while allowing every anomaly in the isolation sense. When someone says "ACID" ask which letter they mean.

Durability without rewriting every table page on every commit is achieved by writing the *change* to a sequential log first and fsync-ing that. On COMMIT the log record is flushed; the table pages are updated in memory and written back lazily. After a crash, recovery replays the log: committed transactions are redone, uncommitted ones are discarded. This is why a commit costs one sequential write and one fsync — a few milliseconds — regardless of how many pages it touched.

The WAL is also what replication ships to replicas, what point-in-time recovery replays, and what change-data-capture tails. Understanding it once explains half of operations.

Keep transactions short: they hold locks and pin old row versions for their whole duration. Never hold one open across a network call, a user prompt, or a sleep. Put the whole unit of work in one transaction — not "one per statement, with retries" — so that partial failure is impossible rather than handled. Use SAVEPOINT for a nested unit you may want to undo without abandoning the outer work. And handle the errors: a transaction that fails in the middle is in an aborted state until you roll it back, and every further statement will be rejected.