ACID Properties - System Design 101

Introduction

ACID is a set of properties that guarantee reliable processing of database transactions. These properties ensure data integrity even in the face of errors, power failures, and other mishaps.

ACID compliance is a hallmark of traditional relational databases and is critical for applications where data correctness is paramount.

The acronym ACID stands for:

Atomicity
Consistency
Isolation
Durability

Let’s explore each property in detail.

What is a Transaction?

Before diving into ACID, let’s understand transactions: A transaction is a sequence of one or more database operations (reads/writes) treated as a single logical unit of work.

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

Transactions must complete entirely or not at all—partial execution is not allowed.

The Four ACID Properties

Atomicity: All or Nothing

Definition: The writes in a transaction are executed all at once and cannot be broken into smaller parts. If there are faults when executing the transaction, the writes in the transaction are rolled back.

Atomicity means 'all or nothing'

Either all operations in a transaction succeed, or none of them do. There’s no partial success.

How Atomicity Works

Successful transaction:

BEGIN TRANSACTION
  ✓ Operation 1: Deduct $100 from Account A
  ✓ Operation 2: Add $100 to Account B
COMMIT → Both operations persist

Failed transaction:

BEGIN TRANSACTION
  ✓ Operation 1: Deduct $100 from Account A
  ✗ Operation 2: Add $100 to Account B (FAILS)
ROLLBACK → Operation 1 is undone, Account A unchanged

Without atomicity, the first operation might succeed while the second fails, leaving the system in an inconsistent state (money disappeared!).

Implementation Mechanisms

Databases achieve atomicity through: Write-Ahead Logging (WAL):

1. Transaction starts
2. Write operations to log (not actual tables yet)
3. If all operations succeed:
   - Mark transaction as committed in log
   - Apply changes to actual tables
4. If any operation fails:
   - Discard log entries
   - No changes applied

Rollback Segments:

Store “before” images of data
If transaction fails, restore original values
After successful commit, discard rollback data

Real-World Example

E-commerce order placement:

BEGIN TRANSACTION;
  -- Deduct item from inventory
  UPDATE inventory SET quantity = quantity - 1 
  WHERE product_id = 123;
  
  -- Create order record
  INSERT INTO orders (user_id, product_id, amount) 
  VALUES (456, 123, 99.99);
  
  -- Charge payment
  INSERT INTO payments (order_id, amount, status)
  VALUES (LAST_INSERT_ID(), 99.99, 'charged');
COMMIT;

If the payment fails:

Order is not created
Inventory is not decremented
System remains consistent

Without atomicity, you could charge a customer without creating an order, or reduce inventory without a corresponding sale.

Consistency: Preserving Database Invariants

Definition: Any data written by a transaction must be valid according to all defined rules and maintain the database in a good state.

Unlike “consistency” in the CAP theorem (which means every read receives the most recent write), ACID consistency means preserving database invariants and constraints.

What are Invariants?

Invariants are rules that must always be true: Examples:

Foreign key constraints (orders must reference valid customers)
Unique constraints (email addresses must be unique)
Check constraints (age must be >= 0)
Business rules (account balance cannot be negative)
Data types (age must be an integer)

How Consistency Works

Before transaction:

Account A: $500
Account B: $300
Total: $800 ✓

During transaction:

BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
  UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;

After transaction:

Account A: $400
Account B: $400  
Total: $800 ✓ (Invariant preserved)

The total amount of money in the system remains constant—a critical business invariant.

Constraint Enforcement

Databases enforce consistency through: Foreign Key Constraints:

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- This will FAIL if customer 999 doesn't exist
INSERT INTO orders (id, customer_id) VALUES (1, 999);

Check Constraints:

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  balance DECIMAL(10,2),
  CHECK (balance >= 0)  -- Balance cannot be negative
);

-- This will FAIL
UPDATE accounts SET balance = -100 WHERE id = 1;

Unique Constraints:

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE  -- No duplicate emails
);

-- Second insert will FAIL
INSERT INTO users (id, email) VALUES (1, '[email protected]');
INSERT INTO users (id, email) VALUES (2, '[email protected]'); -- ✗

Triggers:

-- Ensure orders don't exceed inventory
CREATE TRIGGER check_inventory
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF (SELECT quantity FROM inventory WHERE id = NEW.product_id) < NEW.quantity THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient inventory';
  END IF;
END;

Consistency is partially the responsibility of the application. The database enforces constraints, but business logic must ensure operations make sense.

Isolation: Concurrent Transactions Don’t Interfere

Definition: When there are concurrent writes from two different transactions, the two transactions are isolated from each other.

The most strict isolation is serializability, where each transaction acts like it is the only transaction running in the database. However, this is hard to implement in reality, so we often adopt looser isolation levels.

Why Isolation Matters

Without isolation, concurrent transactions can interfere:

Transaction A                 Transaction B
─────────────                 ─────────────
READ balance (= 100)
                              READ balance (= 100)
balance = balance - 50
                              balance = balance - 30
WRITE balance (= 50)
                              WRITE balance (= 70)
                              
Final balance: 70 ❌ (Should be 20)

Lost Update: Transaction B overwrites Transaction A’s update.

Isolation Levels

SQL defines four isolation levels, trading off consistency for performance:

Read Uncommitted (Lowest)

Definition: Transactions can read data modified by other uncommitted transactions.Problems:

Dirty Reads: Reading uncommitted changes that may be rolled back

Example:

Transaction A: UPDATE balance = 500 (not committed)
Transaction B: READ balance → sees 500 (dirty read)
Transaction A: ROLLBACK
Transaction B: Used invalid data!

Use cases: Rarely used; perhaps for approximate analytics where accuracy isn’t critical.

Read Committed

Definition: Transactions can only read data that has been committed.Problems Solved:

✓ No dirty reads

Problems Remaining:

Non-Repeatable Reads: Reading same row twice gives different results

Example:

Transaction A:
  READ balance → 100
  (Transaction B updates balance to 200 and commits)
  READ balance → 200 (different!)

Use cases: Default for many databases (PostgreSQL, Oracle).

Repeatable Read

Definition: Data read during the transaction stays the same as when the transaction started.Problems Solved:

✓ No dirty reads
✓ No non-repeatable reads

Problems Remaining:

Phantom Reads: New rows appearing in range queries

Example:

Transaction A:
  SELECT COUNT(*) FROM users WHERE age > 25 → 10 rows
  (Transaction B inserts user with age=30 and commits)
  SELECT COUNT(*) FROM users WHERE age > 25 → 11 rows (phantom!)

Use cases: MySQL/InnoDB default; good balance for most applications.

Serializable (Highest)

Definition: Transactions execute as if they ran one at a time, in sequence.Problems Solved:

✓ No dirty reads
✓ No non-repeatable reads
✓ No phantom reads

Trade-off:

Significantly lower concurrency
Higher latency
More lock contention

Implementation: Often uses locks or optimistic concurrency control.Use cases: Financial transactions, critical inventory operations.

Implementation: MVCC

Most modern databases use MVCC (Multi-Version Concurrency Control) to implement isolation efficiently. How MVCC works: Each row has hidden metadata:

transaction_id: Which transaction created/modified this version
roll_pointer: Pointer to previous version of the row

Example scenario:

Initial State

Balance = 100 (transaction_id = 200, committed)

Transaction A Starts

Creates Read View with transaction_id = 201

Transaction B Starts

Creates Read View with transaction_id = 202

Transaction A Modifies Balance

New row: Balance = 200 (transaction_id = 201, uncommitted) Roll pointer → old row (Balance = 100)

Transaction B Reads Balance

Sees transaction_id = 201 is uncommitted Follows roll_pointer → reads Balance = 100

Transaction A Commits

Balance = 200 is now committed

Transaction B Still Reads 100

Read View was created before Transaction A committed Consistent snapshot maintained

MVCC allows readers and writers to avoid blocking each other, dramatically improving concurrency compared to traditional locking.

Durability: Surviving Failures

Definition: Data is persisted after a transaction is committed, even in case of system failure. In a distributed system, this means the data is replicated to some other nodes.

Durability guarantees permanence

Once a transaction is committed, the changes are permanent—even if the server crashes immediately after.

Why Durability Matters

Without durability:

User: "Transfer $1000 to Alice"
Database: "OK, committed!"
💥 Server crashes 💥
Database restarts: No record of transfer
User: "Where's my money?!"

With durability:

User: "Transfer $1000 to Alice"
Database: Writes to persistent storage
Database: "OK, committed!"
💥 Server crashes 💥  
Database restarts: Recovers transfer from log
User's money is safe ✓

Implementation Mechanisms

Write-Ahead Logging (WAL):

Transaction Executes

Changes are made in memory (buffer pool)

Write to Log

Changes are written to a durable log file on disk

Commit

Log is fsynced to disk (forced to physical storage)

Return Success

Only now does the database tell the client “committed”

Later: Write to Data Files

Changes are eventually written to the actual data files

The key insight: Writing sequentially to a log file is much faster than random writes to data files. WAL improves both durability and performance.

fsync() System Call:

// Write transaction to log
write(log_fd, transaction_data, size);

// Force to disk before returning to client
fsync(log_fd);  // Blocks until physically on disk

// Now safe to tell client "committed"
return SUCCESS;

Checkpoints:

Periodically flush dirty pages to disk
Allows truncating old log files
Reduces recovery time

Replication (Distributed Systems):

Primary Database
       ↓
  Write to Log
       ↓
  Replicate to Secondaries
       ↓
  Wait for acknowledgment
       ↓
  Commit

Data is durable once replicated to multiple nodes.

fsync() is slow because it requires waiting for physical disk writes. This is why durability comes at a performance cost.

Durability vs Performance

Strict durability (fsync after every commit) can be slow. Some systems offer options: PostgreSQL:

-- Strict durability (default)
SET synchronous_commit = on;  -- fsync before commit returns

-- Relaxed durability (faster, small data loss window)
SET synchronous_commit = off;  -- fsync asynchronously

MySQL:

-- Strict durability
SET innodb_flush_log_at_trx_commit = 1;  -- fsync every commit

-- Relaxed (faster)
SET innodb_flush_log_at_trx_commit = 2;  -- fsync every second

For most applications, strict durability is worth the performance cost. Losing committed transactions destroys user trust.

ACID in Practice

Example: Bank Transfer

Let’s see all four ACID properties in action:

BEGIN TRANSACTION;  -- Start atomic unit

-- Deduct from Account A
UPDATE accounts 
SET balance = balance - 100 
WHERE account_id = 'A'
AND balance >= 100;  -- Check constraint (Consistency)

-- Add to Account B  
UPDATE accounts 
SET balance = balance + 100 
WHERE account_id = 'B';

-- Check affected rows
IF @@ROWCOUNT != 2 THEN
  ROLLBACK;  -- Atomicity: All or nothing
  RETURN 'Transfer failed';
END IF;

COMMIT;  -- Persist changes (Durability)
-- Other concurrent transactions don't see intermediate state (Isolation)

ACID guarantees:

Atomicity: Both updates happen or neither does

Consistency: Balance constraint is enforced; total money unchanged

Isolation: Other transactions see either old or new state, not intermediate

Durability: After commit, transfer survives crashes

ACID vs BASE

NoSQL databases often follow BASE instead of ACID: BASE stands for:

Basically Available
Soft state
Eventual consistency

Comparison:

Aspect	ACID	BASE
Consistency	Strong, immediate	Eventual
Availability	May sacrifice for consistency	Prioritized
Complexity	Database handles	Application handles
Use Case	Financial, critical data	Social media, caching
Examples	PostgreSQL, MySQL	Cassandra, DynamoDB

ACID and BASE represent different trade-offs in the CAP theorem spectrum. Neither is universally better—choose based on your requirements.

When ACID is Critical

Use ACID-compliant databases when:

Financial transactions (banking, payments)

Inventory management (can’t oversell)

Healthcare records (accuracy is critical)

Booking systems (hotels, flights)

Order processing (e-commerce)

Any system where data correctness is paramount

When to Consider Non-ACID

You might relax ACID requirements for:

Social media feeds (eventual consistency is fine)

Analytics and reporting (approximate is acceptable)

Caching layers (stale data is tolerable)

High-throughput logging

IoT sensor data

Common Pitfalls

Pitfall #1: Assuming all databases are ACIDMany NoSQL databases sacrifice ACID for scalability. Always check your database’s guarantees.

Pitfall #2: Long-running transactionsACID transactions hold resources. Long transactions can block others and cause performance issues.

Pitfall #3: Not understanding isolation levelsUsing the wrong isolation level can lead to data anomalies or unnecessary performance degradation.

Pitfall #4: Ignoring network failures in distributed transactionsDistributed transactions are much harder to get right. Consider saga patterns or eventual consistency.

Best Practices

Keep Transactions Short

Minimize the time transactions hold locks. Do expensive operations outside transactions.

Choose the Right Isolation Level

Don’t default to serializable unless necessary. Most apps work fine with read committed or repeatable read.

Handle Failures Gracefully

Always catch and handle transaction failures, with proper retry logic.

Use Constraints

Let the database enforce invariants with foreign keys, checks, and triggers.

Monitor Transaction Duration

Alert on long-running transactions that might indicate problems.

Conclusion

ACID properties are fundamental to reliable data storage:

Atomicity ensures all-or-nothing execution
Consistency preserves database invariants
Isolation prevents concurrent transaction interference
Durability guarantees committed changes survive failures

These properties come at a cost (performance, scalability), but for many applications, they’re essential for correctness.

When in doubt, start with ACID-compliant storage for critical data. You can always add eventually-consistent caches or read replicas later for performance.

Next Steps

CAP Theorem

Understand trade-offs in distributed databases

Database Isolation

Deep dive into isolation levels and MVCC

Choosing a Database

Select the right database for your needs

Database Replication

Learn how replication affects ACID properties

APIs & Web

Databases

Caching & Performance

Security

Cloud & Distributed Systems

DevOps & CI/CD

​Introduction

​What is a Transaction?

​The Four ACID Properties

​Atomicity: All or Nothing

Atomicity means 'all or nothing'

​How Atomicity Works

​Implementation Mechanisms

​Real-World Example

​Consistency: Preserving Database Invariants

​What are Invariants?

​How Consistency Works

​Constraint Enforcement

​Isolation: Concurrent Transactions Don’t Interfere

​Why Isolation Matters

​Isolation Levels

​Implementation: MVCC

​Durability: Surviving Failures

Durability guarantees permanence

​Why Durability Matters

​Implementation Mechanisms

​Durability vs Performance

​ACID in Practice

​Example: Bank Transfer

​ACID vs BASE

​When ACID is Critical

​When to Consider Non-ACID

​Common Pitfalls

​Best Practices

​Conclusion

​Next Steps

CAP Theorem

Database Isolation

Choosing a Database

Database Replication

Build docs developers (and LLMs) love

Introduction

What is a Transaction?

The Four ACID Properties

Atomicity: All or Nothing

How Atomicity Works

Implementation Mechanisms

Real-World Example

Consistency: Preserving Database Invariants

What are Invariants?

How Consistency Works

Constraint Enforcement

Isolation: Concurrent Transactions Don’t Interfere

Why Isolation Matters

Isolation Levels

Implementation: MVCC

Durability: Surviving Failures

Why Durability Matters

Implementation Mechanisms

Durability vs Performance

ACID in Practice

Example: Bank Transfer

ACID vs BASE

When ACID is Critical

When to Consider Non-ACID

Common Pitfalls

Best Practices

Conclusion

Next Steps