PATTERN Cited by 1 source

Ordered commit without 2PC¶

Pattern¶

When a cross-shard DML must update an authoritative data table and one or more dependent tables (like routing indexes), order the commits across separate connections with carefully-chosen locking, rather than running 2PC on every DML. Pick an invariant weaker than full atomicity — one that user-facing queries can't violate — and accept that dependent tables may be briefly inconsistent with the authoritative table.

The canonical wiki instance is Vitess's Consistent Lookup Vindex: three MySQL connections (Pre, Main, Post) committed in a fixed order, with rollback in the same order.

Why not 2PC¶

2PC (Two-Phase Commit) guarantees atomicity across participants: all participants commit or all abort. It requires:

a prepare phase on every participant (extra round-trip);
a transaction coordinator to collect prepare votes and decide the outcome;
participants holding locks through the coordinator's decision window.

For high-throughput sharded OLTP, this cost is paid on every DML, and coordinator failure during the decision window leaves participants in an in-doubt state that typically requires operator intervention.

"To maintain consistency between the user data table and the Vindex tables, all these operations will need to occur in a transaction that spans multiple shards. This means we need to implement a costly protocol like 2PC (Two Phase Commit) to guarantee Atomicity and Isolation."

— sources/2026-04-21-planetscale-achieving-data-consistency-with-the-consistent-lookup-vindex

The article asserts 2PC is "costly" but doesn't quantify the cost in numbers. The argument is qualitative + widely accepted in the distributed-database literature.

The mechanism: three connections, ordered commit¶

Vitess opens three MySQL connections per DML:

Pre: reserved for inserts into the dependent table (lookup-Vindex table) and for orphan-row reclamation updates.
Main: reserved for the authoritative user-table DML.
Post: reserved for deletes on the dependent table (lookup-Vindex table).

On commit: Pre → Main → Post. On failure: rollback the open transactions in the same order.

Per-DML commit sequences¶

Delete (authoritative delete + dependent delete):

Main:   SELECT ... FOR UPDATE            (read current row's indexed columns)
Post:   DELETE from lookup_tables        (enqueued, not committed)
Main:   DELETE from user_table           (enqueued, not committed)

On commit: Pre (no-op) → Main (user-row gone) → Post (lookup-rows gone)

If Main succeeds and Post fails: the user row is gone, the lookup rows remain → orphan situation. User queries stay correct because they route through the lookup but filter on the user table.

Insert (dependent insert + authoritative insert):

Pre:    INSERT into lookup_tables        (enqueued; may hit duplicate-key)
Main:   INSERT into user_table           (enqueued)

On commit: Pre → Main → Post (no-op)

On Pre duplicate-key error (a leftover orphan on a unique lookup column):

Pre:    SELECT ... FOR UPDATE on the orphan
Main:   SELECT ... FOR UPDATE on user_table (confirms no user row)
Pre:    UPDATE lookup_table SET keyspace_id = new_id

The double FOR UPDATE (orphan + absent user row) makes reclamation race-safe against concurrent DMLs.

Update of Vindex column: delete-then-insert. Delete rides Post, insert rides Pre.

Invariant: the authoritative table wins¶

The pattern works because one participant is authoritative for user-facing queries. Dependent tables exist only to accelerate routing; the final answer is always a filter on the authoritative table. This is exactly the invariant the post names:

"The lookup Vindex table may be inconsistent with the user table but the results returned for the query remained consistent with the user table."

The pattern is not general to all cross-shard transactions — it requires an asymmetry in the participant roles that makes the weaker invariant observable-equivalent to atomicity for the use case.

Trade-offs¶

Cheaper happy path: no PREPARE phase, no coordinator round-trip, no in-doubt state on coordinator failure.
Weaker guarantee: dependent tables can be briefly (or indefinitely) inconsistent with the authoritative table. The user-facing-query invariant must hold — if any code path bypasses the authoritative table (e.g. reads directly from the dependent table as if it were a materialised view of the user table), this pattern fails.
Self-healing on unique dependent columns: duplicate-key errors on later inserts drive lazy orphan reclamation. Not self-healing on non-unique dependent columns — orphans on those can accumulate (see concepts/orphan-lookup-row caveats).
Hard-coded corner cases: Vitess turns identity-value updates on Vindex columns into no-ops to avoid the single-transaction deadlock-against-itself that the Post-delete + Pre-insert pair would otherwise cause. The two-separate-statements-in-one-transaction variant is an unresolved limitation.
Locking cost: SELECT ... FOR UPDATE on every DML to serialise concurrent mutations. The pattern inherits row-level lock contention costs from the host engine.

Generalisation beyond Vitess¶

The pattern generalises to any system with:

One authoritative table that user-facing queries always consult.
One or more derived or routing tables whose role is to narrow down which authoritative-table partition to read.
A failure model where "derived table is stale relative to authoritative" is acceptable because user queries re-filter on the authoritative table anyway.

Related patterns in the wiki:

Event-log + rollup counters (slotted counter, Netflix Distributed Counter) take a similar design stance — authoritative event log + derived rollups that can lag — but over a single-table, not cross-table, schema.
Write-ahead log + checkpoint (any LSM / B-tree storage engine) has the same asymmetry: WAL is authoritative, on-disk pages are derived; recovery replays WAL to reconcile.

Ordered-commit-without-2PC is the cross-shard-writes altitude of this broader family.

Seen in¶

sources/2026-04-21-planetscale-achieving-data-consistency-with-the-consistent-lookup-vindex — canonical wiki instance of the pattern as the three-connection Consistent Lookup Vindex mechanism in Vitess. Delete / insert / update DML flows with explicit commit-and-rollback ordering; the user-table-authoritative invariant that makes the weaker guarantee sufficient; the duplicate-key-reclamation path for self-healing orphans on unique dependent columns; the identity-update no-op optimisation and its two-statements-in-one-transaction limitation.