ACID in PostgreSQL: what is guaranteed and what is not? Basic

The problem

A has 100, B has 50. Moving 30 from A to B takes two statements: debit A, credit B.

If each statement autocommits and the app server dies between them, the database keeps A = 70, B = 50: the total drops from 150 to 120. Both UPDATEs are valid; the bug is that one business operation was split into two commits.

Wrapping both statements in one transaction (a group of SQL statements that commit together or roll back together) is the first step. ACID is four questions to answer next, not a switch that makes all logic correct.

ACID in one table

Letter	Question in the transfer example	PostgreSQL guarantees	PostgreSQL does not guarantee
A Atomicity	What if the debit succeeds and the credit fails?	Statements in the same transaction commit together or roll back together	HTTP calls, emails, messages already sent out
C Consistency	After commit, do the business rules still hold?	Declared constraints (CHECK, UNIQUE, FK, EXCLUDE)	Undeclared rules, e.g. “whatever is debited must be credited”
I Isolation	What do other requests see while it runs?	No reads of uncommitted data	Every other race under Read Committed (see isolation levels)
D Durability	Commit reported done, then power is lost?	Recovery from WAL locally (default configuration)	Whether a replica has it, whether the client got the response

Each letter, briefly

A: the boundary is COMMIT

• BEGIN opens a transaction; COMMIT or ROLLBACK closes it.
• Without BEGIN, each statement is its own transaction (autocommit). Two consecutive UPDATEs are not atomic together. Drivers and ORMs often enable autocommit implicitly, so check the real transaction boundary in code.
• After an error inside a transaction, every following statement is rejected with SQLSTATE 25P02 (in_failed_sql_transaction) until ROLLBACK. Do not catch the error and continue in the same transaction (unless you use a savepoint).

C: the database only enforces rules it knows

• CHECK (balance >= 0) blocks negative balances. It does not know “A + B must stay constant”: debiting 30 and crediting 20 still commits normally.
• UPDATE ... WHERE balance >= 30 when A has only 10 does not raise an error; it just returns 0 rows. The app must check the row count and ROLLBACK; if it still credits B and commits, money is created out of thin air.
• Rules spanning several rows: use atomic SQL, EXCLUDE/UNIQUE, locks or Serializable (see anomalies).

I: not seeing half-done data does not mean no races

• The default is Read Committed: each SELECT takes a fresh snapshot when the statement starts. Other sessions do not see A = 70 before commit.
• But two SELECTs in the same transaction can see two different states, and “read the balance, check it in code, then UPDATE” can still race. Put the condition into the UPDATE itself and check RETURNING; if you need stability across several steps, use a row-level lock.

D: a durable commit does not mean every copy has the data

PostgreSQL writes the write-ahead log (WAL) before trusting data pages. With synchronous_commit = on (default) and fsync = on, a commit is only reported successful after WAL is flushed locally; a crash after that is still recoverable from WAL.

Configuration	Risk
synchronous_commit = off	Lose the last few transactions after a crash, window up to 3 x wal_writer_delay (default 200ms, so 600ms). The database stays consistent
fsync = off	Possible unrecoverable data corruption. Never turn it off on real data
remote_write / remote_apply	Waits for synchronous replicas; requires synchronous_standby_names, costs latency

Where it crashes, what you get

Failure point	Database state	What the client knows	How to handle it
After UPDATE #1, before COMMIT (app or connection dies)	Full rollback: A = 100, B = 50	Connection error	Safe to retry
While sending COMMIT, connection lost before the ack	Unknown: may or may not have committed	Connection error, ambiguous outcome	Idempotency key, look up the state before retrying
After COMMIT, before the client receives the response	Durable (WAL flushed)	Timeout even though it succeeded	Same idempotency key to fetch the earlier result
Server crashes after the ack, synchronous_commit = on	Recovered from WAL, nothing lost	n/a	Nothing to do

The second row is the most expensive one in production: a blind retry with a new request ID transfers the money twice.

How it works

One transaction moving 30: state before and after COMMIT

Enlarge diagram

Application → PostgreSQLBEGIN

◀ Prev▶ PlayNext ▶↺ RestartStep 1/5

The application opens a transaction. The starting balances are A = 100, B = 50; no change exists yet for other sessions to see.

Point	The transfer session sees	A new SELECT from another session sees
Before BEGIN	A = 100, B = 50	A = 100, B = 50
A debited, B not yet credited	A = 70, B = 50	A = 100, B = 50
B credited, not yet committed	A = 70, B = 80	A = 100, B = 50
After COMMIT	A = 70, B = 80	A = 70, B = 80

The table assumes no third transaction touches the two accounts. A SELECT that started before the commit keeps its old snapshot.

Real-world example

Moving 30 units between two internal wallets

The transaction contains the debit, the credit and the transfers record (one decision). The service only returns “success” after COMMIT is confirmed. If the debit updates 0 rows for lack of funds: roll back, return a business error, skip the credit.

The notification email is not in the transaction: write a row to an outbox table in the same transaction, and a worker sends the email after commit (see transactional outbox).

The client sends an Idempotency-Key, stored in transfers with UNIQUE, so a retry after an ambiguous failure returns the earlier result instead of transferring again.

Run the single-session demo (TEMP table, leaves nothing behind):

docker compose -f examples/docker-compose.yml up -d postgres
docker compose -f examples/docker-compose.yml exec -T postgres psql -U hub -d hub < examples/src/postgres-acid/acid-demo.sql

BEGIN;
UPDATE acid_accounts SET balance = balance - 30 WHERE account_id = 1 AND balance >= 30;
UPDATE acid_accounts SET balance = balance + 30 WHERE account_id = 2;
ROLLBACK; -- second round changes this to COMMIT

Expected result: after rollback still 100 and 50, after commit 70 and 80. To observe isolation, open two psql sessions on a regular table (a TEMP table is private to each session).

When ACID is not enough

Checklist for a money or inventory operation:

1. The business condition lives in the write statement (WHERE balance >= 30) and the code checks the row count.
2. If an invariant can be expressed, declare a constraint; do not trust every service to check it the same way.
3. Every request has an idempotency key stored in the same transaction.
4. Side effects outside the database go through an outbox or an idempotent mechanism after commit.
5. Keep transactions short: no slow network calls while holding locks.
6. Retry the whole transaction on 40001 or 40P01.

Trade-offs

What transactions help with

• Group several SQL changes into one commit or rollback point.
• Constraints block declared invalid states, no matter which service writes.
• WAL makes committed changes recoverable.

Costs and boundaries

• Long-open transactions hold locks and block cleanup of old versions.
• Stronger isolation brings retries; the default Read Committed does not remove every race.
• Side effects outside PostgreSQL, replicas and ambiguous COMMIT outcomes need their own mechanisms.

Common mistakes

Common pitfall

“I use a transaction, so I don’t need to check row counts.” The balance >= 30 condition can make the debit update 0 rows with no SQL error. Check RETURNING or the row count; if it is not exactly one row, roll back.

Common pitfall

“The C in ACID means PostgreSQL understands every business rule.” The database only protects declared constraints. Code that debits 30 and credits 20 still commits perfectly.

Common pitfall

“A successful COMMIT means the email and the replica are updated.” Durability only covers PostgreSQL data on the primary. Losing the connection right at COMMIT also leaves the client unsure of the outcome.

Summary

Key takeaways

• A: statements in the same transaction commit or roll back as one unit; statements outside it do not.
• C: the schema enforces declared rules; the remaining invariants are the application’s job.
• I: no reads of uncommitted data; other races depend on the isolation level and how the SQL is written.
• D: a commit only reports success after WAL is flushed locally; synchronous_commit = off can lose up to roughly the last 600ms of commits (default).
• An ambiguous failure at COMMIT needs an idempotency key, not a “stronger transaction”.

Self-check: (1) The debit updates 0 rows but the credit still runs: which letter of ACID does not save that logic?

(2) Why should you not resend a transfer request with a new ID after the connection drops right after COMMIT? (3) With synchronous_commit = off, can the database be corrupted after a crash?

References

• Transactions — PostgreSQL 17
• Transaction Isolation — PostgreSQL 17
• Constraints — PostgreSQL 17
• Write-Ahead Logging — PostgreSQL 17
• WAL Configuration — PostgreSQL 17
• Error Codes — PostgreSQL 17