Distributed Transactions

Chapter 1: ACID Breakdown

The word "transaction" gets thrown around loosely. Let's be precise. A database transaction provides four guarantees, collectively known as ACID. But these guarantees are not equally useful, and not all databases implement them equally.

A — Atomicity

Atomicity means "all or nothing." Either every operation in the transaction completes, or none of them do. If the system crashes midway through, it rolls back any partial changes. Think of it as an undo button: the transaction is the unit of "undo." You can't undo half a transaction.

Important clarification: atomicity is NOT about concurrency (that's Isolation). Atomicity is about fault tolerance — what happens when things crash partway through. The name is misleading; "abortability" would be more accurate.

C — Consistency

Consistency means the transaction preserves application invariants. If your invariant is "account balances never go negative," then a transaction that would cause a negative balance must be aborted. But here's the critical insight: the database can't know your application's invariants. YOU are responsible for writing transactions that maintain consistency. The database just provides the tools (constraints, triggers, atomicity). In practice, "C" in ACID is the application's responsibility, not the database's.

I — Isolation

Isolation means concurrent transactions don't interfere with each other. Each transaction behaves as if it were the only transaction running. The strongest form is serializability: the result of running transactions concurrently is the same as if they ran one at a time in some serial order.

D — Durability

Durability means once a transaction commits, the data survives crashes. Typically implemented via write-ahead logging (WAL): changes are written to a durable log before the commit acknowledgment. Even if the server crashes, it replays the log on recovery. In distributed systems, durability often means "replicated to N nodes."

Chapter 2: Isolation Levels

True serializable isolation is expensive — it often requires locking or aborting transactions that conflict. In practice, databases offer a menu of isolation levels, each trading correctness for performance. Understanding these is essential: most production databases default to a weaker level, and bugs from insufficient isolation are among the hardest to diagnose.

The Hierarchy

Let's demystify each level with concrete examples.

Read Committed

The most common default (PostgreSQL, Oracle, SQL Server). Two guarantees: (1) you only read data that has been committed (no dirty reads), (2) you only overwrite data that has been committed (no dirty writes). Implemented with row-level locks for writes and snapshot reads for reads.

Snapshot Isolation (MVCC)

Snapshot Isolation gives each transaction a consistent snapshot of the database at the moment it started. All reads within the transaction see that snapshot, even if other transactions commit changes in the meantime. This is how PostgreSQL's "REPEATABLE READ" and MySQL's "REPEATABLE READ" work (via Multi-Version Concurrency Control — MVCC).

Chapter 3: Read Phenomena

To understand isolation levels, you need to know the specific anomalies they prevent. Each "read phenomenon" is a concrete scenario where concurrent transactions produce results that couldn't happen in any serial execution.

Dirty Read

A dirty read happens when transaction T1 reads data written by transaction T2 that hasn't committed yet. If T2 later aborts, T1 has read data that never officially existed.

Non-Repeatable Read (Read Skew)

A non-repeatable read happens when T1 reads the same row twice and gets different values because T2 committed a change between the two reads.

Phantom Read

A phantom is like a non-repeatable read, but for sets of rows. T1 runs a query that returns a set of rows, T2 inserts or deletes a row that matches the query, and T1 re-runs the query — the set has changed.

Write Skew

Write skew is the subtlest and most dangerous anomaly. Two transactions each read the same data, make a decision, and write to different rows. Neither violates a constraint individually, but the combined result does.

Chapter 4: Two-Phase Commit

How do you get two independent databases to agree on committing a transaction? You can't just send "COMMIT" to both — what if one succeeds and the other crashes before receiving the message? You need a protocol that ensures either ALL participants commit or ALL abort, even in the face of failures.

Two-Phase Commit (2PC) is the classic answer. It introduces a coordinator (also called the transaction manager) that orchestrates the commit across all participants (the databases).

The Protocol

The Data Flow

Chapter 5: Coordinator Crash

Two-Phase Commit has a fatal weakness: the coordinator is a single point of failure. If the coordinator crashes at exactly the wrong moment, participants are stuck in a state called in-doubt (or uncertain), holding locks indefinitely.

The Worst-Case Scenario

Mitigations (But No Silver Bullet)

Chapter 6: The Saga Pattern

2PC is correct but blocking. What if you need distributed atomicity but can't afford to hold locks across services? The Saga pattern provides an alternative: instead of one distributed transaction, execute a sequence of local transactions, each in its own service. If any step fails, execute compensating transactions to undo the previous steps.

Think of it like booking a trip. You book a flight, then a hotel, then a rental car. If the car rental fails, you cancel the hotel, then cancel the flight. Each cancellation is a separate operation that undoes the corresponding booking.

Saga vs. 2PC

Two Flavors: Choreography vs. Orchestration

Choreography: Each service listens for events and decides its own next step. No central coordinator. Services communicate through events (e.g., Kafka topics).

Orchestration: A central saga orchestrator tells each service what to do and tracks the overall state. The orchestrator is like a state machine.

Compensating Transactions

The hard part of sagas is writing correct compensating transactions. A compensation must undo the effect of a step, but it can't simply "rollback" — the original transaction already committed. Compensation is a new, forward-moving operation.

Chapter 7: The Outbox Pattern

Sagas rely on services publishing events. But there's a subtle reliability problem: how do you atomically update the database AND publish an event? If you update the database first and then crash before publishing, the event is lost. If you publish first and then crash before updating, the event describes something that didn't happen.

This is the dual write problem: you need to write to two systems (database + message broker) atomically, but they don't share a transaction.

The Outbox Solution

Instead of writing to both systems, write ONLY to the database — but include the event in an outbox table within the same database transaction. A separate process (the "relay" or "publisher") reads the outbox table and publishes events to the message broker.

CDC: The Modern Outbox

Change Data Capture (CDC) eliminates polling by tailing the database's write-ahead log (WAL). Tools like Debezium, Maxwell, or native PostgreSQL logical replication capture every committed change and stream it to Kafka automatically. The outbox table still exists (for structure), but the relay is replaced by the CDC connector.

python
# Outbox pattern: atomic write + deferred publish
def create_order(order):
    with db.transaction():
        db.execute("INSERT INTO orders ...", order)
        db.execute("INSERT INTO outbox (event_type, payload, published) VALUES (%s, %s, false)",
                   'OrderCreated', json.dumps(order))
    # No Kafka publish here! The relay handles it.

# Relay process (runs continuously)
def relay():
    while True:
        events = db.execute("SELECT * FROM outbox WHERE published = false ORDER BY id LIMIT 100")
        for e in events:
            kafka.publish(topic=e.event_type, value=e.payload, key=e.id)
            db.execute("UPDATE outbox SET published = true WHERE id = %s", e.id)

Chapter 8: Saga Orchestrator

This is the showcase simulation. You are the saga orchestrator for an e-commerce order. The saga has four steps: create order, charge payment, reserve inventory, and schedule shipping. Each step can succeed or fail (you control which ones fail). When a step fails, the orchestrator runs compensating transactions for all previously completed steps in reverse order.

Observe the state machine transitions:

Chapter 9: Connections

Distributed transactions are the price you pay for splitting data across multiple services. Every microservice architecture eventually faces this problem, and the solution is always a trade-off between consistency, availability, and complexity.

Decision Framework

The Practical Answer

Related lessons:

• Coordination Avoidance — CRDTs and when you DON'T need transactions
• Database Replication — the replication foundations that transactions build on
• Partitioning & Storage — sharding introduces cross-shard transaction challenges

"A distributed system is one where I can't get my work done because a computer I've never heard of has crashed." — Leslie Lamport