ACID Properties

Atomicity ensures all operations in a transaction succeed or all fail.

Atomicity Explained

Transaction: Transfer $100 from A to B

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If second UPDATE fails:
→ First UPDATE is rolled back
→ Database returns to original state
→ No partial update

Atomicity Implementation

Write-Ahead Logging (WAL):

1. Write changes to log (before disk)
2. Apply changes to database
3. If crash: replay log or undo

Log contains:
- Transaction ID
- Operation type
- Before image
- After image

Atomicity in Practice

-- Atomic: Both succeed or both fail
BEGIN;
  INSERT INTO orders (user_id, total) VALUES (1, 100);
  UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 1;
COMMIT;

-- If inventory update fails, order insert is rolled back

Atomicity Guarantees

With Atomicity:
✓ All operations complete
✓ No partial updates
✓ Database consistent on failure
✓ Automatic rollback on error

Without Atomicity:
✗ Partial updates possible
✗ Inconsistent state
✗ Manual cleanup needed
✗ Data corruption risk

Atomicity Limitations

1. Performance Overhead
   - Logging adds I/O
   - Rollback takes time

2. Long Transactions
   - Hold locks longer
   - Reduce concurrency

3. Distributed Transactions
   - Two-phase commit
   - Complex and slow

When Atomicity Matters

Critical:
- Financial transfers
- Order processing
- Inventory updates
- User account changes

Less Critical:
- Logging
- Analytics
- Cache updates
- Temporary data

Consistency ensures data satisfies all constraints after transaction.

Consistency Explained

Constraint: balance >= 0

Before: A=200, B=100
Transaction: Transfer $150 from A to B

A = 200 - 150 = 50 ✓
B = 100 + 150 = 250 ✓

If A would go negative:
→ Transaction rejected
→ Constraint violated
→ Database remains consistent

Types of Consistency

1. Entity Integrity
   - Primary keys unique
   - No duplicate IDs

2. Referential Integrity
   - Foreign keys valid
   - No orphan records

3. Domain Integrity
   - Data types correct
   - Values in valid range

4. Business Rules
   - Custom constraints
   - Application logic

Consistency Implementation

-- Constraints enforce consistency
CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  balance DECIMAL CHECK (balance >= 0)
);

-- Foreign keys
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  total DECIMAL
);

-- Triggers for complex rules
CREATE TRIGGER check_inventory
BEFORE INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION check_stock();

Consistency Guarantees

Before Transaction:
- Database in valid state
- All constraints satisfied

After Transaction:
- Database in valid state
- All constraints satisfied
- No constraint violations

Consistency Challenges

1. Application Logic
   - Database can't enforce all rules
   - Business logic in application

2. Distributed Systems
   - Eventual consistency
   - Temporary violations

3. Performance
   - Constraint checking overhead
   - Index maintenance

When Consistency Matters

Critical:
- Financial data (balance >= 0)
- Inventory (quantity >= 0)
- User data (unique emails)
- Relationships (valid foreign keys)

Less Critical:
- Logs (no constraints)
- Analytics (derived data)
- Cache (temporary data)

Isolation ensures concurrent transactions don't interfere with each other.

Isolation Explained

Without Isolation:

T1: Read balance = 100
T2: Read balance = 100
T1: Write balance = 150 (100+50)
T2: Write balance = 80 (100-20)

Result: T1's update lost!

With Isolation:
- T1 and T2 don't interfere
- Correct result achieved

Isolation Levels

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

Isolation Mechanisms

1. Locking
   - Pessimistic: Lock rows during access
   - Optimistic: Check conflicts at commit

2. MVCC
   - Multi-Version Concurrency Control
   - Each transaction sees snapshot
   - No read locks needed

3. Timestamps
   - Order transactions by timestamp
   - Serializable execution

MVCC in PostgreSQL

Each transaction gets snapshot:
- Sees data committed before snapshot
- Doesn't see uncommitted changes
- Readers don't block writers
- Writers don't block readers

Benefits:
- High concurrency
- No deadlocks from reads
- Consistent reads

Isolation Tradeoffs

Higher Isolation:
+ Better consistency
+ No anomalies
- Lower concurrency
- More locking
- Lower performance

Lower Isolation:
+ Higher concurrency
+ Better performance
- Possible anomalies
- Application must handle

Choosing Isolation Level

Read Committed (default):
- Most applications
- Good balance

Repeatable Read:
- Financial reports
- Consistent reads

Serializable:
- Critical operations
- No anomalies allowed

Isolation Best Practices

1. Start with Read Committed: Default for most databases
2. Increase only when needed: Higher levels reduce performance
3. Use MVCC: PostgreSQL, MySQL InnoDB
4. Monitor anomalies: Track dirty/non-repeatable reads
5. Test under load: Verify isolation under concurrency

Durability ensures committed data survives crashes.

Durability Explained

After COMMIT:
- Data written to disk
- Survives power failure
- Survives system crash
- Data permanently stored

Durability Mechanisms

1. Write-Ahead Logging (WAL)
   - Write to log before data
   - Log is sequential (fast)
   - Data pages written later

2. Checkpointing
   - Periodically flush data to disk
   - Reduce recovery time

3. Replication
   - Multiple copies
   - Survives disk failure

4. fsync
   - Force write to disk
   - Ensure data persisted

WAL Process

1. Transaction begins
2. Write changes to WAL (sequential)
3. Apply changes to buffer pool
4. Transaction commits
5. WAL flushed to disk (fsync)
6. Data pages written later

On crash:
- Replay WAL from last checkpoint
- Recover committed transactions
- Undo uncommitted transactions

Durability Configuration

-- PostgreSQL
synchronous_commit = on  # Wait for WAL flush
wal_sync_method = fsync  # Force to disk

-- MySQL
innodb_flush_log_at_trx_commit = 1  # Flush every commit
sync_binlog = 1  # Sync binlog every commit

Durability Tradeoffs

Strong Durability:
+ Data never lost
+ Guaranteed recovery
- Slower commits
- More I/O

Weak Durability:
+ Faster commits
+ Better performance
- Data loss on crash
- Acceptable for some use cases

Durability Guarantees

With Durability:
✓ Committed data survives crash
✓ Automatic recovery
✓ No data loss
✓ Consistent state after recovery

Without Durability:
✗ Data loss on crash
✗ Manual recovery
✗ Inconsistent state
✗ Possible corruption

When Durability Matters

Critical:
- Financial transactions
- Order processing
- User data
- Audit logs

Less Critical:
- Cache data
- Temporary data
- Analytics
- Logs