Follow a transaction through three system designs

The shop stores order 501 under customer 7. An application changes the customer to 9 and maintains an index for finding orders by customer. After a successful transaction, the order and index must agree.

This example connects mechanisms we already know: representation, atomic updates, concurrency checks, replication, and recovery. Spanner, CockroachDB, and FoundationDB organize those responsibilities differently. We will compare the boundaries rather than declare one universally faster.

Name the three changes before choosing a product

In a simplified key/value representation, the index is an extra key that points to the order. Changing the customer requires:

Remove the old index entry
Delete customer/7/order/501.
Change the order
Set order/501.customer to 9.
Add the new index entry
Create customer/9/order/501.

These three logical changes belong to one transaction. Publishing only a subset produces a misleading lookup.

The keys are illustrative; a real encoding must preserve the ordering and type distinctions its queries require. Concurrent updates must also participate in the transaction’s conflict rules.

Ask which layer maintains the index

FoundationDB supplies an ordered transactional key/value substrate. In its 2021 architecture paper, richer data models and indexes are responsibilities of layers above that substrate. A layer can use one transaction to read the order and perform the three mutations. It must correctly declare the reads and writes used for conflict checking and handle retries. The underlying transaction service does not invent the layer’s missing index update.

CockroachDB supplies a SQL interface over distributed storage. Its SQL and transaction layers handle relational operations and coordinate their execution across the underlying key ranges. An application declaring a supported SQL index delegates its maintenance to the database rather than manually writing the illustrative keys above.

The original Spanner paper describes a geographically distributed transactional database using replicated data and bounded clock uncertainty. Its ordering guarantees come from protocols that account for that uncertainty, including waiting where required; clocks do not remove replication or atomic-commit obligations.

These are different ways to package the mechanisms. Exact isolation options, transaction limits, and interfaces must be checked for the chosen product and version.

Keep the workload in the comparison

If all three changes are near one authority, the communication path can be short. If they span owners or regions, the transaction may require more coordination. A local index benchmark does not measure that full commit path.

Failure tests should include a lost response after commit and a conflict requiring a retry. A retried transaction must not accidentally repeat an external shipment or charge.

Predict the missing mutation

A custom FoundationDB layer updates order/501 and adds the customer-9 index entry in one transaction, but forgets to delete the customer-7 entry. Will atomic commit repair the omission?

Separate execution guarantees from application intent

No. Atomic commit applies the submitted changes together; it cannot infer an unsubmitted deletion. The layer must encode the invariant correctly. A query through the old index can otherwise still find a stale association.

Sources: FoundationDB’s 2021 paper, CockroachDB’s transaction layer, and Spanner’s original paper.

Extended reference

The extended chapter preserves the original technical coverage, additional examples, and citations. The explanation above supplies the concepts needed for this page; the reference develops the specialist cases.

Find a section from the original chapter