Designing Data-Intensive Applications Part 3: Transactions & Consistency

In real-world software systems, everything that can go wrong will go wrong.

A database server might lose power midway through processing a payment. A network cable might drop packets while a multi-table record update is executing. Two clients might try to book the exact same airline seat at the exact same millisecond.

To hide this staggering operational complexity from application developers, databases provide a crucial abstraction: Transactions.

A transaction is a mechanism for grouping multiple read and write operations into a single logical unit of execution. Either the entire transaction succeeds (Commit), or it fails and all changes are completely undone (Abort/Rollback).

In Part 3 of our Designing Data-Intensive Applications masterclass series, we explore the deep mechanics of ACID, Isolation Levels, Concurrency Bugs, and Distributed Consensus.

1. The Real Meaning of ACID

The safety guarantees provided by transactions are traditionally summarized by the acronym ACID (coined by Jim Gray in 1983). However, the definitions of these letters vary wildly across database vendors.

                    ┌───────────────────────────────────┐
                    │          ACID GUARANTEES          │
                    └─────────────────┬─────────────────┘
                                      │
         ┌──────────────────┬─────────┴─────────┬──────────────────┐
         ▼                  ▼                   ▼                  ▼
  ┌──────────────┐   ┌──────────────┐    ┌──────────────┐   ┌──────────────┐
  │  ATOMICITY   │   │ CONSISTENCY  │    │  ISOLATION   │   │ DURABILITY   │
  │ All-or-Nothing│   │ App Invariant│    │ Concurrency  │   │ Persistent   │
  │ Abortability │   │ (Not DB Rule)│    │ Protection   │   │ On Commit    │
  └──────────────┘   └──────────────┘    └──────────────┘   └──────────────┘

Atomicity (Abortability)

In computer science, “atomic” usually means something that cannot be broken into smaller pieces. In ACID, Atomicity does NOT refer to concurrency.

Atomicity means Abortability: if a transaction fails or encounters an error halfway through its execution, the database discards or aborts all writes made by that transaction so far.

Without atomicity, if an update touches 5 rows and fails on row 4, your database is left in a corrupted half-written state. Atomicity guarantees all-or-nothing execution.

Consistency

Consistency is a overloaded term. In ACID, Consistency means that your application maintains its invariants (invariants are statements about your data that must always be true, e.g. “Account balance must never be negative”).

Key Insight: Consistency in ACID is primarily the application developer’s responsibility, not the database’s! The database can only enforce simple constraints (foreign keys, UNIQUE constraints). If you write bad business logic that sets an account balance to -$500, the database cannot stop you unless you define explicit schema rules.

Isolation (Concurrency Control)

Isolation means that concurrently executing transactions should not interfere with each other.

The classic definition of Serializable Isolation states that the net result of running N concurrent transactions should be identical to running those same transactions serially, one after the other.

Durability

Durability is the promise that once a transaction has committed successfully, any data it has written will not be lost, even if the database server crashes or loses power a microsecond later.

In single-node databases, durability is achieved by writing data to a Write-Ahead Log (WAL) on non-volatile disk. In distributed databases, durability means the transaction has been acknowledged by a quorum of nodes.

2. Weak Isolation Levels & Concurrency Bugs

If true Serializable isolation were used everywhere, databases would execute transactions slowly because locking or snapshot validation limits throughput.

Therefore, databases routinely use Weak Isolation Levels by default. While weak isolation improves performance, it exposes developers to subtle, catastrophic concurrency bugs!

Isolation Level 1: Read Committed

Read Committed is the default isolation level in many popular databases (PostgreSQL, SQL Server, Oracle). It makes two key guarantees:

1. No Dirty Reads: When reading from the database, you will only see data that has been committed (you will never see uncommitted transient writes from another in-flight transaction).
2. No Dirty Writes: When writing to the database, you will only overwrite data that has already been committed (prevents overwriting uncommitted rows).

Transaction A (Writer)                     Transaction B (Reader)
──────────────────────                     ──────────────────────
UPDATE users SET age = 30 WHERE id = 1;
(Uncommitted) ───────────────────────────> SELECT age FROM users WHERE id = 1;
                                           Returns OLD value (age = 29)!
                                           (Dirty Read Prevented!)
COMMIT;

How Read Committed is Implemented:

• Dirty Writes are prevented using Row-Level Locks. When Transaction A updates a row, it holds a lock on that row until Transaction A commits or aborts.
• Dirty Reads are prevented by having the database remember the old committed value of a row, serving that old value to readers until the writer commits.

Isolation Level 2: Snapshot Isolation & MVCC

Read Committed does not prevent Read Skew (Non-Repeatable Reads).

Consider Alice, who has $1,000 split across Account 1 ($500) and Account 2 ($500). She transfers $100 from Account 1 to Account 2:

Alice Transaction                          Bank Audit Transaction
─────────────────                          ──────────────────────
1. READ Account 1 ($500)                   1. READ Account 1 ($500)
2. UPDATE Account 1 = $400 (Commit)
                                           2. READ Account 2 ($600) (Commited!)
                                           3. Sum = $500 + $600 = $1,100! (READ SKEW!)
3. UPDATE Account 2 = $600 (Commit)

To the Bank Audit query, Alice appears to have $1,100 because it read Account 1 before the transfer and Account 2 after the transfer!

The Solution: Snapshot Isolation via MVCC

Snapshot Isolation guarantees that a transaction reads from a consistent snapshot of the database taken at the exact millisecond the transaction started.

Snapshot isolation is implemented using Multi-Version Concurrency Control (MVCC):

Row Data (User Account 1)
┌─────────┬────────┬────────────────────┬────────────────────┐
│ User ID │ Balance│ Created_By_Tx_ID   │ Deleted_By_Tx_ID   │
├─────────┼────────┼────────────────────┼────────────────────┤
│ 1       │ $500   │ Tx 100             │ Tx 102             │ ◄── Version 1
│ 1       │ $400   │ Tx 102             │ NULL               │ ◄── Version 2
└─────────┴────────┴────────────────────┴────────────────────┘

When Tx 101 (started at time 101) reads Account 1, it sees Version 1 ($500) because Version 2 was created by Tx 102 (which is greater than 101).

Golden Rule of MVCC: “Readers never block writers, and writers never block readers.” This allows databases like PostgreSQL to execute fast, long-running analytics queries without locking out incoming write transactions!

Concurrency Bug: Lost Updates

A Lost Update occurs when two transactions read a row, modify it locally, and write it back, causing the first write to be overwritten and silently lost:

Transaction A                             Transaction B
─────────────                             ─────────────
1. READ balance ($100)                    1. READ balance ($100)
2. Local calc: 100 + 50 = 150
                                          2. Local calc: 100 + 20 = 120
3. WRITE balance = 150 (Commit)
                                          4. WRITE balance = 120 (Commit)
Result: Balance is $120! The $50 deposit from Transaction A WAS LOST!

How to Prevent Lost Updates:

1. Atomic Update Operations: UPDATE accounts SET balance = balance + 50 WHERE id = 1; (Pushes update logic into the database engine).
2. Explicit Locking (SELECT FOR UPDATE): SELECT * FROM accounts WHERE id = 1 FOR UPDATE; (Forces Transaction B to block until Transaction A completes).

Concurrency Bug: Write Skew & Phantoms

Write Skew is a subtle concurrency anomaly that is neither a dirty write nor a lost update.

The On-Call Doctors Problem

Suppose a hospital requires at least one doctor on call at all times. Doctor Alice and Doctor Bob are both on call. Feeling unwell, both request to go off-call at the exact same millisecond:

Doctor Alice Transaction                   Doctor Bob Transaction
────────────────────────                   ──────────────────────
1. SELECT COUNT(*) FROM doctors            1. SELECT COUNT(*) FROM doctors
   WHERE on_call = true;                      WHERE on_call = true;
   (Returns 2)                                (Returns 2)

2. IF count >= 2 THEN                      2. IF count >= 2 THEN
   UPDATE doctors                             UPDATE doctors
   SET on_call = false                        SET on_call = false
   WHERE name = 'Alice';                      WHERE name = 'Bob';

3. COMMIT;                                 3. COMMIT;

Result: BOTH TRANSACTIONS COMMIT! ZERO DOCTORS ARE NOW ON CALL!

This bug occurred because both transactions checked a premise (count >= 2), made a decision, and updated different rows. The change made by one transaction invalidated the premise of the other!

This phenomenon is closely tied to Phantom Reads: where a write in one transaction changes the result of a search query in another transaction.

3. Serializability: Ultimate Isolation

Serializable isolation is the strongest isolation level. It guarantees that even if transactions execute in parallel, the final state is identical to running them strictly one by one.

There are three main implementation strategies for Serializability:

Strategy 1: Actual Serial Execution (Single-Threaded Engines)

Eliminate concurrency entirely by running all transactions sequentially on a single CPU thread!

• Used by Redis and VoltDB.
• Extremely fast for short, in-memory transactions.
• Limitation: Long-running transactions ruin throughput completely.

Strategy 2: Two-Phase Locking (2PL)

For decades, 2PL was the standard implementation of serializability in SQL databases.

• Phase 1 (Growing Phase): Acquire locks as you read and write data.
• Phase 2 (Shrinking Phase): Release all locks at the very end of the transaction (COMMIT or ABORT).

Lock Compatibility Matrix:
                 │ Shared Lock (Read)  │ Exclusive Lock (Write)
─────────────────┼─────────────────────┼───────────────────────
Shared Lock      │      ALLOWED        │       BLOCKED
Exclusive Lock   │      BLOCKED        │       BLOCKED

Warning: 2PL is NOT the same as 2PC (Two-Phase Commit)! 2PL provides serializability; 2PC provides distributed atomic commit. 2PL suffers from terrible latency and frequent Deadlocks.

Strategy 3: Serializable Snapshot Isolation (SSI)

Pioneered in 2008 and used in modern PostgreSQL, SSI offers full serializable isolation with almost zero performance penalty compared to Snapshot Isolation!

SSI uses an optimistic concurrency control approach:

1. Transactions execute without acquiring locks, reading from MVCC snapshots.
2. The database tracks dependencies (when a transaction reads a query result that is later modified by another transaction).
3. When a transaction attempts to commit, the database checks if any premise was violated. If so, only the conflicting transaction is aborted and retried.

4. Distributed Transactions & Consensus

In a distributed environment, transactions must cross multiple nodes or database partitions. This introduces the infamous problem of Distributed Consensus.

The Two-Phase Commit (2PC) Protocol

When a transaction updates Node A and Node B, how do we guarantee that both nodes commit or both nodes abort?

2PC introduces a central Coordinator node:

[ COORDINATOR ]
      │
      ├─── Phase 1: Prepare ───> Node A ("Can you commit?")
      ├─── Phase 1: Prepare ───> Node B ("Can you commit?")
      │
      │   (Both Nodes reply "YES" & write to local log)
      │
      ├─── Phase 2: Commit  ───> Node A ("Commit now!")
      └─── Phase 2: Commit  ───> Node B ("Commit now!")

The Fatal Flaw of 2PC:

If the Coordinator node crashes in Phase 2 after Node A and Node B responded “YES”, Node A and Node B are left blocking indefinitely! They hold locks and cannot abort because they promised to commit, but cannot commit because they haven’t received Phase 2 instructions!

Distributed Consensus Algorithms (Paxos, Raft)

To solve the blocking problem of 2PC and handle leader elections in distributed systems, computer scientists developed Consensus Algorithms:

• Paxos (Leslie Lamport)
• Raft (Ongaro & Ousterhout)
• Zab (Apache ZooKeeper)

A consensus algorithm allows a group of nodes to agree on a single value or sequence of state machine operations, even if some nodes fail or drop network packets.

Key Guarantees of Consensus Algorithms:

• Uniform Agreement: No two nodes decide differently.
• Integrity: No node decides twice.
• Validity: If a node decides value V, then V was proposed by some node.
• Termination (Fault Tolerance): As long as a majority of nodes ((N/2) + 1) are healthy, the system makes progress!

Summary of Part 3

In Part 3, we mastered the mechanics of database consistency:

1. Atomicity means abortability (all-or-nothing execution), not concurrency control.
2. Read Committed prevents dirty reads and dirty writes using row locks and old committed values.
3. Snapshot Isolation (MVCC) prevents Read Skew by keeping multiple timestamped versions of rows, allowing readers to never block writers.
4. Write Skew and Phantoms require Serializable isolation (SSI, 2PL, or single-threaded execution).
5. Two-Phase Commit (2PC) provides atomic commits across nodes but can block if the coordinator dies; Raft/Paxos consensus algorithms provide fault-tolerant agreement across a majority of nodes.

Up next: Designing Data-Intensive Applications Part 4: Batch & Stream Processing (MapReduce, Spark, Kafka, & Event Sourcing).