ACID properties in HLD - Deep Dive

Overview - ACID properties

What is it?

ACID properties are a set of rules that guarantee reliable processing of database transactions. They ensure that each transaction is handled completely and correctly, even if errors or failures happen. The four properties are Atomicity, Consistency, Isolation, and Durability. Together, they help keep data accurate and trustworthy.

Why it matters

Without ACID properties, databases could lose or corrupt data during crashes or multiple users working at once. Imagine banking systems where money disappears or duplicates because transactions are not handled properly. ACID ensures data stays correct and safe, which is critical for trust in software systems that manage important information.

Where it fits

Before learning ACID, you should understand what a database and a transaction are. After ACID, you can explore distributed databases, eventual consistency, and advanced transaction management techniques.

Mental Model

Core Idea

ACID properties ensure every database transaction is complete, correct, isolated, and permanent, so data stays reliable no matter what.

Think of it like...

ACID is like a secure bank vault process: either the whole deposit or withdrawal happens perfectly, no partial steps; the vault rules are always followed; each customer’s transaction is private; and once done, the money stays safe forever.

┌─────────────┐
│  Transaction │
└──────┬──────┘
       │
       ▼
┌─────────────┐
│  Atomicity  │
│ (All or None)│
└──────┬──────┘
       │
       ▼
┌─────────────┐
│ Consistency │
│(Valid State)│
└──────┬──────┘
       │
       ▼
┌─────────────┐
│  Isolation  │
│(No Interference)│
└──────┬──────┘
       │
       ▼
┌─────────────┐
│ Durability  │
│(Permanent)  │
└─────────────┘

Build-Up - 7 Steps

1

Foundation

What is a Database Transaction

Concept: Introduce the idea of a transaction as a unit of work in a database.

A transaction is a group of operations that must all succeed or fail together. For example, transferring money from one account to another involves subtracting from one and adding to another. Both steps must happen together to avoid errors.

Result

You understand that transactions bundle multiple steps into one logical action.

Knowing what a transaction is helps you see why special rules like ACID are needed to keep data safe.

2

Foundation

Why Transactions Can Fail

Concept: Explain common problems that can happen during transactions.

Transactions can fail due to power loss, software bugs, or multiple users changing data at the same time. Without protections, this can leave data half-changed or inconsistent.

Result

You realize that without safeguards, data can become wrong or lost.

Understanding failure causes motivates the need for ACID properties.

3

Intermediate

Atomicity: All or Nothing

🤔Before reading on: do you think a transaction can partially succeed and still be safe? Commit to yes or no.

Concept: Atomicity means a transaction either completes fully or not at all.

If any part of a transaction fails, the database must undo all changes made so far. This prevents partial updates that could corrupt data.

Result

You learn that atomicity protects data from partial changes.

Knowing atomicity prevents half-done work that breaks data integrity.

4

Intermediate

Consistency: Valid Data States

🤔Before reading on: do you think a transaction can leave data in an invalid state temporarily? Commit to yes or no.

Concept: Consistency ensures that a transaction moves the database from one valid state to another.

Rules like data types, constraints, and relationships must always hold true. Transactions must respect these rules to keep data meaningful.

Result

You understand that consistency keeps data correct according to rules.

Recognizing consistency prevents invalid or corrupt data from entering the system.

5

Intermediate

Isolation: Transactions Don’t Interfere

🤔Before reading on: do you think two transactions running at the same time can see each other's partial changes? Commit to yes or no.

Concept: Isolation means each transaction runs as if it is alone, without seeing others’ unfinished work.

This avoids confusion and errors from overlapping changes. Different isolation levels balance safety and speed.

Result

You grasp how isolation prevents messy data conflicts.

Understanding isolation helps avoid bugs from concurrent data access.

6

Advanced

Durability: Permanent Changes

🤔Before reading on: do you think a transaction’s changes can be lost after it finishes? Commit to yes or no.

Concept: Durability guarantees that once a transaction commits, its changes survive crashes or power loss.

Databases write changes to stable storage like disks or logs before confirming success. This ensures data is never lost after commit.

Result

You learn how durability protects data permanence.

Knowing durability builds trust that committed data won’t disappear unexpectedly.

7

Expert

Tradeoffs and Isolation Levels

🤔Before reading on: do you think full isolation always improves performance? Commit to yes or no.

Concept: Different isolation levels trade off strictness for speed and concurrency.

Strict isolation avoids all conflicts but slows down systems. Lower levels allow some anomalies but improve performance. Experts choose levels based on application needs.

Result

You understand the balance between safety and efficiency in real systems.

Recognizing tradeoffs helps design systems that meet both correctness and speed requirements.

Under the Hood

Internally, databases use logs, locks, and buffers to enforce ACID. Atomicity uses rollback logs to undo partial work. Consistency relies on constraints checked during transactions. Isolation uses locking or multiversion concurrency control to separate transactions. Durability writes data to stable storage before confirming success.

Why designed this way?

ACID was designed to solve real problems from early database failures and concurrent access. Alternatives like eventual consistency were rejected for critical systems needing strict correctness. The design balances complexity with reliability.

┌───────────────┐
│ Transaction   │
├───────────────┤
│ 1. Begin      │
│ 2. Execute    │
│ 3. Log Write  │
│ 4. Lock Data  │
│ 5. Commit     │
│ 6. Release    │
└─────┬─────────┘
      │
      ▼
┌───────────────┐
│ Storage Layer │
│ (Disk, Logs)  │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does atomicity mean a transaction can partially update some data and still be valid? Commit yes or no.

Common Belief: Atomicity means parts of a transaction can succeed independently.

Reality: Atomicity means the entire transaction succeeds or none of it does; no partial success is allowed.

Why it matters: Believing partial success is allowed can lead to corrupted or inconsistent data.

Quick: Can two transactions see each other's uncommitted changes? Commit yes or no.

Common Belief: Transactions can always see all changes made by others immediately.

Reality: Isolation prevents transactions from seeing uncommitted changes of others to avoid conflicts.

Why it matters: Ignoring isolation can cause data anomalies and unpredictable results.

Quick: Is durability guaranteed even if the system crashes right after commit? Commit yes or no.

Common Belief: Once a transaction commits, changes might still be lost if a crash happens immediately.

Reality: Durability ensures committed changes survive crashes by writing to stable storage first.

Why it matters: Misunderstanding durability risks losing critical data after commit.

Quick: Does consistency mean the database is always valid during a transaction? Commit yes or no.

Common Belief: Data must be valid at every step inside a transaction.

Reality: Consistency means the database is valid before and after the transaction, but temporary invalid states can exist during execution.

Why it matters: Expecting constant validity inside transactions can lead to confusion about how databases work internally.

Expert Zone

1

Some isolation levels allow controlled anomalies to improve performance, which experts must carefully manage.

2

Durability often uses write-ahead logging, where changes are recorded before applying them, to ensure recovery.

3

Atomicity and durability together require careful coordination between memory and disk to avoid partial writes.

When NOT to use

ACID is not suitable for highly distributed systems needing extreme scalability and availability, where eventual consistency models like BASE are preferred.

Production Patterns

Real systems use ACID for critical data like banking or inventory, often tuning isolation levels and using transaction logs and checkpoints to balance performance and reliability.

Connections

Distributed Systems

Builds-on

Understanding ACID helps grasp why distributed systems struggle with consistency and need new models like CAP theorem.

Concurrency Control

Same pattern

ACID’s isolation property is a core part of concurrency control, which manages how multiple users safely access data.

Legal Contracts

Analogy in different field

Like ACID ensures transactions are complete and binding, legal contracts ensure agreements are fully executed or void, highlighting the importance of all-or-nothing rules.

Common Pitfalls

#1 Allowing partial transaction commits leading to inconsistent data.

Wrong approach:

UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- no rollback if next step fails
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

Correct approach:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Root cause:

Misunderstanding atomicity and not grouping operations into a single transaction.

#2 Reading uncommitted data from other transactions causing dirty reads.

Wrong approach:

SELECT balance FROM accounts WHERE id = 1; -- reads uncommitted changes from another transaction

Correct approach:

Use transaction isolation level READ COMMITTED or higher to prevent reading uncommitted data.

Root cause:

Ignoring isolation levels and concurrency control.

#3 Assuming data is saved permanently immediately after update without commit.

Wrong approach:

UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- no commit or log write

Correct approach:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Root cause:

Not understanding durability and the need for commit to make changes permanent.

Key Takeaways

ACID properties ensure database transactions are reliable by guaranteeing atomicity, consistency, isolation, and durability.

Atomicity prevents partial updates, so transactions succeed fully or not at all, protecting data integrity.

Isolation keeps transactions independent, avoiding conflicts and ensuring predictable results in concurrent environments.

Durability makes sure committed changes survive crashes, building trust in data permanence.

Understanding ACID helps design systems that balance correctness, performance, and user trust in critical applications.