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

✓ = anomaly prevented | ✗ = anomaly possible

Concurrency anomaly definitions:

• Dirty Read: Reading uncommitted data from another transaction
• Dirty Write: Overwriting uncommitted data from another transaction
• Read Skew: Reading different versions of data in the same transaction (non-repeatable read)
• Lost Update: Two concurrent read-modify-write cycles, one overwrites the other
• Write Skew: Two transactions read overlapping data, make decisions, write different objects in conflicting ways
• Phantom Read: A query returns different results when re-executed because another transaction inserted/deleted rows

---

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