ACID Properties Explained: Database Transactions

ACID properties are four guarantees — Atomicity, Consistency, Isolation, and Durability — that ensure database transactions are processed reliably even when things go wrong. Without them, a server crash mid-transaction could leave your data half-written, two concurrent users could corrupt each other's updates, and committed data could vanish.

You're transferring money. The debit happens. Then your server crashes. Did the credit happen too?

Without ACID guarantees, you'd have to hope so. With them, either both happen or neither does.

What Is ACID?

ACID describes four properties that guarantee database transactions are processed reliably. It's not a new technology — it's a set of requirements that databases like PostgreSQL, MySQL, and Oracle implement.

ACID = Atomicity, Consistency, Isolation, Durability.

Each property addresses a different failure mode. Together they make transactions the fundamental unit of reliability in relational databases.

Atomicity: All or Nothing

A transaction either completes fully or not at all. No partial states.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Debit Alice
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Credit Bob
COMMIT;

If the second UPDATE fails (Bob's account doesn't exist), the database rolls back both changes. Alice keeps her $100. No money disappears.

Without atomicity: Alice loses $100, Bob gets nothing.

-- Explicit rollback on error
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  
  -- Something goes wrong
  ROLLBACK;  -- First UPDATE is undone

PostgreSQL tracks every change made during a transaction in memory (and WAL). ROLLBACK reverses all of them in one shot. From the database's perspective, the transaction never happened.

Savepoints give you partial rollback within a transaction:

BEGIN;
  INSERT INTO orders (user_id, total) VALUES (42, 200.00);
  SAVEPOINT order_created;
  
  INSERT INTO order_items (order_id, product_id) VALUES (1, 99);  -- This fails
  ROLLBACK TO SAVEPOINT order_created;  -- Undo only the item insert
  
  -- Order still exists, items rolled back
COMMIT;

Consistency: Rules Are Always Satisfied

A transaction moves the database from one valid state to another. It can't break your defined rules.

-- Define a constraint: balance can't go below 0
ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);
 
BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- Alice has $100
  -- This fails: CHECK constraint violated (balance would be -400)
  -- Transaction rolls back automatically
COMMIT;

Consistency means your data invariants are always true:

• Account balances stay non-negative
• Foreign keys are always valid
• Unique constraints are always respected

The database enforces this, not just your application code. Even if your application has a bug that tries to create an orphaned order with no valid user, the foreign key constraint will reject it.

-- Foreign key constraint enforces referential integrity
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT;
 
-- This will fail if user 999 doesn't exist
INSERT INTO orders (user_id, total) VALUES (999, 100.00);
-- ERROR: insert or update on table "orders" violates foreign key constraint

Isolation: Concurrent Transactions Don't Interfere

Multiple transactions running at the same time see consistent data, as if they ran one after another.

Without isolation, you get anomalies:

Dirty read: Transaction A reads data that Transaction B has modified but not committed yet. B then rolls back. A read data that never existed.

Non-repeatable read: Transaction A reads a row. Transaction B updates that row and commits. A reads it again and gets a different value within the same transaction.

Phantom read: Transaction A queries rows matching a condition. Transaction B inserts a new matching row and commits. A runs the same query and gets a different result set.

-- PostgreSQL isolation levels (increasing strictness)
 
-- READ COMMITTED (default): Only sees committed data. Prevents dirty reads.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
-- REPEATABLE READ: Same row reads return same data within transaction. Prevents dirty + non-repeatable reads.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
-- SERIALIZABLE: Fully isolated. Behaves as if transactions ran sequentially.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Higher isolation = fewer anomalies = more locking = lower throughput. Most apps work fine with READ COMMITTED (default).

Use SERIALIZABLE for financial transactions where even phantom reads could cause problems (like double-spending attacks).

Durability: Committed Data Survives Crashes

Once a transaction commits, the data is permanently saved — even if the server crashes immediately after.

PostgreSQL achieves this with a Write-Ahead Log (WAL). Before writing to the actual data files, it writes the change to a log that's flushed to disk. On crash recovery, it replays the log.

BEGIN;
  INSERT INTO orders (user_id, total) VALUES (123, 100.00);
COMMIT;  -- At this point, data is on disk. Server crash won't lose it.

Without durability: you'd need to verify every write actually persisted.

WAL also enables replication — standbys replay the same log stream, staying in sync with the primary. This is why PostgreSQL streaming replication is reliable: it's built on the same durability mechanism.

ACID in Practice: PostgreSQL Examples

Here's a complete transfer function showing all four properties in action:

import psycopg2
 
def transfer_money(from_id: int, to_id: int, amount: float):
    conn = psycopg2.connect("postgresql://localhost/mydb")
    
    try:
        with conn:  # Context manager handles BEGIN/COMMIT/ROLLBACK
            with conn.cursor() as cur:
                # Check balance (read within transaction)
                cur.execute(
                    "SELECT balance FROM accounts WHERE id = %s FOR UPDATE",
                    (from_id,)
                )
                balance = cur.fetchone()[0]
                
                if balance < amount:
                    raise ValueError("Insufficient funds")
                
                # Debit
                cur.execute(
                    "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                    (amount, from_id)
                )
                
                # Credit
                cur.execute(
                    "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                    (amount, to_id)
                )
                
                # COMMIT happens automatically when context manager exits normally
                
    except Exception:
        # ROLLBACK happens automatically on exception
        raise
    finally:
        conn.close()

FOR UPDATE locks the row so no other transaction can modify it while you're reading and about to write. Without this lock, two concurrent transfers from the same account could both read the same balance, both decide it's sufficient, and both proceed — resulting in a balance going negative even with a CHECK constraint.

Transaction Locking in PostgreSQL

PostgreSQL uses row-level locking by default. When you do SELECT ... FOR UPDATE, you acquire an exclusive lock on that row. Other transactions trying to update that row will wait until your transaction commits or rolls back.

-- Session 1: Acquires lock on account 1
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- balance = 500, lock held
 
-- Session 2: Waits here
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Blocked until Session 1 commits or rolls back
 
-- Session 1: Commits, releases lock
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
 
-- Session 2: Now unblocked, reads the updated balance (400)

FOR SHARE acquires a shared lock — multiple readers can hold it simultaneously, but writers must wait:

-- Multiple reads can proceed concurrently
SELECT * FROM products WHERE category = 'electronics' FOR SHARE;
 
-- But this will wait until all FOR SHARE locks are released
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

Isolation Levels Explained

Isolation is not binary — it's a spectrum. SQL standard defines four levels, each preventing a different class of anomaly.

Read Uncommitted

Allows dirty reads. Transactions can see uncommitted changes from other transactions. Almost never used in practice because the data you read might be rolled back.

PostgreSQL doesn't actually implement this level — it silently upgrades it to Read Committed.

-- Session 1
BEGIN;
UPDATE products SET price = 999.99 WHERE id = 1;
-- NOT committed yet
 
-- Session 2 (at READ UNCOMMITTED)
SELECT price FROM products WHERE id = 1;
-- In a true READ UNCOMMITTED db, could return 999.99
-- In PostgreSQL, returns the last committed value (ACID enforced anyway)

Read Committed (PostgreSQL Default)

Each statement sees only data committed before that statement began. Prevents dirty reads. Does NOT prevent non-repeatable reads.

-- Session 1
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- Returns 500
 
-- Session 2 (commits while Session 1 is mid-transaction)
UPDATE accounts SET balance = 600 WHERE id = 1;
COMMIT;
 
-- Session 1 (same transaction, second read)
SELECT balance FROM accounts WHERE id = 1;
-- Returns 600! (non-repeatable read — different value than first read)
COMMIT;

This is usually acceptable. Your individual statements are consistent. The window for seeing stale data between statements is brief.

Repeatable Read

A snapshot of the database is taken at the start of the transaction. All reads within the transaction see data as of that snapshot, even if other transactions commit in the meantime. Prevents dirty reads and non-repeatable reads.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
-- Session 1
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- Returns 500 (snapshot taken here)
 
-- Session 2 commits a change to account 1 while Session 1 is running
UPDATE accounts SET balance = 600 WHERE id = 1;
COMMIT;
 
-- Session 1 (same transaction, second read)
SELECT balance FROM accounts WHERE id = 1;
-- Still returns 500! Snapshot is preserved.
COMMIT;

Phantom reads are also prevented in PostgreSQL's implementation of Repeatable Read (PostgreSQL's MVCC goes further than the SQL standard requires at this level).

Serializable

The strictest level. Transactions execute as if they were fully sequential — no two transactions run truly concurrently from the data's perspective. Prevents all anomalies including phantom reads and write skew.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
-- Classic write skew example: two doctors trying to go off-call
-- Both check "is there at least one doctor on call?"
-- Both see yes. Both go off-call. No doctor on call. Problem.
 
-- With SERIALIZABLE, one transaction gets serialization failure:
-- ERROR: could not serialize access due to read/write dependencies
-- Application must retry the failed transaction.

Anomaly prevention by level:

*PostgreSQL prevents phantom reads at Repeatable Read due to its MVCC implementation.

ACID vs BASE

NoSQL databases often trade ACID for performance and scale. BASE is the opposing philosophy:

BASE = Basically Available, Soft state, Eventually consistent.

*MongoDB added multi-document ACID transactions in v4.0, but it's opt-in and has performance costs.

When to Choose ACID

• Money moves: payments, transfers, ledger entries — partial transactions cause real harm
• Inventory: overselling because two transactions both see "1 in stock"
• Coordinated writes: user creation + profile creation + default settings — all succeed or none do
• Regulatory requirements: financial and healthcare systems often require ACID by regulation

When to Choose Eventual Consistency (BASE)

• High-volume reads with tolerance for slight staleness: product catalog, social feeds, leaderboards
• Geographic distribution: users in multiple regions, where strong consistency would require cross-region coordination (high latency)
• Write-heavy workloads at massive scale: Cassandra can absorb millions of writes/second per node precisely because it doesn't coordinate
• Analytics and aggregation: running totals, view counts, recommendation scores — being off by a few is fine

The key question: what's the cost of briefly showing stale or conflicting data? For bank balances, catastrophic. For Instagram likes, imperceptible.

Transaction Isolation in Real Applications

Choosing the Right Isolation Level

Most applications can use READ COMMITTED (PostgreSQL's default) for the vast majority of operations.

-- You can set it per transaction, not globally
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ... your queries
COMMIT;
 
-- Or set a default for the session
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Use READ COMMITTED when:

• Simple CRUD operations with no read-modify-write patterns
• Queries where seeing the absolute latest committed value is fine
• High-throughput applications (default is fastest)

Use REPEATABLE READ when:

• Generating reports that query multiple tables and need consistent data across all reads
• Running aggregations where you need all rows counted from the same point in time
• Any transaction that reads data twice and makes decisions based on both reads

Use SERIALIZABLE when:

• Financial transactions with complex logic (double-spending prevention)
• Scheduling systems where two concurrent bookings could conflict
• Any scenario where write skew could occur (two transactions each checking a condition and acting on it)

Performance Implications

Higher isolation levels have real costs:

• READ COMMITTED: PostgreSQL takes a snapshot per statement. Minimal overhead.
• REPEATABLE READ: Snapshot taken at transaction start. Older snapshots mean more MVCC versions to maintain. Long transactions at this level can cause table bloat.
• SERIALIZABLE: PostgreSQL tracks dependencies between transactions using predicate locking. Additional memory and CPU overhead. Transactions may fail and need retrying.

For SERIALIZABLE, always implement retry logic:

import psycopg2
from psycopg2 import errors
import time
 
def run_serializable(conn, fn, max_retries=3):
    for attempt in range(max_retries):
        try:
            with conn:
                with conn.cursor() as cur:
                    cur.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                    return fn(cur)
        except errors.SerializationFailure:
            if attempt == max_retries - 1:
                raise
            time.sleep(0.1 * (2 ** attempt))  # Exponential backoff

Two-Phase Commit: ACID Across Services

ACID works within a single database. But modern architectures often span multiple databases or services. This is where things break down.

Why ACID Breaks in Distributed Systems

Consider an e-commerce checkout that must:

1. Deduct inventory (inventory service, PostgreSQL)
2. Charge the customer (payment service, Stripe)
3. Create the order (order service, PostgreSQL)

You can't wrap all three in a single BEGIN/COMMIT — they're in different databases, different services, different networks. Any of them can fail independently.

The Two-Phase Commit Protocol (2PC)

2PC is the classical solution. It adds a coordinator that orchestrates the commit across all participants:

Phase 1 — Prepare:

Coordinator → Inventory DB: "Prepare to commit (deduct 1 unit of SKU-42)"
Coordinator → Order DB:     "Prepare to commit (create order #789)"
Inventory DB → Coordinator: "Ready" (locked, not committed)
Order DB → Coordinator:     "Ready" (locked, not committed)
Payment API:                 "Charge will succeed" (authorization held)

Phase 2 — Commit (or Abort):

-- If all say Ready:
Coordinator → All: "Commit"
All participants commit and release locks.
 
-- If any say No/Timeout:
Coordinator → All: "Abort"
All participants roll back.

Limitations of 2PC

2PC works but has serious drawbacks in practice:

Blocking: If the coordinator crashes after Phase 1 but before Phase 2, all participants are stuck holding locks indefinitely — waiting for a coordinator that may never come back.

Latency: Every commit requires two network round trips minimum. In distributed systems with high latency between services, this compounds.

Vendor support: Not all databases or APIs support 2PC. Stripe doesn't give you a "prepare to charge" endpoint.

What Microservices Actually Use

Most teams avoid 2PC and instead use:

Saga pattern: Break the distributed transaction into a sequence of local transactions. Each step has a compensating transaction (undo). If step 3 fails, you run the compensating transactions for steps 2 and 1.

# Saga for checkout
def checkout_saga(order):
    try:
        inventory_id = inventory_service.reserve(order.items)          # Step 1
        payment_id = payment_service.charge(order.user, order.total)   # Step 2
        order_id = order_service.create(order, payment_id)             # Step 3
        inventory_service.confirm(inventory_id)                        # Step 4
    except PaymentFailed:
        inventory_service.release(inventory_id)                        # Compensate step 1
        raise
    except OrderCreationFailed:
        payment_service.refund(payment_id)                             # Compensate step 2
        inventory_service.release(inventory_id)                        # Compensate step 1
        raise

Outbox pattern: Write events to an outbox table in the same database transaction as your data change. A separate process publishes those events reliably. Guarantees at-least-once delivery without distributed transactions.

BEGIN;
  INSERT INTO orders (user_id, total) VALUES (42, 200.00) RETURNING id;
  INSERT INTO outbox (event_type, payload) VALUES ('order.created', '{"order_id": 123}');
COMMIT;
-- Both rows committed atomically. Outbox processor publishes the event.

Common ACID Pitfalls

Long-Running Transactions

Long transactions hold locks. Other transactions wait. Throughput drops — and in extreme cases, deadlocks cascade.

# Bad: holding transaction open while doing slow external work
with db.transaction():
    order = db.create_order(...)
    email_service.send_confirmation(order)  # Network call inside transaction!
    payment_service.charge(order)           # Another network call!
 
# Good: do DB work only inside transaction
order = db.create_order(...)  # Quick, inside transaction implicitly
# Then do external calls outside the transaction
email_service.send_confirmation(order)
payment_service.charge(order)

In PostgreSQL, long-running transactions also prevent VACUUM from reclaiming dead rows — causing table bloat over time. Monitor pg_stat_activity for transactions older than a few seconds:

-- Find long-running transactions
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
  AND state != 'idle';

Lock Contention

When many transactions compete for the same rows, they queue up. This is normal and expected — but worth profiling.

-- See what's waiting for locks
SELECT
  blocked.pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

Reduce lock contention by:

• Keeping transactions short (acquire locks late, release early)
• Using SELECT ... FOR UPDATE SKIP LOCKED for queue-like patterns (job processing)
• Partitioning hot rows — instead of one "account balance" row, use an append-only ledger

Deadlocks

Two transactions each waiting for a lock held by the other. PostgreSQL detects these and kills one transaction automatically.

-- Session 1                        -- Session 2
BEGIN;                               BEGIN;
UPDATE accounts SET balance = 1      UPDATE accounts SET balance = 2
  WHERE id = 1;  -- Locks row 1        WHERE id = 2;  -- Locks row 2
UPDATE accounts SET balance = 1      UPDATE accounts SET balance = 2
  WHERE id = 2;  -- Waits for row 2    WHERE id = 1;  -- Waits for row 1
-- DEADLOCK detected. One session gets:
-- ERROR:  deadlock detected
-- DETAIL: Process N waits for ShareLock on transaction...

Prevention: always acquire locks in a consistent order. If your application always updates account with lower ID first, deadlocks in the above pattern become impossible.

def transfer_money(from_id, to_id, amount):
    # Always lock lower ID first to prevent deadlocks
    first_id, second_id = min(from_id, to_id), max(from_id, to_id)
    
    with conn.cursor() as cur:
        cur.execute("SELECT id, balance FROM accounts WHERE id IN %s FOR UPDATE ORDER BY id",
                    ((first_id, second_id),))
        # Now safely update both

Key Takeaways

• Atomicity: all steps succeed or none do — no partial transactions
• Consistency: database constraints are always enforced — not just application-level
• Isolation: concurrent transactions don't see each other's partial work
• Durability: committed data survives crashes (WAL in PostgreSQL)
• Higher isolation levels prevent more anomalies but reduce throughput
• READ COMMITTED (default) is fine for most apps; use SERIALIZABLE for financial operations
• Keep transactions short — long transactions hold locks and hurt throughput
• NoSQL databases often trade ACID for availability and speed (BASE)
• Distributed systems can't use a single database transaction — use Sagas or the Outbox pattern instead
• Always acquire locks in consistent order to prevent deadlocks

ACID is what lets you sleep at night when your app handles money. Use a database that supports it for anything where partial writes cause harm.

FAQ

What are the ACID properties in databases?

ACID stands for Atomicity, Consistency, Isolation, and Durability. These four properties guarantee that database transactions are processed reliably. Atomicity ensures all steps in a transaction either complete or none do. Consistency ensures transactions leave the database in a valid state. Isolation ensures concurrent transactions don't interfere with each other. Durability ensures committed data survives system failures.

What is atomicity in a database transaction?

Atomicity means a transaction is treated as a single indivisible unit — it either completes fully or has no effect at all. If any part of a transaction fails (a network error, a constraint violation, an application exception), the database automatically rolls back all changes made so far. This prevents partial writes that leave your data in an inconsistent state.

What is the difference between ACID and BASE?

ACID prioritizes consistency and correctness — every read gets accurate, up-to-date data, and transactions either fully succeed or fully fail. BASE (Basically Available, Soft state, Eventually consistent) prioritizes availability and performance — the system always responds, but data might be temporarily stale or inconsistent across nodes, eventually converging to a consistent state. ACID is suited for financial systems and inventory management. BASE suits social feeds, analytics, and globally distributed reads where brief staleness is acceptable.

What are database isolation levels?

Isolation levels control how much a transaction is shielded from changes made by concurrent transactions. The four standard levels (from weakest to strongest) are: Read Uncommitted (allows dirty reads), Read Committed (prevents dirty reads, PostgreSQL's default), Repeatable Read (prevents non-repeatable reads), and Serializable (prevents all anomalies including phantom reads and write skew). Higher levels prevent more problems but have higher performance overhead and may cause transactions to fail and need retrying.

What is a dirty read and how do I prevent it?

A dirty read occurs when transaction A reads data that transaction B has modified but not yet committed. If B then rolls back, A has read data that never officially existed. In PostgreSQL, dirty reads cannot happen even at the lowest effective isolation level (Read Committed) — the database only shows you committed data. If you're using a database that allows dirty reads, set your isolation level to Read Committed or higher.

Does PostgreSQL support full ACID compliance?

Yes. PostgreSQL is fully ACID compliant. It implements all four properties: atomicity via its transaction system and rollback mechanism, consistency via constraints (CHECK, FOREIGN KEY, UNIQUE, NOT NULL), isolation via MVCC (Multiversion Concurrency Control) with four configurable isolation levels, and durability via Write-Ahead Logging (WAL) that ensures committed data is flushed to disk before the commit is acknowledged. PostgreSQL's MVCC implementation also means readers don't block writers and writers don't block readers, giving it strong concurrency characteristics alongside full ACID compliance.

How do microservices handle ACID transactions?

Microservices can't use a single database transaction across services — each service has its own database. The two main patterns are: (1) Saga pattern — break the distributed transaction into a sequence of local transactions, with compensating transactions to undo completed steps if a later step fails; (2) Outbox pattern — write events to a local outbox table in the same database transaction as your data change, then have a separate process reliably publish those events. Two-Phase Commit (2PC) is also theoretically possible but rarely used in practice due to blocking issues if the coordinator fails and limited support in modern APIs and services.

Related reading: Database Indexing Explained · CAP Theorem Explained · Database Sharding Explained