system-design · beginner

ACID Transactions — All-or-Nothing Database Work

The Central Question

Consider an enterprise financial ledger platform running on the TxLab platform (txlab.com) processing 100,000,000 transactions per day:

• A mobile application user executes a $\$500$ transfer from Alice's account (acc_alice) to Bob's account (acc_bob).
• The application issues two distinct database update statements:

If the database server suffers an unexpected power loss immediately after executing statement 1, $\$500$ is deducted from Alice, but Bob never receives the money. $\$500$ has completely vanished from the system.

Furthermore, if a second user attempts to read Alice's account balance while statement 1 has executed but statement 2 is still pending, the second user sees an inconsistent, transient state.

To prevent partial updates and concurrent data corruption, databases group operations into protected execution units.

A Database Transaction is a sequence of SQL operations executed as a single, indivisible unit of work governed by ACID (Atomicity, Consistency, Isolation, Durability) properties.

This lesson answers one central question: How do the four ACID guarantees (Atomicity, Consistency, Isolation, Durability) prevent data corruption during concurrent operations and system crashes, and how do Write-Ahead Logging (WAL) and Multi-Version Concurrency Control (MVCC) implement transaction safety in relational database engines?

Deconstructing ACID: The Four Guarantees

The ACID acronym defines four distinct operational promises made by a relational database engine:

flowchart TD
  ACID[ACID Transaction Properties] --> A[Atomicity: All or Nothing]
  ACID --> C[Consistency: Valid Schema States]
  ACID --> I[Isolation: Concurrency Safety]
  ACID --> D[Durability: Crash Survival]
  
  A --> ADesc["Prevents partial executions. If any step fails, all steps roll back."]
  C --> CDesc["Enforces schema constraints (foreign keys, CHECK, UNIQUE rules)."]
  I --> IDesc["Prevents concurrent transactions from seeing partial dirty work."]
  D --> DDesc["Guarantees committed transactions survive hardware power loss."]

Figure 1: Taxonomy of the four foundational ACID database guarantees.

1. Atomicity ("All or Nothing")

2. Consistency (Schema Rules)

[!NOTE] ACID Consistency vs. CAP Consistency: ACID Consistency refers to schema constraint integrity within a single database node. CAP Consistency (Linearizability) refers to read freshness across multiple replicated nodes in a distributed system.

3. Isolation (Concurrent Non-Interference)

4. Durability (Committed Data Persists)

Durability Internals: Write-Ahead Logging (WAL)

To achieve high write throughput while preserving Durability, databases do not write modified data pages directly to disk data files on every COMMIT. Disk data files are large and scattered across un-contiguous sectors, making synchronous disk writes extremely slow.

Instead, relational databases utilize Write-Ahead Logging (WAL):

sequenceDiagram
    autonumber
    actor App as Application
    participant RAM as DB Shared Buffer (RAM)
    participant WAL as Write-Ahead Log (Disk)
    participant DataFile as Main Data File (Disk)
    
    App->>RAM: 1. UPDATE balance = balance - 500
    Note over RAM: In-memory page marked Dirty
    App->>RAM: 2. COMMIT
    RAM->>WAL: 3. Append Transaction Record to WAL File (Sequential Disk I/O)
    WAL-->>App: 4. COMMIT Acknowledged (HTTP 200)
    
    Note over RAM,DataFile: Asynchronous Background Checkpoint
    RAM->>DataFile: 5. Flushes Dirty Buffer Pages to Main Data File on Disk

Figure 2: Write-Ahead Logging sequence appending commits sequentially before background disk checkpoints.

SQL Isolation Levels and Concurrency Anomalies

The ANSI SQL standard defines four transaction isolation levels based on which concurrent read anomalies they permit or prevent:

flowchart TD
  Levels[SQL Isolation Levels] --> RU[1. Read Uncommitted]
  Levels --> RC[2. Read Committed]
  Levels --> RR[3. Repeatable Read]
  Levels --> Ser[4. Serializable]
  
  RU --> DirtyRead["Permits Dirty Reads.<br/>Transaction A reads un-committed draft changes of Transaction B."]
  RC --> NonRepRead["Prevents Dirty Reads.<br/>Transaction A re-reads same row and sees updated values (Non-Repeatable Read)."]
  RR --> Phantom["Prevents Non-Repeatable Reads.<br/>Transaction A re-runs range query and sees new rows inserted (Phantom Read)."]
  Ser --> Strict["Strict Serializability.<br/>Zero anomalies. Transactions execute in total sequential order."]

Figure 3: Taxonomy of SQL isolation levels and allowed concurrency anomalies.

Anomaly Matrix

Two-Phase Locking (2PL) vs Snapshot Isolation (SI)

• Two-Phase Locking (2PL): Pessimistic locking mechanism divided into a Growing Phase (acquiring shared/exclusive locks) and a Shrinking Phase (releasing locks at COMMIT). Shared read locks block exclusive write locks, causing severe concurrency bottlenecks.
• Snapshot Isolation (SI): Optimistic concurrency control using MVCC. Transactions read a consistent snapshot of data frozen at transaction start time (BEGIN). Writes take exclusive locks at commit time. If two concurrent transactions attempt to write to the same row, the second committing transaction aborts with a serialization error (First-Committer-Wins).

Nested Transactions and Savepoints

WAL Archiving & Point-In-Time Recovery (PITR)

Auto-Vacuum Tuning for High-Update Workloads

Multi-Version Concurrency Control (MVCC)

Modern databases (such as PostgreSQL and MySQL InnoDB) avoid restrictive read-write locks by implementing Multi-Version Concurrency Control (MVCC):

flowchart LR
  subgraph Data Row Tuple Version Stack
    V1["Tuple V1 (xmin: 100, xmax: 105)<br/>balance = 1000"]
    V2["Tuple V2 (xmin: 105, xmax: 0)<br/>balance = 500"]
  end
Tx102[Tx 102: SELECT balance] -->|Reads Snapshot| V1
  Tx108[Tx 108: SELECT balance] -->|Reads Snapshot| V2

Figure 4: MVCC maintaining multiple tuple versions to enable non-blocking concurrent reads.

Golden Rule of MVCC

"Readers never block writers, and writers never block readers."

When a transaction modifies a row under MVCC, the database does not overwrite the existing disk row. Instead, it creates a new tuple version stamped with xmin (creating transaction ID) and xmax (deleting transaction ID). Concurrent transactions read historical tuple versions matching their snapshot timestamp without taking locks!

Complete Worked Example: Production Go Transaction Isolation Engine

Let's inspect a complete Go implementation of a Transaction Isolation Engine for the TxLab platform (txlab.com).

package main
import (
	"context"
	"database/sql"
	"fmt"
	"time"
)
type AccountTxManager struct {
	db *sql.DB
}
func NewAccountTxManager(db sql.DB) AccountTxManager {
	return &AccountTxManager{db: db}
}
func (m *AccountTxManager) TransferMoneySerializable(ctx context.Context, fromAcc, toAcc string, amount float64) error {
	// Enforce Serializable Isolation Level for Financial Transfers
	txOpts := &sql.TxOptions{
		Isolation: sql.LevelSerializable,
		ReadOnly:  false,
	}
tx, err := m.db.BeginTx(ctx, txOpts)
	if err != nil {
		return fmt.Errorf("failed to begin serializable transaction: %w", err)
	}
	defer tx.Rollback()
// 1. Check Sender Balance
	var balance float64
	err = tx.QueryRowContext(ctx, "SELECT balance FROM accounts WHERE account_id = $1 FOR UPDATE", fromAcc).Scan(&balance)
	if err != nil {
		return fmt.Errorf("failed to query sender balance: %w", err)
	}
if balance < amount {
		return fmt.Errorf("insufficient funds: balance %.2f < amount %.2f", balance, amount)
	}
// 2. Deduct from Sender
	_, err = tx.ExecContext(ctx, "UPDATE accounts SET balance = balance - $1 WHERE account_id = $2", amount, fromAcc)
	if err != nil {
		return fmt.Errorf("failed to update sender balance: %w", err)
	}
// 3. Add to Recipient
	_, err = tx.ExecContext(ctx, "UPDATE accounts SET balance = balance + $1 WHERE account_id = $2", amount, toAcc)
	if err != nil {
		return fmt.Errorf("failed to update recipient balance: %w", err)
	}
// 4. Commit Transaction Atomically
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit failed (serializable collision): %w", err)
	}
fmt.Printf("[TX SUCCESS] Transferred $%.2f from %s to %s at %s\n", amount, fromAcc, toAcc, time.Now().Format(time.RFC3339))
	return nil
}

Failure Modes and Engineering Mitigations

What You Should Remember

1. ACID guarantees data integrity: Atomicity (All or Nothing), Consistency (Schema Constraints), Isolation (Concurrency Safety), and Durability (Crash Persistence).
2. WAL provides crash survival with high throughput: Write-Ahead Logging appends commits sequentially to disk logs before flushing data pages asynchronously.
3. MVCC enables non-blocking reads: Multi-Version Concurrency Control maintains tuple versions so readers never block writers and writers never block readers.
4. Choose isolation levels deliberately: Use Read Committed for general QPS and Serializable for financial transfers to prevent Write Skew.
5. Lock rows in deterministic order to prevent deadlocks: Always acquire locks in alphabetical or numerical key order (acc_A before acc_B).

Glossary of Terms

Practice Scenario and Self-Assessment

Architecture Scenario

• Account balances (account_id) process 10,000 concurrent transfers per second.
• Critical requirement: Overdrafts below $\$0.00$ are strictly forbidden.

1. Formulate the exact SQL isolation level and locking strategy required to prevent Write Skew and overdrafts.
2. Design the WAL logging configuration to achieve sub-millisecond commit latency without risking financial data loss.

Interactive Self-Assessment

It maintains multiple row versions so read queries read historical snapshots without taking locks, preventing readers and writers from blocking each other.

MVCC automatically converts relational database primary key indexes into un-indexed CSV files.

MVCC replaces public DNS nameservers with local hosts file entries.

MVCC doubles the physical hardware clock speed of primary database CPUs.

Repeatable Read permits Write Skew anomalies, allowing concurrent transactions to violate balance invariants and cause overdrafts.

Repeatable Read automatically formats persistent NVMe SSD disk drives on database servers.

Repeatable Read revokes edge HTTPS TLS encryption certificates on load balancers.

Repeatable Read reboots operating system hypervisors across all database nodes.

What to Learn Next

• CAP Theorem & PACELC — Consistency vs Availability: Explore distributed system trade-offs during network partitions.
• Strong vs Eventual Consistency: Learn linearizability and causal consistency models.
• Consensus Algorithms — Raft and Replicated State Machines: Master distributed quorum leader elections.

Track: Data, Storage and Messaging

Next: Banking Transaction Platform Design

By Shubham Jain

All articles · Study paths

Shubham Jain · Learning Lab