Comparisons and explanations · Intermediate

MVCC versus two-phase locking

Compare version-based visibility with two-phase locking, including serialization guarantees, reader/writer interaction and implementation boundaries.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. Compare mechanisms at a consistent level
  2. The two phases are acquisition and release
  3. MVCC selects visible versions
  4. A stable snapshot is not serializability by itself
  5. Compare waiting costs and version-retention costs
  6. Verify commands and isolation in the target product
  7. Ask Logeia about this articleCheck your understanding

Compare mechanisms at a consistent level

MVCC is a broad family of mechanisms that uses several data versions to determine what a read can see. Two-phase locking constrains lock acquisition and release to control concurrent execution. They are not necessarily opposite product categories: a database using versions can still use locks for updates or schema operations. Specify whether the comparison concerns ordinary reads, row updates, multirow predicates, or schema changes. Neither “MVCC means no locks” nor “using a lock means implementing two-phase locking” is a sufficient explanation. The precise operation and guarantee determine which waiting relationships and anomalies are possible in an actual system.

The two phases are acquisition and release

Basic two-phase locking separates a growing phase that acquires locks from a shrinking phase that releases them; acquiring a new lock after release has begun violates the rule. With appropriate conflict protection, this discipline provides a basis for conflict serializability. It does not automatically eliminate deadlock: one transaction can hold resource one while waiting for two as another holds two while waiting for one. Variants such as strict two-phase locking strengthen which locks are retained until transaction completion. These phases are not the prepare and decision phases of a distributed two-phase commit protocol, despite the similar names.

MVCC selects visible versions

When a row changes from ten to twenty, a version-based system may keep ten available for a reader that still needs the older version. Readers and writers can therefore interact differently from a design that makes them contend for one shared representation under locks. Which version is chosen depends on the product and isolation level. In PostgreSQL, ordinary Read Committed SELECT statements use statement snapshots, whereas Repeatable Read uses a stable transaction snapshot. The name MVCC alone does not predict the default behavior of every product. Ask which version a specific command can observe and which concurrent modifications still create blocking or transaction failure.

A stable snapshot is not serializability by itself

Consider two on-call doctors who each check that the other remains available before switching their own row off. Under snapshot isolation, two transactions writing different rows can sometimes both commit and leave no doctor available. Stable read values alone do not prevent that result. A lock-based design must also protect the correct rows or ranges to enforce the intended invariant. PostgreSQL Serializable combines MVCC behavior with dependency monitoring and can abort a transaction to avoid a nonserializable outcome. Compare guarantees over committed histories rather than assuming that a particular storage or locking label completely specifies the application’s concurrency contract.

Compare waiting costs and version-retention costs

Locking designs require attention to wait relationships, hold durations, and deadlock handling. Versioned designs also pay to retain older representations and reclaim them safely. A long reader may interfere less directly with an update while still keeping an old snapshot that delays cleanup. Conversely, MVCC updates can still block one another, so storage accounting alone is insufficient. Specify the read/write mix, transaction duration, contended resources, and tolerated abort or retry behavior. Performance comparisons must preserve the same required isolation contract and completed work. Otherwise a faster result may simply provide a weaker guarantee or omit transactions that failed to commit.

Verify commands and isolation in the target product

For a two-connection experiment, record commands, start order, values read, rows changed, and commit order. Preserve the business invariant being checked as well as whether a statement waited, committed, or aborted. If retry is necessary, determine which earlier reads and decisions must be repeated and how external effects such as email or payment avoid duplication. The examples here are reasoning schedules, not reported database benchmark runs. Consult the target product and version before deploying a conclusion. Similar terminology can coexist with materially different visibility, locking, cleanup, and failure behavior, so the useful comparison ends in an explicit operation-level contract.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 13

Choose a learning path