ACID Transactions

Concept

When you transfer $100 from Alice to Bob, the database must perform two actions: deduct $100 from Alice, and add $100 to Bob. If the server crashes exactly between those two actions, the $100 vanishes into thin air.
ACID Transactions prevent this. ACID is a set of properties (Atomicity, Consistency, Isolation, Durability) that guarantee database transactions are processed reliably, even in the event of hardware crashes or network failures.

The 4 Pillars of ACID

1. Atomicity (All or Nothing)

A transaction is treated as a single, indivisible unit of work. If a transaction consists of 5 SQL statements, and statement #4 fails, the database instantly rolls back statements 1, 2, and 3. The database state remains exactly as it was before the transaction started.

2. Consistency (Following the Rules)

The database enforces its own rules (Constraints, Cascades, Triggers). If a column is defined as NOT NULL, or a foreign key must exist, the database will categorically refuse to commit any transaction that violates these rules. It ensures the database moves from one valid state to another valid state.

3. Isolation (No Peeking)

When thousands of users are reading and writing to the database simultaneously, transactions must not interfere with each other. If Alice is halfway through transferring $100 to Bob, a background analytics query should not be able to “peek” at Alice’s temporarily lower balance until the transaction is fully committed. (This prevents Dirty Reads).

4. Durability (Permanent Storage)

Once the database replies “Transaction Committed Successfully”, that data is permanently saved to a non-volatile hard drive (SSD/HDD). Even if someone pulls the power plug out of the server 1 millisecond later, the data will still be there when the server boots back up.

Mental Model

How It Works: The Write-Ahead Log (WAL)

How does a database guarantee Durability and Atomicity if it crashes mid-write?
Relational databases use a Write-Ahead Log (WAL). Before modifying the actual database tables on the hard drive, the database quickly appends a note to the end of a simple, sequential log file saying “I am about to deduct $100 from Alice”.
If the database crashes while updating the tables, it boots back up, reads the WAL, realizes the transaction didn’t finish, and uses the log to roll back the changes safely.

Interview Questions

Q: In highly scalable distributed systems, why do we often avoid strict ACID transactions?
A: Because of the Isolation property. To guarantee that transactions don’t interfere with each other, databases use Locks. When Alice updates her row, the database puts a lock on it so no one else can touch it until she finishes. In a massive microservices architecture distributed across the globe, holding locks across multiple network hops causes horrific performance bottlenecks. To scale, we usually trade strict ACID Isolation for eventual consistency (BASE).

Q: What are the different Isolation Levels in SQL, and what is the trade-off?
A: The SQL standard defines 4 isolation levels, from lowest to highest:

1. Read Uncommitted: (Fastest, but allows Dirty Reads).
2. Read Committed: (Default in Postgres. Cannot read uncommitted data).
3. Repeatable Read: (Guarantees if you read a row twice in a transaction, it won’t change).
4. Serializable: (Slowest, highest safety. Transactions execute as if they were strictly sequential, one after the other).
   The trade-off is always Performance vs Correctness. Higher isolation requires more locks, reducing the number of concurrent users your system can handle.