Three Ways Databases Make Transactions Serializable

Serializability is a guarantee, not one algorithm

The promise of serializable isolation is simple: even if transactions run concurrently, the result must match some order in which they ran one at a time. The implementation is where things become interesting.

The problem appears whenever two transactions read a fact, make a decision, and then write. Under weak isolation, each decision may look valid alone while their combined result breaks a business rule. Databases have three main ways to prevent that: remove concurrency, block dangerous overlap, or allow concurrency and reject unsafe executions.

💡 Serializable is not the same as serial execution. Serializable describes the result. Actual serial execution, two-phase locking, and Serializable Snapshot Isolation are different ways to produce that result.

1. Actual serial execution removes the race

The most direct solution is to execute one complete transaction at a time on a single thread. If transactions never overlap, they cannot observe one another halfway through a decision.

This sounds slow, but a purpose-built engine avoids lock coordination. It works when transactions are short, the active working set is in memory, and write throughput fits on one CPU core or partitions cleanly.

Why interactive transactions are a problem

Suppose an application sends a SELECT, waits for the result, decides what to do, and then sends an UPDATE. A strict serial executor cannot run another transaction during those network waits without giving up one-at-a-time execution. Its only execution thread would sit idle.

Systems built around serial execution therefore benefit from receiving the complete transaction as one stored-procedure call:

flowchart LR
  A[Separate SELECT] --> B[Network wait]
  B --> C[Application decision]
  C --> D[Separate UPDATE]
  E[One procedure call] --> F[Read decide write]
  F --> G[Commit]

💡 A normal BEGIN, SELECT, UPDATE, COMMIT transaction is still valid. Stored procedures matter here because they remove repeated network waits from a serial executor. Stored procedures alone do not imply serial execution.

Stored procedures also bring operational costs. Database vendors use different languages, and procedure code can be harder to debug, test, deploy, and monitor than application code. A procedure that consumes too much CPU or memory also runs inside the database, giving mistakes a larger blast radius.

Replication and partitioning

VoltDB demonstrates two useful consequences. First, it can replicate the stored-procedure call and its parameters instead of copying every resulting write. Each replica must execute the procedure deterministically, so uncontrolled time or randomness cannot produce different results.

Second, each partition can have its own serial executor. Independent single-partition transactions run in parallel across cores. A transaction touching several partitions must coordinate those executors, so the cross-partition path is much slower and does not gain the same linear scalability.

flowchart TD
  A[Transaction] --> B{One partition}
  B -->|Yes| C[One serial executor]
  C --> D[Fast independent commit]
  B -->|No| E[Coordinate executors]
  E --> F[Higher latency and lower throughput]

The limitations are strict: one slow procedure stalls its partition, disk I/O can stop the loop, and cross-partition work becomes a bottleneck. Anti-caching is one escape hatch: abort or pause a transaction that misses memory, fetch the cold data asynchronously, and retry after it is resident.

2. Two-phase locking prevents dangerous overlap

Two-phase locking, or 2PL, allows transactions to overlap but forces conflicting operations to wait. Readers acquire shared locks, while writes require exclusive locks. Multiple readers may coexist, but a reader and writer cannot use the same protected object concurrently.

If a transaction reads and later writes the same object, it upgrades its shared lock to an exclusive lock. Under strict 2PL, conflicting locks remain held until commit or rollback.

💡 The two phases mean locks are acquired and later released. They do not mean BEGIN and COMMIT. Also, 2PL is unrelated to two-phase commit, or 2PC, which coordinates commit across nodes.

Blocking creates a different failure mode

Imagine transaction A holds row 1 and waits for row 2, while transaction B holds row 2 and waits for row 1. Neither can continue. The database detects this deadlock, aborts one transaction, and expects the application to retry it.

Even without deadlocks, a slow transaction can hold many locks and create a queue. This gives 2PL strong correctness but unpredictable tail latency, reduced concurrency, aborted work, and retry overhead.

Rows are not enough when the rule concerns absence

A booking query may find no reservation for room 123 between noon and 1 p.m. With no matching row, there is nothing to row-lock. Another transaction can insert an overlapping booking after the check. That new matching row is a phantom, and both bookings may be accepted.

flowchart TD
  A[Query finds no booking] --> B[No existing row to lock]
  B --> C[Concurrent insert matches query]
  C --> D[Overlapping booking accepted]
  A --> E[Predicate or index range lock]
  E --> F[Conflicting insert waits]

A predicate lock protects the full search condition, including future matching rows. Exact predicates are expensive, so databases commonly approximate them with index-range or next-key locks. The protected range may include harmless rows, but checking a B-tree range is cheaper than comparing every write against many logical predicates. Without a useful index, a whole-table shared lock is a safe but costly fallback.

💡 When a missing row cannot be locked, a constraint may express the rule directly. Other options include serializable isolation or locking a stable parent row, such as the room, provided every competing code path follows the same convention.

3. SSI detects danger without making readers wait

Serializable Snapshot Isolation, or SSI, keeps snapshot isolation's consistent reads and adds dependency tracking. Transactions proceed optimistically. At commit, the database aborts an execution that cannot fit into a safe serial order.

The doctors example shows the problem. Alice and Bob are both on call. Two transactions read that fact. One turns Alice off; the other turns Bob off. They update different rows, so ordinary write-conflict detection sees no collision, yet the final result leaves nobody on call.

flowchart TD
  A[T42 reads Bob true] --> B[T42 changes Alice]
  C[T43 reads Alice true] --> D[T43 changes Bob]
  D --> E[T42 read is invalidated]
  B --> F[T43 read is invalidated]
  E --> G[Dangerous dependency cycle]
  F --> G
  G --> H[Abort one transaction]
  H --> I[Retry with fresh snapshot]

Two ways a premise becomes stale

First, a snapshot read may ignore another transaction's uncommitted MVCC version. If that ignored write commits before the reader commits, the reader's premise has become stale. SSI waits until commit because the reader may remain read-only or the other writer may still abort.

Second, a later write may affect a range that another transaction already read. SSI records the earlier read on an index entry, or more broadly at table level. When a write touches that range, the marker acts as a tripwire. Unlike a 2PL range lock, it does not block the writer. The dependency is evaluated when transactions commit.

💡 An MVCC update commonly leaves an old and a new row version. A snapshot may still see Alice's old on_call = true version while another transaction has created an uncommitted false version. A conflict notification is internal bookkeeping; the application normally sees only a serialization failure if its transaction is chosen for abort.

SSI trades waiting for retries

Fine-grained dependency tracking avoids unnecessary aborts but costs more bookkeeping. Coarser tracking is cheaper but may reject safe work. SSI performs best with modest contention and short read-write transactions. Under heavy contention, aborted transactions retry and add load. Long read-only snapshot queries are usually less troublesome because they do not create write skew.

💡 Retry the entire transaction after a serialization failure or deadlock. Keep external side effects, such as sending an email, outside a retryable transaction body unless they are idempotent.

The takeaway

Actual serial execution removes overlap and works when transactions are tiny and partition-local. Two-phase locking prevents dangerous overlap but pays through blocking and deadlocks. SSI preserves concurrency and predictable reads but pays through tracking and aborts.

The practical question is not which mechanism sounds strongest. It is whether concurrent transactions can jointly break a business invariant, and which protection expresses that rule with acceptable cost.

These are my personal learning notes from Designing Data-Intensive Applications by Martin Kleppmann.