ACID Properties

1. Introduction

ACID is the foundational guarantee that relational databases provide for reliable transaction processing. It stands for Atomicity, Consistency, Isolation, and Durability. Every transaction — whether a simple UPDATE or a multi-table financial transfer — must satisfy all four properties. PostgreSQL implements ACID through WAL (Write-Ahead Logging), MVCC, and constraint enforcement.

2. Why It Matters

Without ACID, banking systems could lose money during crashes, e-commerce platforms could oversell inventory, and healthcare records could become inconsistent mid-update. Understanding ACID lets you reason about failure scenarios and design systems that remain correct even when hardware, networks, or applications fail unexpectedly.

3. Real-World Analogy

Think of a bank wire transfer: money must be debited from Account A and credited to Account B as a single operation. If the system crashes after debiting A but before crediting B, the bank must roll back the debit — otherwise money vanishes. ACID is the contractual guarantee that this all-or-nothing behavior holds, even during power outages or software bugs.

4. How It Works

• Atomicity: All statements in a transaction succeed or all are rolled back. PostgreSQL uses WAL to undo partial changes on failure.
• Consistency: Every transaction moves the database from one valid state to another. CHECK constraints, FK constraints, and triggers enforce business rules.
• Isolation: Concurrent transactions don't see each other's uncommitted changes. PostgreSQL achieves this via MVCC — each transaction sees a snapshot of the database as of its start time.
• Durability: Once COMMIT returns, changes survive crashes. PostgreSQL writes changes to the WAL before acknowledging COMMIT, and the WAL is flushed to disk via fsync().

5. Internal Architecture

When a transaction begins, PostgreSQL assigns it a transaction ID (XID). All modified rows store the XID in system columns xmin and xmax. The WAL records every change before it hits data files. On COMMIT, the WAL is flushed to disk (durability). On ROLLBACK or crash, the WAL is used to undo uncommitted changes (atomicity). The MVCC snapshot mechanism ensures isolation without blocking readers.

6. Visual Explanation

The diagram shows the transaction lifecycle: BEGIN assigns an XID, modifications are written to WAL and buffers, COMMIT flushes WAL to disk, and crash recovery replays committed transactions from the WAL.

7. Practical Example

8. Common Mistakes

• Assuming a single SQL statement is always atomic: Multi-statement operations need explicit BEGIN/COMMIT.
• Ignoring isolation anomalies: READ COMMITTED (PostgreSQL default) allows non-repeatable reads — use REPEATABLE READ or SERIALIZABLE when needed.
• Long-running transactions: Holding a transaction open for minutes bloats tables (dead tuples) and blocks VACUUM.
• Not handling COMMIT failures: Network timeouts after COMMIT can leave the client unsure if the transaction persisted — use idempotent keys or outbox patterns.

9. Quick Quiz

10. Scenario-Based Challenge

Design a transaction for an e-commerce checkout that: (1) decrements product stock, (2) creates an order record, (3) charges the customer's wallet. Ensure that if any step fails, all changes are rolled back. Add a CHECK constraint on stock to prevent negative values, and explain which ACID property enforces it.

11. Debugging Exercise

Root cause: No CHECK constraint enforced the business rule, so the Consistency property was violated. Adding the constraint ensures every transaction maintains valid state.

12. Interview Questions

• Q: Explain the difference between Atomicity and Durability.
  A: Atomicity guarantees that partial transaction changes are rolled back on failure. Durability guarantees that committed changes survive crashes via WAL flush.
• Q: How does PostgreSQL guarantee Consistency?
  A: Through constraints (CHECK, FK, UNIQUE, NOT NULL), triggers, and data types. The database rejects any transaction that would violate these rules.
• Q: What happens to a transaction if the server crashes mid-execution?
  A: On recovery, PostgreSQL replays the WAL. Uncommitted transactions are not found in the commit log, so their changes are effectively invisible — crash recovery rolls them forward to a consistent state.

13. Production Considerations

• synchronous_commit: Setting to off improves throughput but risks losing the last few transactions on crash (durability trade-off).
• WAL archiving: Enable archive_mode for point-in-time recovery (PITR) — critical for production databases.
• Transaction ID wraparound: PostgreSQL XIDs are 32-bit. Ensure autovacuum runs regularly to freeze old tuples and prevent wraparound shutdowns.
• Two-Phase Commit (2PC): Use PREPARE TRANSACTION for distributed transactions across multiple databases.