Chapter 7: Transactions

Core Concepts

What is a Transaction?

• Group of reads and writes as a single logical operation
• Provides safety guarantees
• All-or-nothing semantics

ACID Properties

BASE vs ACID

• BASE: Basically Available, Soft state, Eventual consistency
• ACID: Strong consistency, pessimistic approach
• Trade-off: Consistency vs Availability

---

Single-Object vs Multi-Object Operations

Single-Object Operations

• Read-modify-write cycles
• Need atomicity for single object

Multi-Object Operations

• Read/write across multiple objects
• Need atomicity for entire transaction

---

Isolation Levels

Read Committed

• See only committed data
• No dirty reads
• No dirty writes

Snapshot Isolation (Repeatable Read)

• Each transaction sees consistent snapshot
• No dirty reads or writes
• Uses Multi-Version Concurrency Control (MVCC)

Read Skew

• Reading different objects at different times
• Inconsistent view of data

Write Skew

• Two transactions read same data, write different objects
• Violation of consistency constraints

Serializable Isolation

• Strictest level
• Transactions appear to execute sequentially
• Most expensive performance-wise

---

Implementing Snapshot Isolation

Multi-Version Concurrency Control (MVCC)

• Each transaction gets consistent snapshot
• Old versions kept for concurrent readers
• Garbage collection of old versions

Indexes and Snapshot Isolation

• Append-only B-trees: No locking needed
• Indexes point to multiple versions
• Readers never block writers

---

Preventing Lost Updates

Atomic Write Operations

• Database prevents modification during read-modify-write

Compare-and-Set (CAS)

• Optimistic concurrency control
• Fail if data changed since read

Pessimistic vs Optimistic

• Pessimistic: Lock before accessing
• Optimistic: Allow conflicts, detect and retry

---

Weak Isolation Levels

Read Committed

• Most common
• Prevents dirty reads/writes
• Fast performance

Snapshot Isolation

• Consistent snapshots
• Better for read-heavy workloads
• Write skew possible

Conflict Resolution

• Last Write Wins (LWW): Simple but loses data
• Conflict-free Replicated Data Types (CRDTs): Mathematical guarantees

---

Serializability

Actual Serial Execution

• Single-threaded execution
• Simple but limits throughput

Two-Phase Locking (2PL)

• Shared locks for reads
• Exclusive locks for writes
• Blocks concurrent access

Serializable Snapshot Isolation (SSI)

• Optimistic approach
• Detect conflicts at commit time
• Based on snapshot isolation

---

Key Takeaways

1. ACID provides strong consistency guarantees
2. Isolation levels trade off consistency for performance
3. Snapshot isolation is widely used and provides good balance
4. Write skew is a subtle bug that requires careful handling
5. Serializable isolation is expensive but prevents all anomalies