🛡️ Introduction: The Automatic Vending Machine
Imagine you walk up to a soda vending machine in the mall:
- Atomicity (All-or-Nothing): You insert a coin, select a drink, and the machine drops the soda. If the machine jams at the last second, it doesn't keep your money. It rolls back and spits your coin back out. You either get both the soda and lose the coin, or get nothing.
- Consistency (Valid Rules): The machine requires exactly $1.50 for a soda. You cannot cheat the machine and buy a drink with a $1.00 coin. It enforces rules.
- Isolation (Private Swapping): If two people try to buy a soda at the same time, the machine doesn't mix up their transactions. It processes one person, completes their swap, and then processes the next person.
- Durability (Permanence): Once the soda falls into the pickup bin, it is yours. Even if the mall loses power or the machine crashes a second later, the soda is physically in your hand.
In databases, ACID is the set of safety guarantees that ensure transactions are reliable and safe!
🏗️ The Four Pillars of ACID
A transaction must satisfy all four properties to be considered safe:
ACID TRUST
+-------------------------------------------------------------+
| ATOMICITY | CONSISTENCY | ISOLATION | DURABILITY |
+----------------+----------------+----------------+--------------+
| All-or-Nothing | Strict Schema | Private Transactions| Saved to Disk|
| Rollback on err| Constraints | Locking/MVCC | Write-Ahead |
| | | | Log (WAL) |
+-------------------------------------------------------------+
1. Atomicity (All-or-Nothing)
If any statement in a transaction fails, the entire transaction is aborted, and previous edits are rolled back.
- How it works: The database writes modifications to a temporary log before applying them to data pages. If a rollback is triggered, it reads the log backwards and reverses the writes.
2. Consistency (Rules & Constraints)
A transaction can only transition the database from one valid state (obeying all constraints, checks, and foreign keys) to another.
- How it works: If you try to insert an order with an invalid
customer_id(constraint violation), the transaction is rejected.
3. Isolation (Concurrency Control)
Ensures that concurrent execution of transactions leaves the database in the same state as if they were executed sequentially.
- How it works: Implemented using locking mechanisms or version controls (MVCC).
4. Durability (Disk Safes)
Guarantees that once a transaction commits, it will survive system crashes or power losses.
- How it works: The database uses a Write-Ahead Log (WAL). Changes are written to a sequential log file on disk before they are updated in memory pages, ensuring recovery on reboot.
💻 Code Examples: Transaction Isolation and Rollback
Let's simulate a transactional database operation with rollback handling.
SQL Syntax
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- Changes are saved and made durable!Multi-Language Execution
⚠️ Common Mistakes
1. Assuming Auto-Commit is Transaction Safe
Running multiple SQL queries in your code without wrapping them in BEGIN TRANSACTION and COMMIT. If your server crashes between query 1 and query 2, your database is corrupted!
2. Disabling Write-Ahead Logging (WAL)
Some developers disable WAL or set sync modes to off to speed up inserts. This breaks Durability—if the power cuts out, your database files will get corrupted and lose data.
🔍 Interview Corner
Q1: What does the "Atomicity" property of ACID guarantee?
Atomicity guarantees that a transaction is treated as a single, indivisible unit of work. Either all SQL operations within the transaction execute successfully, or the database is rolled back to its original state, leaving no half-finished updates.
Q2: What is the Write-Ahead Log (WAL), and which ACID property does it support?
The Write-Ahead Log (WAL) is a disk file where transactions are appended in order before they are applied to the database pages in memory. It supports Durability because it allows the database to replay the logs and recover unwritten changes after a crash.
📝 Summary
- ACID properties are the cornerstone of reliable database transaction design.
- Atomicity: All-or-nothing rollback.
- Consistency: Schema rules are maintained.
- Isolation: Transactions execute without interference.
- Durability: Committed data is safe on disk.