CockroachDB 23.2.4 enforces read-only historical transactions

Choosing a historical timestamp should also constrain what a transaction may do. CockroachDB 23.2.4, released on April 11, fixes a path that set an AS OF SYSTEM TIME timestamp without marking an implicit transaction read-only. The omission allowed mutation and locking statements in a context where they should have been rejected.

The backport in pull request 120158 identifies the problem in handleAOST. That handler recorded the historical timestamp but did not set the corresponding access mode. The repair makes those two pieces of transaction state agree. It also prevents transaction_read_only from being set to false during a historical transaction.

I value CockroachDB's explicit consistency model, so a historical read needs an equally explicit access contract. Reading old state and reserving rows for a new write are different operations. A supported historical query should not acquire mutation capabilities because one transaction-construction path omitted a flag.

An implicit transaction still needs complete state

The release notes specify implicit single-statement transactions as the affected setup path. That is a useful distinction because an application can issue one SQL statement without spelling out a BEGIN block. The database still establishes transaction state for the operation. Correct enforcement must apply to that state just as it applies to an explicit transaction.

The bug demonstrates why recording a timestamp alone was insufficient. Later checks used read-only state to reject mutation and locking operations. If timestamp selection and access-mode selection diverged, the transaction could reach those checks with an invalid combination. The repair supplies the missing state at setup, where the historical timestamp becomes part of the transaction.

This is more precise than describing the change as a new restriction on all historical SQL. The documented contract already required historical transactions to be read-only. The patch corrects enforcement in a particular path. Applications depending on the accepted invalid combination can see a new error even though the intended transaction model has not expanded or changed direction.

The pull request calls the old combination of historical timestamps and mutation statements undefined. That limits what can be concluded about earlier successful execution. Acceptance by an affected version did not establish a supported meaning for writing through an old snapshot. The new behavior rejects the combination before it can be treated as a legitimate application operation.

Locking reads are included in the restriction

The announcement names UPDATE and DELETE as mutation examples, but also includes SELECT FOR UPDATE. That last case matters because a statement beginning with SELECT can still request locking behavior. Classifying statements only by whether they return rows would miss the access requirement involved in reserving those rows.

The SET TRANSACTION documentation for 23.2 defines access mode separately from isolation level and priority. Its historical-time option uses database contents as of a past time and is allowed only with read-only access. Requests containing writes or explicit read-write mode produce an error. Those are properties of the transaction, not merely of the presentation of its result.

This distinction also separates historical reads from the Read Committed preview introduced in 23.2. Isolation level determines which concurrent committed states a transaction may observe. The historical timestamp selects a past state, while read-only mode constrains permitted operations. These settings describe different dimensions of execution and cannot substitute for one another.

I find the locking case particularly informative. It shows why the database needs to classify an operation by its effects, including locks, instead of treating all SELECT syntax as passive observation. The repair makes that classification apply consistently to the implicit historical transaction path.

A session variable cannot remove the invariant

The second part of the fix rejects setting transaction_read_only to false during an AS OF SYSTEM TIME transaction. Without that restriction, transaction setup could initially establish the correct mode and a later configuration action could undo it. The historical timestamp would remain while its required access constraint disappeared.

Both parts are therefore necessary to preserve the same rule through the transaction's lifetime. One establishes read-only state when the implicit historical transaction is created. The other prevents an incompatible later mode change. Their common requirement is that a selected historical state remains associated with permitted historical operations.

April's release also fixes a memory-accounting leak present in 23.2.3 for long-running sessions issuing very many transactions. That is a separate defect, with root: memory budget exceeded as a possible symptom for other queries. It should not be inferred from a rejection of historical mutation SQL, which is now the intended behavior of the transaction checks.

The release makes historical access more predictable by rejecting unsupported combinations at their relevant boundaries. An implicit transaction receives the read-only state its timestamp requires, and a later session-variable assignment cannot make that historical transaction writable. Ordinary historical reads retain their documented purpose of observing database contents as of the selected past time.