# ACID Properties ## Overview **ACID** is an acronym for the four key properties that every database transaction must satisfy to ensure data validity even in cases of errors, power failures, and concurrent access. ``` A — Atomicity C — Consistency I — Isolation D — Durability ``` --- ## A — Atomicity ### Definition A transaction is treated as a **single indivisible unit**. Either ALL operations in the transaction are executed successfully, or NONE of them are (all-or-nothing). ### Example ``` Bank Transfer: ₹10,000 from Account A to Account B Operations: 1. Debit A: A = 50,000 − 10,000 = 40,000 → write(A) 2. Credit B: B = 30,000 + 10,000 = 40,000 → write(B) Case 1 — Both succeed: A = 40,000, B = 40,000 ✓ Committed Case 2 — Step 1 done, system crashes before Step 2: Without Atomicity: A = 40,000, B = 30,000 ← ₹10,000 disappeared! With Atomicity: ROLLBACK → A = 50,000, B = 30,000 ✓ Consistent ``` ### Enforced By - **Transaction Manager** using the **undo log** - On failure, DBMS undoes all writes of the aborted transaction --- ## C — Consistency ### Definition A transaction takes the database from one **valid, consistent state** to another valid, consistent state. All defined rules, constraints, and integrity conditions must hold before and after the transaction. ### Example ``` Bank Rule (Constraint): Total money in the system must remain constant. Total before = A + B = 50,000 + 30,000 = 80,000 After Transfer: A = 40,000, B = 40,000 Total after = 80,000 ✓ Consistent After Rollback (if failure): A = 50,000, B = 30,000 Total = 80,000 ✓ Still consistent ``` ### Types of Consistency ``` Integrity Constraints: - Primary key uniqueness - Foreign key referential integrity - CHECK constraints (e.g., salary > 0) - NOT NULL constraints Business Rules: - Sum of debits = sum of credits - Stock quantity cannot be negative - Order total = sum of line items ``` ### Enforced By - **Application logic** (developer responsibility) - **Database integrity constraints** (CHECK, FK, UNIQUE, NOT NULL) --- ## I — Isolation ### Definition Concurrent transactions execute **independently** of each other. The intermediate (incomplete) state of a transaction is **not visible** to other transactions. Each transaction appears to execute as if it were the only transaction running. ### Example ``` T1: Transfer A→B ₹10,000 T2: Read balance of A ──────────────────────── ─────────────────────── T1: read(A) = 50,000 T1: write(A = 40,000) ← partially done, not committed T2: read(A) = ??? WITHOUT Isolation: T2 reads A = 40,000 (dirty read — T1 might rollback!) WITH Isolation: T2 reads A = 50,000 (sees committed value only) ``` ### Isolation Problems (without proper control) | Problem | Description | |---------|-------------| | Dirty Read | Reading uncommitted data from another transaction | | Non-Repeatable Read | Same query returns different values within a transaction | | Phantom Read | New rows appear/disappear between two reads of same query | | Lost Update | One transaction's write overwritten by another | ### Isolation Levels (SQL Standard) ``` READ UNCOMMITTED → No isolation; all problems possible READ COMMITTED → Prevents dirty reads REPEATABLE READ → Prevents dirty + non-repeatable reads SERIALIZABLE → Prevents all problems; highest isolation ``` ### Enforced By - **Concurrency Control Manager** using **locks** or **MVCC** --- ## D — Durability ### Definition Once a transaction is **committed**, its changes are **permanent** — they survive any subsequent system failures (crashes, power loss, hardware failures). ### Example ``` T1 commits at 2:00 PM (successfully transfers ₹10,000) System crashes at 2:01 PM WITHOUT Durability: Data reverts to state before T1's commit WITH Durability: On recovery, T1's changes are restored from log A = 40,000, B = 40,000 is guaranteed ``` ### Enforced By ``` Write-Ahead Logging (WAL): - All changes written to log BEFORE writing to data pages - Log is on stable storage (survives crashes) - On recovery: redo all committed transactions from log Checkpoints: - Periodic flushing of dirty pages to disk - Reduces log scanning needed during recovery ``` --- ## ACID in Practice ``` Property | Responsibility | Mechanism ───────────|─────────────────────|────────────────────────── Atomicity | Transaction Manager | Undo log, Rollback Consistency| App + DB Constraints| CHECK, FK, Business logic Isolation | Concurrency Manager | Locks, MVCC, Timestamps Durability | Recovery Manager | Write-ahead log, Checkpoints ``` --- ## BASE vs ACID (NoSQL Comparison) NoSQL databases often trade ACID for scalability using **BASE**: ``` BASE = Basically Available, Soft state, Eventually consistent ACID: Strong consistency, Low scalability for distributed systems BASE: Eventual consistency, High scalability, High availability ``` Traditional relational databases follow ACID. Modern NoSQL systems (Cassandra, DynamoDB) follow BASE for better performance at scale.