Table hints for isolation mode without a transaction

Is it possible to specify in a select to use snapshot mode instead of serialize mode?

In sql server you could do something like this
select * from Table with(nolock) which is a hint to use read uncomitted.

I use this often because I don’t want to block writes, and my usecase isn’t life or death or financial, so missing a newly inserted row is no big deal. I know with transactions you can set the isolation level to snapshot, but I tend to avoid transactions as again, my use-case is not life or death or financial, so the priority is speed over all else.

Just curios if this is possible already and I missed it in the docs or something that is on the roadmap? Thanks!

We support configurable isolation levels on the BEGIN TRANSACTION statement, that adjust the semantics for the execution of all the statements in that transaction.

For your use case though, you can also use AS OF SYSTEM TIME <some point e.g. ~10s ago> on your select statement to run it at a recent but not quite now historical timestamp, making it less likely to see or conflict with in-progress transactions. Does that help?

Note that SNAPSHOT and SERIALIZABLE isolation levels are identical for read-only statements/transactions. The two isolation levels only differ when you write to the DB. We do not currently offer any lower isolation levels or options equivalent to with(nolock).

I should also mention that even if you just run e.g. SELECT * FROM foo, cockroach will implicitly wrap that statement in a transaction for you, since all SQL statements access the underlying data via a transaction, explicit or otherwise.

Using AS OF SYSTEM TIME causes that implicit transaction to be run at a historical timestamp, which in our MVCC transaction model, means it should happily read the data as it was at that time and ignore any in-progress transactions that have started since then, which is about the closest we have to the behavior you described.

Alright. Knowing that either way a transaction exists is good info.

Is there any performance overhead to using the time travel data? Is it just as fast as normal reads? Like I said my main goal is performance and not letting writes harm read performance.

Thanks for the info guys! Helpful as always.

There’s no additional overhead: all transactions get assigned a timestamp – either the one you specify with AS OF SYSTEM TIME or, if you don’t provide one, we’ll just pick the current time when we receive the query and use that. By using AS OF SYSTEM TIME to explicitly pick a slightly older timestamp than right now, you’re just avoiding any recent, potentially in-progress transactions that might cause you to wait or restart – so it should be strictly more performant than letting the system pick the current time.