ACID names four guarantees a transaction gives. PostgreSQL provides them with constraints, multiversion concurrency control (MVCC) snapshots and its write-ahead log (WAL).
- Atomicity means a transaction is all or nothing. If any statement in it fails, PostgreSQL rolls the whole transaction back, and none of its changes ever become visible.
- Consistency means every transaction leaves the data valid. PostgreSQL enforces this with constraints such as primary keys, foreign keys and
CHECK. - Isolation means a transaction does not see the unfinished changes of others. PostgreSQL gives each statement an MVCC snapshot of the data.
- Durability means a committed change survives a crash. PostgreSQL records the change in the WAL and flushes that log to disk before
COMMITreturns.
Outside a BEGIN block, PostgreSQL runs each statement as its own transaction. Wrapping the transfer below in BEGIN and COMMIT makes its two updates one atomic unit.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- Both operations succeed or both fail
Rewriting in plainer words…
This answer doesn't lend itself to a diagram - it reads best . No credits were charged.
Why there's no diagram: “”
The interactive diagram is below the answer - jump to diagram ↓ · Below it, the related concept . Jump to it ↓
The diagram below the answer is the concept . Jump to it ↓
Tailored explanation · switch back to · ·
What should the new diagram focus on?