Database Transactions and ACID: Isolation Levels Demystified

A database transaction is a promise: a group of operations that either all succeed together or all fail together. This sounds simple until you consider what happens when thousands of transactions run simultaneously, each trying to read and write overlapping data. That is where things get interesting.

ACID Through Real-World Analogies

Atomicity: The ATM Withdrawal

You insert your card, request $200, and the ATM begins the process. Behind the scenes, two things need to happen: your account balance decreases by $200 and the machine dispenses cash. Atomicity guarantees that either both happen or neither happens. If the machine jams and cannot dispense cash, your balance stays unchanged. You never lose money into the void.

-- This is atomic: both statements succeed or both roll back
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 'alice';
INSERT INTO atm_dispensals (account_id, amount, atm_id)
  VALUES ('alice', 200, 'atm_downtown_01');
COMMIT;

Without atomicity, a crash between these two statements would leave your balance reduced with no record of where the money went.

Consistency: The Airline Booking

An airline has 180 seats on a flight. Consistency means the database enforces rules — called constraints — that prevent invalid states. If 180 people have already booked, the 181st booking must be rejected, not allowed to create an overbooked state.

-- Consistency constraint
ALTER TABLE flights ADD CONSTRAINT no_overbooking
  CHECK (booked_seats <= total_seats);

-- This will fail if it would violate the constraint
UPDATE flights SET booked_seats = booked_seats + 1
  WHERE flight_id = 'UA123' AND booked_seats < total_seats;

Consistency is partly the database’s job (enforcing constraints, foreign keys, unique indexes) and partly the application’s job (encoding business rules). The database cannot know that a bank balance should not go negative unless you tell it.

Isolation: The Grocery Store Checkout

Imagine two cashiers scanning items for different customers at adjacent registers. Each cashier sees a consistent view of their transaction — they do not see half-scanned items from the other register appearing in their total. Isolation ensures that concurrent transactions do not interfere with each other’s view of the data.

Durability: The Carved Stone Tablet

Once a transaction commits, the data survives even if the server loses power immediately after. The database writes changes to durable storage (disk, SSD) before confirming the commit. Think of it as carving the result into stone rather than writing it in sand.

In practice, durability means the database flushes the write-ahead log to disk before returning “commit success” to the client. Most databases also support replication for additional durability across machines.

Isolation Levels: The Heart of the Complexity

Isolation is the most nuanced ACID property because perfect isolation (every transaction runs as if it is the only one) is extremely expensive. Databases offer multiple isolation levels that trade safety for performance.

Read Anomalies You Need to Know

Before diving into isolation levels, let us understand the three anomalies they prevent.

Dirty read: Transaction A reads data that Transaction B has written but not yet committed. If B rolls back, A has read data that never officially existed.

Timeline:
T1: BEGIN
T2: BEGIN
T2: UPDATE accounts SET balance = 900 WHERE id = 'alice'  -- was 1000
T1: SELECT balance FROM accounts WHERE id = 'alice'       -- reads 900 (dirty!)
T2: ROLLBACK                                               -- balance is back to 1000
T1: -- now holds stale value 900

Non-repeatable read: Transaction A reads the same row twice and gets different values because Transaction B modified and committed it in between.

Timeline:
T1: BEGIN
T1: SELECT balance FROM accounts WHERE id = 'alice'  -- reads 1000
T2: UPDATE accounts SET balance = 500 WHERE id = 'alice'
T2: COMMIT
T1: SELECT balance FROM accounts WHERE id = 'alice'  -- reads 500 (different!)
T1: COMMIT

Phantom read: Transaction A runs a query that returns a set of rows, then Transaction B inserts a new row that matches the query, and when A runs the same query again, a new “phantom” row appears.

Timeline:
T1: BEGIN
T1: SELECT * FROM orders WHERE status = 'pending'  -- returns 5 rows
T2: INSERT INTO orders (status) VALUES ('pending')
T2: COMMIT
T1: SELECT * FROM orders WHERE status = 'pending'  -- returns 6 rows (phantom!)
T1: COMMIT

The Four Isolation Levels

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

Read Uncommitted is the wild west. Transactions can see uncommitted changes from other transactions. Almost no production system uses this because the bugs it creates are subtle and devastating. Imagine showing a user a bank balance that includes a transfer that subsequently gets rolled back.

Read Committed is the default in PostgreSQL and Oracle. Each query within a transaction sees only committed data, but different queries within the same transaction might see different committed states. This is sufficient for most CRUD applications.

Repeatable Read guarantees that if you read a row once within a transaction, reading it again will return the same value — even if another transaction modifies it in the meantime. MySQL InnoDB’s default level. It prevents dirty reads and non-repeatable reads but allows phantom rows.

Serializable is the strongest level. It guarantees that the result of running transactions concurrently is the same as if they ran one after another. This eliminates all anomalies but significantly reduces throughput because the database must detect and prevent conflicts.

-- Setting isolation level in PostgreSQL
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT * FROM inventory WHERE product_id = 'widget' FOR UPDATE;
-- The FOR UPDATE clause locks the row, preventing other transactions
-- from modifying it until this transaction completes

UPDATE inventory SET quantity = quantity - 1
  WHERE product_id = 'widget' AND quantity > 0;

COMMIT;

How Databases Implement Isolation

Multi-Version Concurrency Control (MVCC) is the dominant approach. Instead of locking rows, the database keeps multiple versions of each row. Each transaction sees a snapshot of the data as of its start time. Writers create new versions without blocking readers. PostgreSQL and MySQL InnoDB both use MVCC.

Two-Phase Locking (2PL) is the traditional approach. Transactions acquire locks on data they read or write, and hold those locks until commit. This prevents anomalies but can cause deadlocks and reduces concurrency.

Serializable Snapshot Isolation (SSI) is a newer approach used by PostgreSQL for its serializable level. It uses MVCC for most operations but detects potentially conflicting access patterns and aborts one of the conflicting transactions. This gives serializable guarantees with much better performance than 2PL.

Distributed Transactions: Beyond a Single Database

When your system spans multiple databases, services, or message queues, you need distributed transactions. This is where things get genuinely hard.

Two-Phase Commit (2PC)

Two-phase commit is the textbook solution for distributed transactions. A coordinator orchestrates the process:

Phase 1 — Prepare. The coordinator asks each participant: “Can you commit this transaction?” Each participant writes the transaction to its local log and replies yes or no.

Phase 2 — Commit or Abort. If all participants said yes, the coordinator sends a commit message. If any participant said no, the coordinator sends an abort message.

Coordinator                  Database A         Database B
    │                            │                   │
    │── "prepare" ──────────────▶│                   │
    │── "prepare" ──────────────────────────────────▶│
    │                            │                   │
    │◀── "yes, prepared" ───────│                   │
    │◀── "yes, prepared" ──────────────────────────│
    │                            │                   │
    │── "commit" ───────────────▶│                   │
    │── "commit" ───────────────────────────────────▶│
    │                            │                   │
    │◀── "committed" ───────────│                   │
    │◀── "committed" ──────────────────────────────│

The problem with 2PC is the blocking nature: if the coordinator crashes after sending “prepare” but before sending “commit,” all participants are stuck holding locks indefinitely, unable to commit or abort on their own. This is why 2PC has fallen out of favor in microservices architectures.

The Saga Pattern

Sagas take a fundamentally different approach: instead of one big distributed transaction, break it into a sequence of local transactions, each with a compensating action that undoes its effect if a later step fails.

Order Saga:
1. Create order (compensate: cancel order)
2. Reserve inventory (compensate: release inventory)
3. Charge payment (compensate: refund payment)
4. Ship order (compensate: recall shipment)

If step 3 (charge payment) fails:
  - Run compensating action for step 2: release inventory
  - Run compensating action for step 1: cancel order

Two coordination patterns for sagas:

Choreography: Each service publishes events, and the next service in the chain listens and reacts. Simple for short sagas but hard to debug when you have ten services.

Orchestration: A central orchestrator tells each service what to do and handles the compensation logic. Easier to understand and debug but introduces a single point of coordination.

# Saga orchestrator (simplified)
class OrderSaga:
    def execute(self, order):
        steps = [
            (self.create_order, self.cancel_order),
            (self.reserve_inventory, self.release_inventory),
            (self.charge_payment, self.refund_payment),
            (self.schedule_shipping, self.cancel_shipping),
        ]
        
        completed = []
        for action, compensate in steps:
            try:
                action(order)
                completed.append(compensate)
            except Exception:
                # Compensate in reverse order
                for comp in reversed(completed):
                    comp(order)
                raise SagaFailed(f"Failed at {action.__name__}")

The trade-off: sagas provide eventual consistency rather than strong consistency. Between steps, the system is in an intermediate state that other transactions can observe. Your application must be designed to handle these intermediate states gracefully.

BASE vs ACID

BASE is a set of properties for distributed systems that relaxes ACID guarantees:

• Basically Available: The system guarantees availability (it will respond), even if the response might not reflect the latest write.
• Soft state: The state of the system may change over time even without new inputs, as data propagates.
• Eventually consistent: Given enough time without new writes, all replicas will converge to the same value.

ACID and BASE are not opposites — they are points on a spectrum. Many systems use ACID within a single service (local transactions are strongly consistent) and BASE across services (inter-service communication is eventually consistent).

┌──────────────────────────────────────────────────┐
│                  Consistency Spectrum              │
│                                                    │
│  ACID ◀─────────────────────────────────▶ BASE    │
│  Strong consistency        Eventual consistency    │
│  Lower availability        Higher availability     │
│  Higher latency            Lower latency           │
│  Simpler app logic         Complex app logic       │
│                                                    │
│  Banking, inventory        Social feeds, analytics │
└──────────────────────────────────────────────────┘

How Stripe Handles Transaction Consistency

Stripe processes billions of dollars in payments and must get transaction consistency right. Their publicly shared approach offers practical lessons:

Idempotency keys. Every API request includes an idempotency key. If a network failure causes a retry, the second request returns the same result as the first instead of charging the customer twice. This is essential for at-least-once delivery semantics.

# Stripe API with idempotency
stripe.Charge.create(
    amount=2000,
    currency="usd",
    source="tok_visa",
    idempotency_key="order_12345_charge_attempt_1"
)

Two-phase approach for complex flows. For operations that span multiple internal services (authorize, capture, settle), Stripe uses a pattern similar to sagas with careful state machines. Each payment moves through well-defined states, and each transition is idempotent.

Optimistic concurrency control. Rather than locking rows for the duration of a transaction, Stripe uses version numbers. When updating a record, the query includes the expected version — if it has changed, the update fails and the operation retries.

UPDATE payment_intents
SET status = 'captured', version = version + 1
WHERE id = 'pi_123' AND status = 'authorized' AND version = 5;
-- If version != 5, someone else modified it first

Wrapping Up

Transactions and ACID properties are the foundation of data integrity in any system. Understanding isolation levels helps you choose the right trade-off between consistency and performance for your specific use case. For most applications, read committed isolation with explicit locking on critical operations is a pragmatic sweet spot.

When you move beyond a single database, accept that distributed transactions are fundamentally harder. Choose two-phase commit only when you need strong consistency across a small number of participants. For everything else, design sagas with compensating actions and embrace eventual consistency — but do it deliberately, understanding exactly which inconsistencies your users might observe and for how long.