CockroachDB isolation and follower reads

A current price, a price from a few seconds ago and a price read twice inside one transaction answer different questions. CockroachDB can provide each. Choosing between them starts with what the application will do with the answer.

The shop’s catalogue changes the blue mug, P7, from 20.00 to 22.00. A catalogue page might be allowed to show a price up to ten seconds old. A purchase must check its current conditions together with its writes. A report might need several queries to agree about one earlier state. All three can return correct committed data, yet the same read choice will not suit all three.

Two controls decide which history the reader sees: the transaction’s isolation level and, for a deliberate historical read, its AS OF SYSTEM TIME timestamp. A third question is where that read can run. A nearby replica saves a remote trip only when it has the evidence needed to answer at the chosen time.

This article follows CockroachDB v26.2. The price replay is independent of the purchase. Order 103 remains pending, with no payment, and its agreed line prices total 29.00. Stock after the purchase is P3 10, P7 0 and P9 4. Database timestamps below describe committed history; they do not give the price a business effective date.

Serializable and Read Committed

CockroachDB’s default isolation level is Serializable. Successful transactions must fit an order in which each ran as a whole. A transaction can read several rows, make a decision and write its result without another successful Serializable transaction slipping into the middle of that decision in a way that breaks that ordering. Conflicts can require the application to retry the transaction.

Read Committed gives each statement a fresh committed snapshot. The statements of one transaction can therefore observe different states. It reduces the need to retry whole transactions, at the cost of allowing anomalies across statements. CockroachDB’s implementation protects consistency within one statement more strongly than PostgreSQL’s Read Committed; the shared connection protocol does not make the engines’ isolation identical.

Suppose a transaction reads P7 at 20.00. A separate transaction then commits 22.00 before the first transaction reads P7 again. The isolation contract determines whether those two reads may return different prices.

Under Serializable, the two reads return 20.00. Under Read Committed, the first returns 20.00 and the second 22.00. Both Read Committed answers were correct when their statements took their snapshots. The transaction did not keep one view between them.

A Serializable read timestamp can move internally if CockroachDB can validate the earlier reads at the later time. The promise is a coherent transaction history, rather than a timestamp the application should assume is immovable. Once this transaction has observed P7 at 20.00, it cannot simply return 22.00 on its second read and keep both observations as one successful Serializable execution.

Neither level makes a statement keep absorbing later commits while it runs. A long current read can still be showing an earlier snapshot when it finishes. Read Committed’s advancing snapshots also do not make it a historical follower-read mode.

BEGIN ISOLATION LEVEL READ COMMITTED;
SHOW transaction_isolation;
SELECT price FROM products WHERE product_id = 'P7';
-- Other session commits the independent price update here.
SELECT price FROM products WHERE product_id = 'P7';
COMMIT;

Check SHOW transaction_isolation inside the transaction. SHOW default_transaction_isolation describes the session’s default, which may differ from this transaction’s setting. In v26.2, disabling the cluster setting sql.txn.read_committed_isolation.enabled upgrades requests for Read Committed to Serializable, another reason to inspect the effective level.

Decisions across rows

Seeing committed data does not by itself protect a rule that spans statements. In a separate experiment, order 103 has a line value of 29.00 and no payments. The application accepts a proposed payment of 20.00 only if the recorded total plus 20.00 is no more than 29.00.

Two attempts, 601 and 602, each read the total before either inserts. Both see 0.00, so each accepts its proposed 20.00 payment. Their payment IDs differ, so a unique key on payment ID does not make the attempts conflict. Under Read Committed, both can insert and commit, leaving 40.00 against the 29.00 limit.

There is no serial order that produces both successful checks against zero: whichever payment ran second would see 20.00 already recorded and decline another 20.00. Serializable rejects that combined execution. After a retry starts from a fresh view, the application must repeat the check and act on the new total.

Choose Read Committed only when the complete operation tolerates its weaker guarantee or supplies another complete protection protocol. Locking an authority row can help, but every competing operation that changes the rule’s inputs must participate. Transactions and isolation develops that protocol. Error handling still matters: Read Committed transactions can abort on deadlocks or constraint failures, and some streamed-result failures cannot be retried transparently. CockroachDB transactions and retries covers the retry and uncertain-outcome procedures.

Reading as of one timestamp

Sometimes the reader wants an earlier state on purpose. AS OF SYSTEM TIME selects the database’s committed contents at a historical timestamp. P7 can return 20.00 after the update to 22.00 has committed because the requested time lies before that commit.

That is different from asking which price the shop intended to apply on a particular business date. A price entered today but effective tomorrow, or corrected retrospectively, needs a model of effective and recorded time. History, correction and deletion explains that model. The database’s version history answers what was committed at its timestamp.

For several related queries, choose the timestamp once. This transaction fixes its view before either SELECT, then reads the catalogue price and the order’s agreed lines from that same history:

BEGIN;
SET TRANSACTION AS OF SYSTEM TIME follower_read_timestamp();
SELECT price FROM products WHERE product_id = 'P7';
SELECT line_no, product_id, quantity, unit_price
FROM order_items WHERE order_id = 103 ORDER BY line_no;
COMMIT;

The historical transaction is read-only. It cannot lock old stock and then allocate it, and an UPDATE is rejected. The time must also be one at which the tables and required history exist.

The convenience function follower_read_timestamp() chooses a recent historical time intended to make follower service likely. It selects an exact time for this transaction; it is not a rolling allowance that advances between these two SELECTs. Two separate statements that each recompute a relative time such as '-10s' choose different instants as the clock advances. A commit between them can appear in the second result only.

The same-time requirement applies when data spans several ranges. A coherent old catalogue and a coherent old order list can still disagree if they are old at different times. Time, snapshots and distributed reads builds that reasoning. AS OF SYSTEM TIME supplies the native timestamp choice; it does not prove which replica answered.

Closed timestamps and follower reads

In Inside CockroachDB, a range is a replicated interval of keys, with a leaseholder coordinating ordinary access. Each replica can hold multiple committed versions of P7. Having 20.00 in storage is still insufficient evidence that 20.00 is the right answer at the requested time.

A follower could be missing a command that changes P7 before that time. It needs to know both that no more writes can enter the relevant past and that it has applied the history already assigned there. CockroachDB’s closed timestamp supplies the first promise, tied to a replication position that supplies the second test. A follower can receive the closed-time promise while its local application of those commands is still behind.

The leaseholder closes a range through a timestamp: no new writes will be introduced at or below it. The follower can serve that time after it has applied the Raft commands through the associated position. Closing a time does not mean that every follower has caught up to it.

For a query that needs several ranges, each must be able to answer at the same requested timestamp. One range’s progress cannot qualify another. A nearby SQL gateway is only where the SQL entered the cluster; its key-value requests can still travel elsewhere.

Follower reads can reduce remote trips and leaseholder work. They do not promise a response time: replica placement, catch-up, contention, query work and failures still matter. A very recent exact timestamp may require leaseholder access. Even a timestamp chosen by follower_read_timestamp() gives a probability of nearby service, rather than a guarantee, and an unresolved write intent can still make an exact historical read wait.

Current reads from nearby replicas

The name follower read describes the serving path, so it does not always mean deliberately stale data. CockroachDB’s GLOBAL table locality supports strong reads from nearby replicas. The placement and write protocol make current reads cheap across regions by making writes more expensive.

A rarely updated catalogue shared across regions may be a candidate for that trade. A table with frequent writes has a different cost. CockroachDB table locality and region survival develops the native locality design and failure goals. Adding an AS OF clause and choosing GLOBAL locality are separate application and schema decisions.

Exact and bounded staleness

An exact staleness read fixes one historical timestamp, whether written explicitly or produced by follower_read_timestamp(). Use it when the identity of the snapshot matters, especially for several statements that must agree. Once chosen, that instant stays fixed even while the report runs.

A bounded staleness read gives CockroachDB room to choose a timestamp within a requirement. If the catalogue permits data at most ten seconds old, it can ask for that bound instead of insisting on one particular instant:

SELECT price
FROM products AS OF SYSTEM TIME with_max_staleness('10s')
WHERE product_id = 'P7';

The bound limits how old the chosen snapshot may be when the read chooses it. It does not promise that the snapshot will be exactly ten seconds old, nor that the response will arrive within ten seconds. Time spent running the query and displaying or caching the result can add to the age the user eventually sees.

A minimum timestamp expresses the other side of the same choice. With with_min_timestamp(T), the chosen snapshot must be T or later. It need not equal T. The application must obtain T from a trustworthy relationship to the history it requires; an arbitrary client clock reading is not proof that a particular database commit is included.

Both bounded functions accept an optional nearest_only boolean. Passing true asks for an error if a nearby replica cannot satisfy the bound. Without that restriction CockroachDB can seek service elsewhere. The choice is whether to accept another serving location or an unavailable result, rather than silently relaxing the freshness requirement.

In v26.2 the documented bounded-staleness path is an implicit single-statement transaction reading one row, through a primary index or another index that covers the query without an index join. The P7 primary-key lookup fits. A multi-query report does not, and adding LIMIT 1 to a scan does not establish an eligible point lookup. Use one exact historical transaction for a shared report snapshot.

Freshness after a write

Once Mina’s purchase has been confirmed, her next page needs the resulting order. A fresh current transaction started after that confirmation can read the committed result. A historical snapshot from before the purchase cannot, however safely a nearby follower serves it.

“At most ten seconds old” does not mean “includes my write”. If the purchase committed one second ago, a permitted five-second-old snapshot precedes it. A minimum-history bound can express a dependency when the application has a valid timestamp covering that write, but choosing and propagating that bound is part of the protocol. Starting a fresh current transaction is the direct choice when the next read requires the newly confirmed state.

If the purchase’s outcome is uncertain, an absent row in a historical result proves only that the row was absent at that earlier time. It cannot establish that the purchase failed. Recover the protected request identity, such as purchase-103, through CockroachDB transactions and retries. Do not turn historical absence into permission to create another purchase.

Retained history

A safe timestamp must also be available in storage. CockroachDB garbage-collects old versions, controlled by gc.ttlseconds in replication zone configuration. A request can be old enough for follower service and too old for the history that remains.

SHOW ZONE CONFIGURATION FROM TABLE products;

Read the returned target as well as gc.ttlseconds. A table can inherit its settings; the answer may name the parent zone rather than the table itself. Inspect the relevant tables, indexes and any more specific configurations used by the read, rather than assuming one cluster-wide value.

A fixed report snapshot gets older for as long as the report runs. Budget its starting age, execution time, pauses and any need to resume against the retained-history window. Do not assume an open historical transaction grants unlimited retention. If the required history is unavailable, fail or restart the whole report at a new agreed timestamp; silently switching just the next query would mix histories.

Keeping versions longer costs storage. Increasing retention cannot restore versions already removed. It also does not create permanent business audit history or a recoverable copy after data loss. Those needs belong to an explicit history model and CockroachDB backup and recovery.

Checking the read that actually ran

Successful SQL establishes that CockroachDB returned an answer, but does not establish that a follower served it. Run EXPLAIN ANALYZE on the read whose path matters. It executes the statement, so use a representative, bounded query.

EXPLAIN ANALYZE
SELECT price
FROM products AS OF SYSTEM TIME with_max_staleness('10s')
WHERE product_id = 'P7';

The historical field identifies a historical plan and marks bounded staleness. For a bounded read, its printed time can be the earliest allowed timestamp; the snapshot that supplies the row can be newer. The plan’s sql nodes and kv nodes distinguish SQL processing from the nodes supplying the data; regions adds locality when available. used follower read is the query-level evidence for follower service. distribution: local alone is not that evidence.

The SQL examples and two-session schedules here were checked on owned, disposable single-node CockroachDB v26.2.8 fixtures. The executed lookup reported a bounded-staleness plan and SQL/KV node n1, with no used follower read annotation. It verifies historical SQL and isolation behaviour. It supplies no observation of a remote leaseholder, nearby follower, regional latency or failure availability.

On the real topology, check the effective isolation, the requested snapshot or freshness bound, and the serving path separately. Then compare them with the decision the answer will support. A safe follower can answer the past correctly; the application must still decide whether that past is recent enough for its next action.