As highlighted by #6229 (comment) , the following situation may be a problem with CockroachDB:

• Interactive transaction starts, issues a SELECT for one or more rows from the database
• The value of that row is accessed and considered, somehow
• Concurrently, that row is modified by another operation (concurrent transaction, CTE, UPDATE, DELETE, etc).
• The original transaction commits without updating the original row, but assuming it has not been changed.
• No error is returned, the transaction completes "successfully", even though the assumption of immutability of that row has been violated.

In this scenario, a row was read by a transaction, and modified before the transaction committed. According to Cockroachdb's documentation, this is legal:

“CockroachDB doesn’t allow stale reads”... “No stale reads” means that, once a write transaction committed, every read transaction starting afterwards will see it.

Note that in this situation, the read transaction started before the subsequent write transaction, so the read was not considered stale. That being said, the value read by the transaction did get modified before it was committed.

This issue tracks taking a look at our usage of interactive transactions, for the following pattern:

1. A SELECT statement is issued without a corresponding FOR UPDATE lock
2. The result of that SELECT statement influences the rest of the transaction, in some way. Examples could be: Influencing a conditional in Rust code, using the result to populate a subsequent UPDATE or INSERT, etc.
3. The original rows being SELECT-ed are not themselves modified by the transaction.
4. If the transaction makes the assumption that the SELECT-ed rows must not be modified before the transaction calls COMMIT, then this is a potential problem.

An example of this issue was that highlighted within #6229, where we had roughly the following problem:

• In a Transaction: SELECT the latest blueprint target, confirms it is equal to value X. Perform a database modification, assuming that the SELECT-ed blueprint target has not been modified.
• Concurrently, another operation may occur, which modified the "latest target blueprint".

In this example, the following may occur:

• Txn: SELECT the blueprint target, sees value "X"
• Another operation modified the blueprint target to "X + 1"
• Txn: Modify the DB, issue COMMIT. No error is observed, it is not known that the target changed mid-operation.