ACID Transactions: The Safety Net

When you transfer money, you don’t want “half” of it to arrive. You want all or nothing. ACID is the set of properties that guarantee database transactions are processed reliably.

1. The Classic Problem: The Bank Transfer

Alice has $100. Bob has $50. Alice sends $20 to Bob. The database must do two things:

  1. Subtract $20 from Alice.
  2. Add $20 to Bob.

Disaster Scenario: The power goes out after Step 1.

  • Alice has $80.
  • Bob still has $50.
  • $20 has vanished into thin air.

ACID prevents this.

2. The Four Pillars

A - Atomicity (“All or Nothing”)

This guarantees that the transaction is treated as a single “atom”. It cannot be split.

  • If all steps succeed → Commit.
  • If even one step fails → Rollback (Undo everything).
  • Mechanism: Undo Logs. Before changing data, the DB writes the old value to an Undo Log. If it crashes, it reads the log and restores the old value.

C - Consistency (“Rules are Rules”)

The database must move from one valid state to another valid state.

  • It must obey all defined rules: Foreign Keys, Unique Constraints, Check Constraints.
  • Example: If you try to insert a row with a duplicate unique ID, the transaction aborts.

I - Isolation (“Private Workspace”)

Transactions running at the same time shouldn’t mess with each other.

  • The Problem: If Alice and Bob both try to edit the same row simultaneously, one must wait (or fail).
  • The Solution (MVCC): Modern databases (Postgres, MySQL) use Multi-Version Concurrency Control.
    • Concept: When you update a row, the DB copies it.
    • Reader: Reads Version 1.
    • Writer: Writes Version 2.
    • Result: Readers don’t block Writers!

D - Durability (“Written in Stone”)

Once a transaction says “Success” (Commit), the data is permanent.

  • Even if the server catches fire 1 millisecond later, the data is safe.
  • Mechanism: Write-Ahead Logging (WAL). The DB writes the change to a sequential log file on the disk before updating the actual table. (See WAL).

3. Interactive Demo: The Atomic Swap

Be the Database Engine.

  1. Start Transaction: Creates a safety checkpoint.
  2. Update Data: Watch the Undo Log record the backup.
  3. Crash/Rollback: Watch the engine use the Undo Log to restore the data.
📊 Users Table (Main Memory)
IDNameBalance
1Alice$100
📝 Undo Log (Safety)
[Empty]
System Idle

4. Deep Dive: Distributed ACID (The Nightmare)

ACID is easy on a single machine. It is a nightmare on distributed systems (Microservices). If Service A (Payment) and Service B (Shipping) both need to update, how do we guarantee Atomicity?

Option A: Two-Phase Commit (2PC)

The “Strict Boss” approach. Imagine a wedding ceremony.

  1. Prepare Phase: The Priest (Coordinator) asks “Do you take this…?” to both parties.
    • Service A locks its database rows. “I’m ready.”
    • Service B locks its database rows. “I’m ready.”
  2. Commit Phase: If both say “Yes”, the Priest says “I now pronounce you…”. Both commit simultaneously.
  • The Fatal Flaw: It is a Blocking Protocol.
    • If the Coordinator crashes after Step 1, Service A and Service B are stuck holding locks forever. No one else can touch that data.
    • This is why 2PC is often called the “Availability Killer”.

Option B: Sagas (The Real World)

The “Undo Button” approach. Instead of locking everything, we execute a sequence of local transactions.

  • Step 1: Charge Card (Service A). Success.
  • Step 2: Ship Item (Service B). Fail (Out of stock).
  • Compensating Action: Trigger a “Refund” transaction on Service A to undo Step 1.
  • Pros: High performance. No global locks.
  • Cons: Temporary inconsistency (User sees “Charged” then “Refunded”).

Choreography vs Orchestration

  • Choreography: Service A emits an event “OrderPlaced”. Service B listens to it. Simple but hard to track.
  • Orchestration: A central “Order Service” calls A, then B. Easier to manage.
sequenceDiagram
    participant User
    participant ServiceA as Payment (Svc A)
    participant ServiceB as Shipping (Svc B)

    User->>ServiceA: 1. Order ($100)
    ServiceA->>ServiceA: Charge Card
    ServiceA->>ServiceB: 2. Ship Item
    Note over ServiceB: Out of Stock!
    ServiceB-->>ServiceA: Failure
    ServiceA->>ServiceA: 3. Compensating Txn (Refund $100)
    ServiceA-->>User: "Order Failed (Refunded)"

[!TIP] Interview Pro-Tip: In Microservices, avoid 2PC like the plague. It creates a Single Point of Failure (Coordinator) and kills availability. Use Sagas or Eventual Consistency instead.

5. Summary

  • Atomicity: Undo Logs save you from partial updates.
  • Consistency: Constraints prevent illegal data.
  • Isolation: MVCC allows readers and writers to work simultaneously.
  • Durability: WAL ensures data survives power loss.
  • Distributed: ACID is hard. Prefer Sagas (Eventual Consistency) over 2PC for microservices.