Understanding ACID Properties in Databases

What are ACID Properties?

ACID is an acronym that stands for four critical properties that guarantee reliable processing of database transactions:

• Atomicity: All or nothing
• Consistency: Rules are never violated
• Isolation: Transactions don't interfere
• Durability: Committed data is permanent

These properties work together to ensure data integrity and reliability in database systems.

---PAGE---

Atomicity: All or Nothing

Atomicity ensures that a transaction is treated as a single, indivisible unit of work. Either ALL operations in the transaction complete successfully, or NONE of them do.

Real-World Example

Imagine transferring $100 from Account A to Account B:

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

If the system crashes after deducting from Account A but before adding to Account B, atomicity ensures the entire transaction is rolled back. The money doesn't just disappear!

###Key Points

• Prevents partial updates
• Uses transaction logs for rollback
• Critical for maintaining data integrity

---PAGE---

Consistency: Maintaining Rules

Consistency ensures that a transaction brings the database from one valid state to another, maintaining all defined rules, constraints, and triggers.

Database Constraints

-- Example: Account balance cannot be negative
ALTER TABLE accounts
ADD CONSTRAINT check_positive_balance
CHECK (balance >= 0);

If a transaction would violate this constraint, it's automatically rolled back to maintain consistency.

Types of Constraints

1. Primary Keys: Uniqueness guaranteed
2. Foreign Keys: Referential integrity
3. Check Constraints: Custom business rules
4. Triggers: Automated consistency checks

---PAGE---

Isolation: Concurrent Transactions

Isolation ensures that concurrent transactions don't interfere with each other. Each transaction appears to execute in isolation, even when many run simultaneously.

Isolation Levels

Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	Yes	Yes	Yes
Read Committed	No	Yes	Yes
Repeatable Read	No	No	Yes
Serializable	No	No	No

Example Problem: Lost Update

-- Transaction 1
SELECT balance FROM accounts WHERE id = 1; -- Returns 1000
UPDATE accounts SET balance = 1000 + 100 WHERE id = 1;

-- Transaction 2 (running concurrently)
SELECT balance FROM accounts WHERE id = 1; -- Returns 1000
UPDATE accounts SET balance = 1000 - 50 WHERE id = 1;

Without proper isolation, one update could be lost!

---PAGE---

Durability: Permanent Changes

Durability guarantees that once a transaction is committed, it remains committed even in the case of system failures (power loss, crashes, etc.).

How It Works

1. Write-Ahead Logging (WAL)

   Transaction Log → Disk → Memory
   

2. Checkpoints: Periodic sync to disk

3. Recovery: Replay logs after crash

Database Recovery

After a crash, the database:

• Replays committed transactions from the log
• Rolls back uncommitted transactions
• Ensures no data loss

💡 Tip: Durability is why databases use transaction logs - they're the safety net for your data!

---PAGE---

---QUIZ--- TITLE: Test Your ACID Knowledge INTRO: Let's see how well you understood ACID properties!

Q: Which ACID property ensures that a transaction either completes fully or not at all? A: Atomicity A: Consistency A: Isolation A: Durability CORRECT: 0 EXPLAIN: Atomicity is the "all or nothing" property - either all operations in a transaction succeed, or none of them do.

Q: What does the Consistency property guarantee? A: Transactions execute quickly A: Data is never lost A: Database rules and constraints are never violated A: Multiple users can access data simultaneously CORRECT: 2 EXPLAIN: Consistency ensures that a transaction brings the database from one valid state to another, maintaining all defined rules, constraints, and triggers.

Q: Which isolation level prevents dirty reads but allows non-repeatable reads? A: Read Uncommitted A: Read Committed A: Repeatable Read A: Serializable CORRECT: 1 EXPLAIN: Read Committed isolation level prevents dirty reads (reading uncommitted data) but still allows non-repeatable reads and phantom reads.

Q: What mechanism primarily provides Durability in databases? A: In-memory caching A: Write-Ahead Logging (WAL) A: Table locks A: Index optimization CORRECT: 1 EXPLAIN: Write-Ahead Logging (WAL) ensures durability by writing transaction logs to disk before committing, allowing recovery after crashes.

Q: In a banking transaction transferring money between accounts, which ACID property prevents the money from disappearing if the system crashes mid-transfer? A: Atomicity A: Consistency A: Isolation A: Durability CORRECT: 0 EXPLAIN: Atomicity ensures that if the system crashes during the transfer, the entire transaction is rolled back, preventing partial updates where money could be deducted but not added. ---END-QUIZ---