ACID Transactions

The Core Idea

ACID stands for Atomicity (all or nothing), Consistency (data always valid), Isolation (concurrent transactions do not interfere), and Durability (committed data survives crashes). ACID guarantees are what make relational databases reliable for critical data like financial transactions.

Step-by-Step Walkthrough

When you execute a SQL transaction:

1. BEGIN: Start transaction, allocate resources.
2. Execute statements: Read/write data, buffer changes.
3. COMMIT: Write all changes to the write-ahead log, flush to disk, release locks.
4. If any step fails: ROLLBACK — undo all changes.

The WAL (Write-Ahead Log) is key to durability: changes are written to the log before being applied to data files. If the server crashes mid-transaction, the WAL can replay committed changes.

Why This Approach Wins

• Atomicity: A transaction either completes entirely or not at all. If transferring $100 fails midway, both accounts remain unchanged.
• Consistency: Data always satisfies constraints (foreign keys, unique constraints, check constraints). Invalid data is rejected.
• Isolation levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable — each offers different tradeoffs between correctness and performance.
• Durability: Once a transaction is committed, it persists even if the server crashes. Achieved via write-ahead logs (WAL) and fsync.
• 2PC (Two-Phase Commit): Extends ACID across multiple databases. Coordinator asks all participants to prepare, then commit. Slow but correct.

In Production

PostgreSQL provides full ACID compliance with MVCC (Multi-Version Concurrency Control) for high-performance isolation.

Google Spanner extends ACID across global distributed databases using TrueTime for ordering.

MongoDB added multi-document ACID transactions in version 4.0, bridging the gap with relational databases.

Tradeoffs and Limitations

• ACID vs Performance: Stricter isolation = slower transactions. Serializable is the safest but slowest.
• ACID vs Scalability: Distributed ACID (2PC) is expensive. Many NoSQL databases trade ACID for horizontal scalability.
• Consistency vs Availability: In distributed databases, strict ACID often means CP (sacrificing availability during partitions).

Production Gotchas

1. Assuming all databases provide ACID — many NoSQL databases provide weaker guarantees
2. Using the wrong isolation level — too weak causes phantom reads, too strong kills performance
3. Not understanding WAL — data durability depends on correct fsync configuration

The Interview Angle

1. What does ACID stand for and why does it matter?
2. What are database isolation levels?
3. When would you sacrifice ACID guarantees?
4. What is a write-ahead log?

Next Up

• sql-vs-nosql
• Data Replication
• strong-vs-eventual-consistency
• CAP Theorem

Original DosiBridge notes for ACID Transactions

This section is written specifically for this page, not copied from a general system-design checklist. The useful way to study ACID Transactions is to connect the concept to a data path where correctness, access pattern, and future migrations matter more than benchmark numbers. That framing changes the design discussion from definitions to operating choices.

What makes this topic distinct

• Query Shape: For ACID Transactions, decide the query shape before choosing tools; otherwise the design looks clean but fails when acid changes.
• Write Amplification: For ACID Transactions, decide the write amplification before choosing tools; otherwise the design looks clean but fails when transactions changes.
• Replication Lag: For ACID Transactions, decide the replication lag before choosing tools; otherwise the design looks clean but fails when acid changes.
• Migration Safety: For ACID Transactions, decide the migration safety before choosing tools; otherwise the design looks clean but fails when transactions changes.
• Data Ownership: For ACID Transactions, decide the data ownership before choosing tools; otherwise the design looks clean but fails when acid changes.

Production review question

If a team adopts ACID Transactions, what observable signal proves it helped instead of only making the architecture diagram more fashionable?

Practical metric to track

Track acid-transactions health as a named dashboard or review item: combine one user-facing metric, one saturation metric, and one correctness metric. For ACID Transactions, this keeps the discussion grounded in behavior that can be measured after deployment instead of generic advice that sounds good in an interview but never gets verified.