Serializable Transactions

📋 Overview

Serializable isolation is the highest level of isolation defined by the SQL standard. It guarantees that the execution of a set of concurrent transactions produces the same result as some serial (sequential) execution of those transactions. While it provides the strongest consistency guarantees, it traditionally comes with significant performance overhead due to the need for extensive locking or complex conflict detection.

---

🏗️ Core Principles & Characteristics

• Sequentiality: Transactions appear to execute one after another, even if they run concurrently in reality.
• Anomaly Prevention: Eliminates all concurrency-related anomalies, including:
  • Dirty Reads: Reading uncommitted data.
  • Non-repeatable Reads: Data changing between two reads in the same transaction.
  • Phantom Reads: New rows appearing in a range query between two reads.
  • Write Skew: A subtle anomaly where two transactions read overlapping data sets but update disjoint data sets in a way that violates a cross-record constraint.
• Snapshot Isolation (SI) vs. Serializable Snapshot Isolation (SSI):
  • SI: Each transaction reads from a consistent snapshot. It prevents most anomalies but is vulnerable to Write Skew.
  • SSI: An optimistic approach that tracks read-write dependencies. If a potential non-serializable cycle is detected, one of the transactions is aborted.

---

⚖️ Trade-offs: Pros & Cons

Pros

• Maximum Data Integrity: Ideal for financial systems or inventory management where consistency is non-negotiable.
• Developer Simplicity: Developers don't need to worry about complex race conditions; the database handles the isolation logic.
• SSI Efficiency: Modern implementations like SSI in PostgreSQL provide serializability with performance close to Snapshot Isolation.

Cons

• High Abort Rates: Under high contention (many transactions trying to update the same data), the system may frequently abort and retry transactions.
• Performance Overhead: Tracking dependencies and managing locks increases CPU and memory usage.
• Increased Latency: The overhead of validation at commit time can increase the total response time of a transaction.

---

🌍 Real-World Implementation

• PostgreSQL: Uses Serializable Snapshot Isolation (SSI). It tracks "si-reads" and "si-writes" to detect potential dependency cycles without blocking reads.
• FoundationDB: A distributed database that provides serializable transactions across the entire cluster using an optimistic concurrency control mechanism.
• CockroachDB: Uses a variant of SSI to provide serializable isolation in a globally distributed environment.

---

💡 Interview "Gotchas" & Tips

• The Write Skew Trap: Many candidates think Snapshot Isolation (SI) is the same as Serializable. It is not. Always mention that SI is vulnerable to Write Skew (e.g., the "two doctors on call" problem).
• Optimistic vs. Pessimistic: Be prepared to discuss the difference between locking-based serializability (pessimistic) and validation-based serializability (optimistic).
• Retries are Mandatory: In a serializable system, your application code must be prepared to catch "serialization failure" errors and retry the transaction.

---

📐 Suggested Architecture Primitives

• Conflict Detection Engine: A component that tracks the read-sets and write-sets of active transactions.
• Dependency Graph: Used in SSI to identify cycles that indicate non-serializable execution.
• Versioning (MVCC): Multi-Version Concurrency Control is the foundational technology that allows snapshots to exist without blocking reads.