ACID Properties Explained: What Database Transactions Actually Guarantee

Transactions Are Promises Your Database Makes

Every time you run a bank transfer, place an order, or update inventory, you're relying on ACID properties to keep your data correct. ACID -- Atomicity, Consistency, Isolation, Durability -- is the set of guarantees that database transactions provide. Without them, you get corrupted balances, phantom inventory, and data that quietly drifts into an inconsistent state.

Most developers can recite the acronym. Far fewer understand what each property actually means in practice, especially isolation levels. This guide breaks down each ACID property with concrete PostgreSQL examples, shows you the anomalies that occur when isolation is weakened, and explains why "Serializable" isn't always the right answer.

What Are ACID Properties?

Definition: ACID properties are four guarantees that database transactions provide: Atomicity (all-or-nothing execution), Consistency (data stays valid per defined constraints), Isolation (concurrent transactions don't interfere), and Durability (committed data survives crashes). Together, they ensure reliable data processing.

Atomicity: All or Nothing

Atomicity guarantees that a transaction either completes entirely or has no effect. If any statement in a transaction fails, all preceding statements in that transaction are rolled back. There's no partial execution.

-- Transfer $500 from account A to account B
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 'A';
UPDATE accounts SET balance = balance + 500 WHERE id = 'B';
COMMIT;

-- If the second UPDATE fails (say, account B doesn't exist),
-- the first UPDATE is rolled back. Account A keeps its money.

-- Without atomicity, you'd need to manually reverse the first update:
-- UPDATE accounts SET balance = balance + 500 WHERE id = 'A';
-- This is error-prone and doesn't handle crashes mid-operation.

Atomicity seems simple until you consider crashes. What if the server loses power between the two UPDATEs? PostgreSQL's WAL (Write-Ahead Log) handles this: changes are written to the WAL before being applied. On recovery, PostgreSQL replays committed transactions and discards uncommitted ones. You never get a half-applied transaction.

Consistency: Rules That Can't Be Broken

Consistency means that every transaction moves the database from one valid state to another. "Valid" is defined by your constraints: primary keys, foreign keys, unique constraints, check constraints, and triggers.

-- Consistency via constraints
CREATE TABLE accounts (
  id TEXT PRIMARY KEY,
  balance NUMERIC NOT NULL CHECK (balance >= 0),
  owner_id INTEGER REFERENCES users(id)
);

-- This transaction violates the CHECK constraint
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
-- If A's balance is 500, this fails with:
-- ERROR: new row for relation "accounts" violates check constraint
-- The entire transaction is aborted.
ROLLBACK;

Consistency is partly the database's job (enforcing constraints) and partly yours (defining correct constraints). A database with no constraints will happily store negative balances, orphaned records, and duplicate entries. ACID's consistency guarantee only works if you've told the database what "valid" means.

Pro tip: Don't enforce business rules in application code alone. If two services can write to the same table, an application-level check can race. A database CHECK constraint or UNIQUE index is enforced atomically by the database engine, regardless of which client issues the write.

Isolation: The Most Misunderstood Property

Isolation determines how concurrent transactions interact. Perfect isolation means every transaction runs as if it were the only one -- but perfect isolation is expensive. So databases offer levels of isolation, each allowing certain anomalies in exchange for better performance.

The Four Isolation Levels

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read	Serialization Anomaly
Read Uncommitted	Possible	Possible	Possible	Possible
Read Committed	Prevented	Possible	Possible	Possible
Repeatable Read	Prevented	Prevented	Possible*	Possible
Serializable	Prevented	Prevented	Prevented	Prevented

*PostgreSQL's Repeatable Read actually prevents phantom reads too, thanks to its MVCC implementation. The SQL standard allows phantoms at this level, but PostgreSQL is stricter.

Anomaly 1: Dirty Read

A dirty read occurs when a transaction reads data written by another transaction that hasn't committed yet. If that other transaction rolls back, you've read data that never existed.

-- Transaction A                    -- Transaction B
BEGIN;                              BEGIN;
UPDATE accounts SET balance = 0
  WHERE id = 'A';
                                    -- Dirty read: sees balance = 0
                                    -- even though A hasn't committed
                                    SELECT balance FROM accounts
                                      WHERE id = 'A';
ROLLBACK;  -- A rolls back!
                                    -- B acted on data that never existed

PostgreSQL never allows dirty reads, even at "Read Uncommitted" level. It silently upgrades to Read Committed. This is a good thing.

Anomaly 2: Non-Repeatable Read

A non-repeatable read happens when a transaction reads the same row twice and gets different values because another transaction modified and committed it in between.

-- Transaction A (Read Committed)    -- Transaction B
BEGIN;
SELECT balance FROM accounts
  WHERE id = 'A';  -- Returns 1000
                                     BEGIN;
                                     UPDATE accounts SET balance = 500
                                       WHERE id = 'A';
                                     COMMIT;
SELECT balance FROM accounts
  WHERE id = 'A';  -- Returns 500!
-- Same query, different result within the same transaction
COMMIT;

Anomaly 3: Phantom Read

A phantom read occurs when a transaction re-executes a query and finds new rows that were inserted by another committed transaction.

-- Transaction A (Read Committed)    -- Transaction B
BEGIN;
SELECT COUNT(*) FROM orders
  WHERE status = 'pending';  -- 10
                                     BEGIN;
                                     INSERT INTO orders (status)
                                       VALUES ('pending');
                                     COMMIT;
SELECT COUNT(*) FROM orders
  WHERE status = 'pending';  -- 11!
-- A "phantom" row appeared
COMMIT;

Anomaly 4: Serialization Anomaly (Write Skew)

This is the most subtle anomaly. Two transactions read overlapping data, make decisions based on what they read, and write results that conflict -- but neither sees the other's write.

-- Rule: at least one doctor must be on-call at all times
-- Two doctors are on-call. Both try to go off-call simultaneously.

-- Transaction A (Repeatable Read)   -- Transaction B (Repeatable Read)
BEGIN;                               BEGIN;
SELECT COUNT(*) FROM doctors
  WHERE on_call = true;  -- 2
                                     SELECT COUNT(*) FROM doctors
                                       WHERE on_call = true;  -- 2
-- "2 on-call, safe to remove one"
UPDATE doctors SET on_call = false
  WHERE name = 'Alice';
                                     -- "2 on-call, safe to remove one"
                                     UPDATE doctors SET on_call = false
                                       WHERE name = 'Bob';
COMMIT;                              COMMIT;
-- Result: 0 doctors on-call! Both transactions saw 2 and thought it was safe.

Only Serializable isolation prevents write skew. At Repeatable Read, both transactions succeed because they modified different rows.

Choosing the Right Isolation Level

1. Start with Read Committed (PostgreSQL's default). It's correct for the vast majority of web application workloads.
2. Use Repeatable Read when you need a consistent snapshot for the duration of a transaction -- reporting queries, batch jobs, or any logic that reads the same data multiple times.
3. Use Serializable only when you need to prevent write skew and can handle retry logic. Serializable transactions may fail with serialization errors that your application must catch and retry.
4. Never use Read Uncommitted in PostgreSQL -- it's treated as Read Committed anyway. In other databases (MySQL, SQL Server), avoid it entirely except for rough approximations on read-only analytics.

-- Set isolation level per transaction
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... your queries ...
COMMIT;

-- Set default for the session
SET default_transaction_isolation = 'repeatable read';

-- Handle serialization failures in your application
-- Pseudocode:
-- while True:
--   try:
--     execute_transaction()
--     break
--   except SerializationFailure:
--     continue  # retry

Watch out: Serializable isolation in PostgreSQL uses predicate locks (SIRead locks) that consume memory. Under high concurrency, you may see increased memory usage and more serialization failures. Monitor pg_stat_activity for transactions waiting on serialization locks, and make sure your retry logic includes a limit to prevent infinite loops.

Durability: Surviving Crashes

Durability guarantees that once a transaction commits, its changes survive any subsequent failure -- power loss, OS crash, hardware failure. PostgreSQL achieves this through the WAL: data is written to the WAL and fsynced to disk before the COMMIT returns success to the client.

-- Durability settings in postgresql.conf
fsync = on                  -- Never turn this off in production
synchronous_commit = on     -- Wait for WAL fsync before acknowledging commit
wal_sync_method = fdatasync -- OS-level sync method

-- You CAN relax durability per-transaction for non-critical writes:
SET LOCAL synchronous_commit = off;
INSERT INTO page_views (url, viewed_at) VALUES ('/home', NOW());
-- This returns immediately without waiting for WAL fsync
-- A crash in the next few hundred milliseconds could lose this insert

With fsync = on and synchronous_commit = on, PostgreSQL guarantees that a committed transaction is on disk. With replication and synchronous_commit = remote_apply, it's on disk on multiple machines. This is as durable as it gets.

ACID in Practice: PostgreSQL vs Other Databases

Database	Default Isolation	Full ACID?	Notes
PostgreSQL	Read Committed	Yes	MVCC-based, Serializable via SSI
MySQL (InnoDB)	Repeatable Read	Yes	Gap locking prevents phantoms
SQL Server	Read Committed	Yes	Lock-based or RCSI (snapshot)
MongoDB	Read Uncommitted*	Partial (4.0+)	Multi-document ACID since 4.0
DynamoDB	Eventual	Partial	TransactWriteItems for ACID
CockroachDB	Serializable	Yes	Distributed Serializable by default

Managed PostgreSQL: Pricing for ACID-Compliant Workloads

ACID compliance requires reliable storage and proper fsync behavior. Managed providers handle this, but pricing varies. For a production-grade setup (4 vCPU, 16 GB RAM, 100 GB SSD, HA enabled):

Provider	Monthly Cost	HA Failover	Point-in-Time Recovery
AWS RDS	~$380 (Multi-AZ)	60-120s	5-minute granularity
Google Cloud SQL	~$350 (HA)	~60s	Configurable
Neon	$69 (Scale)	Instant (compute)	Branch-based
Supabase	$25 (Pro)	Automatic	Daily backups
PlanetScale (MySQL)	$39 (Scaler)	Automatic	Continuous

Practical Takeaways

Here's what matters for day-to-day development:

1. Use transactions for multi-step operations -- don't rely on application-level rollback logic
2. Define constraints in the database -- CHECK, UNIQUE, FOREIGN KEY constraints are your consistency safety net
3. Read Committed is fine for most workloads -- upgrade to Repeatable Read or Serializable only when you've identified a specific anomaly
4. If you use Serializable, implement retry logic -- serialization failures are expected, not errors
5. Never disable fsync in production -- the performance gain isn't worth the data loss risk
6. Test concurrent scenarios -- write skew and phantom reads don't show up in single-user testing

Frequently Asked Questions

What does ACID stand for in databases?

ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity ensures transactions are all-or-nothing. Consistency ensures data meets defined constraints. Isolation controls how concurrent transactions interact. Durability guarantees committed data survives crashes. Together, they define the reliability guarantees of a database transaction.

What is the default isolation level in PostgreSQL?

PostgreSQL defaults to Read Committed. Each statement within a transaction sees a snapshot of data as of the start of that statement -- not the start of the transaction. This means consecutive SELECTs can see different data if another transaction commits between them. For a transaction-level snapshot, use Repeatable Read.

What is a dirty read and does PostgreSQL allow it?

A dirty read occurs when a transaction reads uncommitted changes from another transaction. PostgreSQL never allows dirty reads, even if you explicitly set Read Uncommitted. It silently upgrades to Read Committed. This is safer than MySQL or SQL Server where Read Uncommitted actually permits dirty reads.

When should I use Serializable isolation?

Use Serializable when your application has write-write dependencies that can cause write skew -- for example, enforcing invariants like "at least one doctor on-call" or "total allocated budget must not exceed limit." Be prepared to handle serialization failures with retry logic. Don't use it as a default; the performance cost is significant under high concurrency.

Is MongoDB ACID-compliant?

Since version 4.0, MongoDB supports multi-document ACID transactions. Single-document operations were always atomic. However, MongoDB's default read concern is "local" (similar to Read Uncommitted), and transactions have performance overhead. PostgreSQL provides stronger ACID guarantees out of the box with less configuration required.

What happens if I turn off fsync in PostgreSQL?

Turning off fsync removes the durability guarantee. PostgreSQL will report transactions as committed before data reaches disk. A power failure or OS crash can corrupt your database entirely -- not just lose recent transactions, but potentially render the whole cluster unrecoverable. Never disable fsync in production. Use synchronous_commit = off per-transaction for non-critical writes instead.

How do isolation levels affect database performance?

Higher isolation levels require more bookkeeping. Read Committed is cheapest because snapshots are per-statement. Repeatable Read holds a snapshot for the entire transaction, consuming more memory for long transactions. Serializable adds predicate locks to detect conflicts, which increases memory usage and can cause transaction failures under high concurrency. Most applications perform well at Read Committed.