25 min read Transactions & ACID Lesson 9 of 15
Part 3 — Performance, Transactions & Storage Engine

Transactions & ACID Properties

Atomicity, Consistency, Isolation, Durability & Crash Recovery Mechanics

In previous lessons, we mastered how databases store data, execute queries, join normalized tables, and traverse B+Tree indexes at nanosecond speeds. But database engineering now faces a critical reliability challenge: in production applications, multiple database operations rarely occur in isolation. Consider a standard banking scenario: you transfer $10,000 to a peer. Your account balance is deducted, but right before the recipient's balance is credited, the server suffers a sudden power failure! The money vanished from your account, yet the recipient never received a cent. The foundational safeguard that protects systems from partial update disasters is: The Database Transaction and the ACID Guarantees.

What You Will Master in This Lesson:

The bedrock principles of enterprise data reliability and fault tolerance:

  • What is a Transaction? Grouping multiple operations into an indivisible Logical Unit of Work
  • COMMIT vs ROLLBACK: Making changes permanent vs completely reverting uncommitted work
  • The 4 ACID Pillars: Rigorous breakdown of Atomicity, Consistency, Isolation, and Durability
  • Write-Ahead Logging (WAL): How sequential disk logging guarantees durability despite sudden crashes
  • Transaction Scope Anti-Pattern: Why calling third-party APIs inside transactions crashes servers
  • Application Integration: Coordinating backend try/catch blocks with database transactions

1 What is a Transaction? A Real-World Banking Example

In precise terms: A Transaction is a sequence of one or more database operations executed as a single, indivisible Logical Unit of Work (LUW).

Consider a classic peer-to-peer transfer. Salim has $50,000 in his account and Rahim has $20,000. Salim sends $10,000 to Rahim:

3 Indivisible Steps of a Fund Transfer:

1 UPDATE accounts SET balance = balance - 10000 WHERE id = 'salim';
2 UPDATE accounts SET balance = balance + 10000 WHERE id = 'rahim';
3 INSERT INTO transfer_logs (sender, receiver, amount) VALUES ('salim', 'rahim', 10000);

The Partial Update Disaster

Imagine executing these queries without transactional wrapping. Step 1 finishes cleanly—Salim's balance is deducted from $50,000 to $40,000. But right at Step 2, the server suffers a catastrophic kernel panic or loss of power!

Result: Rahim never receives the $10,000, yet Salim's money has evaporated. The total money in the banking system dropped from $70,000 to $60,000—an intolerable corruption of financial records.

Database transactions solve this via an uncompromising guarantee: Either every single step succeeds completely, or if any step fails, the entire database automatically reverts to its pristine starting state.

2 COMMIT, ROLLBACK & The Transaction Lifecycle

The fundamental control commands of a transaction lifecycle are straightforward:

1. COMMIT (The Permanent Seal)

When all operations inside the transaction boundary execute without error, the engine issues a COMMIT. All staged modifications become permanently etched into persistent storage and visible to concurrent users.

BEGIN;
-- All updates succeed
COMMIT; -- Changes sealed permanently!

2. ROLLBACK (Total Reversal)

If any statement encounters an error, a constraint is violated, or the connection crashes mid-flight, a ROLLBACK is triggered. Every single modification made since BEGIN is discarded, restoring the database to its pre-transaction state.

BEGIN;
-- Step 1 OK, Step 2 FAILS ✗
ROLLBACK; -- Undoes Step 1 automatically!
The Commit Boundary: Until COMMIT is explicitly acknowledged, changes exist purely as tentative draft states in RAM and transaction logs. They are not part of the database's permanent official state.

3 What is ACID? The 4 Core Guarantees

To ensure fault-tolerant data management, relational database management systems adhere to four fundamental properties, collectively known as ACID:

A

Atomicity

"All or Nothing"

Like an indivisible atom, a transaction cannot be broken into half-executed fragments. If an operation consists of 10 statements, either all 10 are committed, or none are. Under no circumstances can a transaction be partially applied.

C

Consistency

"Valid State → Valid State"

A transaction brings the database from one valid state to another, strictly obeying all schema constraints (CHECK, FOREIGN KEY, NOT NULL, UNIQUE) and business domain invariants. No transaction is permitted to leave corrupted or rule-breaking records.

I

Isolation

"Controlled Concurrency"

Even when thousands of transactions execute concurrently, the intermediate, uncommitted modifications of one transaction remain invisible to others. Each transaction executes as though it had exclusive access to the database.

D

Durability

"Committed Data Survives"

Once a transaction receives a successful COMMIT acknowledgment, its changes are permanently preserved. Even if the server instantly experiences a power cut or hardware fault, upon reboot the committed changes remain intact.

4 ACID Consistency: Dispelling the CAP Theorem Confusion

A widespread point of confusion among software engineers is: "Does Consistency mean every user across all nodes reads identical data at the same millisecond?" — No!

That distributed reading guarantee is Linearizability / CAP Theorem Consistency. In contrast, the 'C' in ACID refers strictly to database invariants and integrity constraints:

What ACID Consistency Actually Enforces:

  • Schema Constraints: If a column has CHECK (balance >= 0), no transaction can force an account into negative numbers.
  • Referential Integrity: No transaction can insert an order referencing a non-existent customer_id.
  • Domain Invariants: In our bank transfer, $\text{Balance}_{\text{Salim}} + \text{Balance}_{\text{Rahim}} = \$70,000$ must be strictly conserved before and after the transaction. Money cannot be created or destroyed.

5 How Durability Works: Write-Ahead Logging (WAL)

A naive assumption is: "When I type COMMIT, does the database immediately write all modified table rows to random disk pages?"

The answer is: No! Random disk writes take 5 to 10 milliseconds. If every transaction forced synchronous random page writes, database throughput would instantly choke.

Instead, modern enterprise engines (PostgreSQL, MySQL InnoDB, SQLite) utilize Write-Ahead Logging (WAL) or Redo Logs:

The Golden WAL Rule

"No data page from the Buffer Pool may be written to disk until the corresponding log entry recording the change has first been flushed to persistent storage via fsync()."

1. Append-Only Log

Sequential disk writes are orders of magnitude faster than random writes. A commit only requires flushing a tiny sequential log record.

2. Dirty Pages in RAM

Actual table heap pages remain modified in RAM (called Dirty Pages) and are flushed asynchronously to disk in background batches.

3. Crash Recovery

Upon power recovery, the engine inspects the WAL: replaying committed work (REDO) and aborting half-baked transactions (UNDO)!

6 Transaction Scope: The Long-Running Anti-Pattern

The single most dangerous mistake made by novice developers is: holding database transactions open for unnecessarily long durations.

Severe Anti-Pattern: External API Calls Inside Transactions!

BEGIN TRANSACTION;
  UPDATE orders SET status = 'processing';
  // DANGER! Stripe or bKash API call (takes 3-5 seconds)
  await callPaymentGateway(orderId);
  // DANGER! Awaiting email dispatch
  await sendConfirmationEmail(userEmail);
  UPDATE orders SET status = 'paid';
COMMIT;

Why is this catastrophic? While a transaction remains open, the database holds exclusive Row Locks on modified tables and reserves an active connection from the server's connection pool. If an external payment gateway takes 4 seconds, incoming queries pile up, the connection pool is starved, and the entire web application crashes with 504 Gateway Timeout!

Production Best Practice: Transactions must execute in milliseconds (ideally < 5ms). Charge external payments first, open an ultra-fast database transaction to record states, commit immediately, and offload emails to asynchronous background message queues.

7 Are Transactions Only for Banking?

Transactions are ubiquitous across every reliable software domain:

E-Commerce Checkout

Create Order + Insert Items + Decrement Stock. If stock depletion fails, the order cannot be created.

Seat Reservation

Mark seat as booked + Generate passenger invoice. Two users must never double-book the same seat.

SaaS Subscriptions

Upgrade user tier + Generate invoice record + Provision tenant features in one synchronized block.

Hands-On Laboratory

ACID & Transaction Simulation Lab

Explore fund transfers, crash recovery with WAL, and transaction scope timelines using the interactive tools below.

Fund Transfer: Salim → Rahim

Select a transfer amount and observe different transactional outcomes

Amount:
Salim's Account (Sender) $50,000 Rule: CHECK (balance ≥ 0)
Rahim's Account (Receiver) $20,000 Total System Money: $70,000
Transaction Engine Log:
Ready... Choose a scenario button above to trigger the transaction.
Self-Assessment

Knowledge Check: Transactions & ACID

Validate your understanding by answering the questions below:

1. Under the Atomicity guarantee, what occurs if an error happens on step 3 of a 4-step transaction?

2. What does ACID Consistency fundamentally enforce?

3. Why is invoking an external payment API call inside an open database transaction considered a severe architectural defect?

Key Takeaways

  • Transaction: Bundles multiple database statements into an indivisible unit, preventing catastrophic partial updates.
  • COMMIT vs ROLLBACK: Making modifications durable (COMMIT) vs reverting uncommitted drafts upon failure (ROLLBACK).
  • The ACID Pillars: Atomicity (all-or-nothing), Consistency (valid invariants), Isolation (invisible intermediate states), and Durability (power-loss survival).
  • Write-Ahead Logging (WAL): Flushes append-only sequential log records before modifying buffer pool pages, enabling automatic REDO/UNDO crash recovery.
  • Short Transactions: Keep transaction execution strictly within milliseconds; never embed slow external network calls inside open transactions.