Serializable Isolation: Implementation Strategies and Costs

Two-Phase Locking (2PL)
The traditional approach to Serializable isolation. Transactions acquire read and write locks on accessed rows, holding them until commit. Predicate locks (or index range locks) prevent phantoms by locking not just existing rows but also the gaps between index keys where new rows could be inserted. Conflicting transactions block until locks are released. 2PL provides strong guarantees with predictable wait times but risks deadlocks when two transactions each hold locks the other needs. The database detects deadlocks and aborts one transaction to break the cycle.
Serializable Snapshot Isolation (SSI)
SSI is the modern alternative. Transactions run on MVCC snapshots so readers do not block writers. The database tracks read-write dependencies: which rows each transaction read and which it wrote. If two concurrent transactions form a conflict cycle (A read data B wrote, and B read data A wrote), the database aborts one to prevent non-serializable behavior. Performance is excellent when conflicts are rare. At high write rates on hot keys (over 5,000 updates/sec), abort rates can climb to 5-10%, requiring application retry logic.
Timestamp-Based Ordering
Some distributed databases use physical timestamps for serialization. Each transaction gets a globally-ordered timestamp from synchronized clocks. Writes wait briefly (the "commit wait") until clock uncertainty is bounded, ensuring no transaction with a later timestamp could have started before this one. This provides strict serializability: the database order matches real-world time order. Single-region commits add 5-10 ms wait; cross-region transactions pay 80-150 ms plus coordination overhead.
Optimistic Concurrency Control
Optimistic CC assumes conflicts are rare. Transactions execute without acquiring locks, recording their read and write sets. At commit time, the database validates that no conflicts occurred (no other transaction modified data this one read). If validation fails, the transaction aborts and must retry. Works well for read-mostly workloads. Under contention, cascading retries can collapse throughput. Combine with limited retry counts and exponential backoff (waiting longer between each retry attempt).
Choosing an Implementation
2PL: predictable blocking, good for moderate contention, prone to deadlocks. SSI: high throughput at low contention, aborts under conflict. Timestamp ordering: strict consistency, higher latency. Optimistic CC: best for rare conflicts, cascading failures under contention. Most applications should use their database default and only tune for measured bottlenecks.

💡 Key Takeaways

✓Two Phase Locking holds read and write locks until commit and adds predicate locks for phantoms, blocking conflicting transactions and reducing throughput 2 to 5 times under contention

✓Serializable Snapshot Isolation (PostgreSQL, FoundationDB) tracks read write dependencies and aborts on cycles, keeping reads non-blocking but seeing 5 to 10 percent abort rates at over 5,000 updates per second on hot keys

✓Google Spanner TrueTime provides strict serializability via commit wait (typically under 7 milliseconds) plus clock uncertainty, cross region transactions pay 80 to 150 milliseconds for wide area network round trips

✓Apple uses FoundationDB for iCloud metadata with optimistic concurrency, achieving few millisecond median latency but 10 to 20 percent aborts under coarse conflict ranges

✓Most production systems use Serializable selectively for critical write paths (money transfers, unique allocation) and Read Committed or snapshots for read heavy endpoints to gain 2 to 5 times throughput

✓Operational practice: monitor abort/retry rates for SSI, lock wait times and deadlocks for 2PL, commit wait duration for time based systems, and keep transactions short with narrow read/write sets

📌 Interview Tips

1PostgreSQL Serializable: Banking transfer reads two account balances (snapshot), updates both, SSI detects concurrent transfer conflict and aborts one for retry

2Google Spanner: Advertising auction transaction commits with 5 millisecond commit wait in single region, cross continent auction takes 120 milliseconds (80 millisecond RTT plus 7 millisecond wait plus consensus)

3FoundationDB at Apple: Metadata update with read set spanning 100 keys sees 15 percent abort rate, narrowing to 10 keys drops aborts to under 3 percent

4MySQL InnoDB Serializable with 2PL: Hot secondary index range query (top 100 posts by score) acquires gap locks, serializing concurrent inserts and dropping insert throughput from 8,000 per second to 2,000 per second

← Back to Transaction Isolation Levels Overview