ACID Transactions

TL;DR

ACID is a set of guarantees that database transactions provide: Atomicity (all-or-nothing), Consistency (constraints preserved), Isolation (concurrent transactions don’t interfere), and Durability (committed = permanent). These guarantees simplify application development by letting you reason about operations as atomic units.

Visual Overview

ACID Properties

The Four Properties

Atomicity: All or Nothing

A transaction is an indivisible unit—either all operations succeed, or all fail.

Example: Bank Transfer

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;

If the system crashes after the first UPDATE but before COMMIT:

With atomicity: Both updates rolled back, money intact
Without atomicity: Account A debited, Account B not credited

Implementation: Write-Ahead Log (WAL) records changes before applying them. On crash, replay or undo based on WAL state.

Consistency: Constraints Preserved

Transactions transition the database from one valid state to another. All constraints (foreign keys, unique, check) are enforced.

Example: Referential Integrity

-- orders.user_id must reference existing user
INSERT INTO orders (user_id, amount) VALUES (999, 50.00);
-- Fails if user 999 doesn't exist

Note: Application-level consistency (business rules) is your responsibility. The database only enforces declared constraints.

Isolation: Concurrent Transactions Don’t Interfere

Even when transactions run concurrently, the result is as if they ran serially.

Example: Preventing Double-Booking

-- Transaction A                    -- Transaction B
BEGIN;                              BEGIN;
SELECT * FROM rooms                 SELECT * FROM rooms
  WHERE available = true;             WHERE available = true;
-- Both see Room 1 available        -- Both see Room 1 available

UPDATE rooms SET available = false  UPDATE rooms SET available = false
  WHERE id = 1;                       WHERE id = 1;
COMMIT;                             COMMIT; -- One must fail!

Isolation Levels (trade-off: correctness vs performance):

Level	Dirty Read	Non-Repeatable Read	Phantom
Read Uncommitted	Possible	Possible	Possible
Read Committed	Prevented	Possible	Possible
Repeatable Read	Prevented	Prevented	Possible
Serializable	Prevented	Prevented	Prevented

Durability: Committed = Permanent

Once a transaction commits, its changes survive any subsequent failure.

Implementation:

Write to WAL (Write-Ahead Log) before responding
fsync() to force data to disk
On crash recovery, replay committed transactions from WAL

Trade-off: fsync() is slow (~10ms). Options:

Synchronous: Wait for disk, guaranteed durability
Asynchronous: Risk losing recent commits on crash

ACID in Practice

PostgreSQL Transaction

BEGIN;

-- All operations in one atomic unit
INSERT INTO orders (user_id, total) VALUES (1, 99.99);
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 123;
INSERT INTO payments (order_id, amount) VALUES (currval('orders_id_seq'), 99.99);

COMMIT;  -- All succeed, or all fail

Application Code Pattern

def transfer_money(from_account, to_account, amount):
    with db.transaction():  # BEGIN
        from_balance = db.query(
            "SELECT balance FROM accounts WHERE id = ?", from_account
        )
        if from_balance < amount:
            raise InsufficientFunds()

        db.execute(
            "UPDATE accounts SET balance = balance - ? WHERE id = ?",
            amount, from_account
        )
        db.execute(
            "UPDATE accounts SET balance = balance + ? WHERE id = ?",
            amount, to_account
        )
    # COMMIT happens automatically at end of 'with' block
    # ROLLBACK if exception raised

When ACID Isn’t Enough

Distributed Transactions

ACID within a single database is well-solved. Across databases? Much harder.

Approach	Trade-off
Two-Phase Commit (2PC)	Blocking, single point of failure
Saga Pattern	Eventual consistency, compensation logic
Event Sourcing	Append-only, rebuild state from events

Performance at Scale

Strict ACID limits throughput:

Locks reduce concurrency
fsync() adds latency
Cross-partition transactions coordinate

Solutions:

Lower isolation levels where safe
Partition data to avoid distributed transactions
Use eventual consistency where appropriate (BASE)

ACID vs BASE

For distributed systems that prioritize availability:

Property	ACID	BASE
Consistency	Strong (immediate)	Eventual
Availability	May sacrifice	Highly available
State	Always valid	Soft state (may change)
Use Case	Banking, payments	Social media, analytics

Related Concepts:

Write-Ahead Log - How durability is implemented
Exactly-Once Semantics - Transaction guarantees in streaming
Consensus - Agreement for distributed transactions

Used In Systems:

PostgreSQL, MySQL, SQL Server (single-node ACID)
Google Spanner, CockroachDB (distributed ACID)
Kafka (transactional messaging)

Explained In Detail:

Database Internals Deep Dive - How WAL, isolation, and durability work under the hood

Next Recommended: Write-Ahead Log - Learn how databases implement durability