The 2 isolation levels we need:
SERIALIZABLE is the correct isolation for write transactions.
SNAPSHOT is best for read only transactions.
There will be anomalies otherwise, whether you deem them serious or not.
SERIALIZABLE is the correct isolation for write transactions.
SNAPSHOT is best for read only transactions.
There will be anomalies otherwise, whether you deem them serious or not.
Furthermore even read only transactions can fail during the transaction, especially longer running ones. Sometimes that is fine, and even required. But many other times reading a snapshot even if it becomes stall is preferable.
Through I wouldn't say snapshot is it's own isolation level. It's more like a specific way you can run read only serializable transactions by telling the DB "pretend in this transaction(only) any changes by other transactions happen after I ended the transaction".
With an MVCC architecture, snapshot can have much better performance than serializable. This is exactly why Oracle originally became popular.
No. See "A read-only transaction anomaly under snapshot isolation" [1].
Do you know what this case is? It could be a truth that won't matter to many/most of us--perhaps good to know, but no reason to stop everything. I don't particularly care as DBs I work with have been Repeatable Read or Read Committed.
It was demonstrated on an Oracle database and it's unclear if it would apply to others.
Read-only transactions with multiple top-level `SELECT`s need to use a (potentially long-term) snapshot.
Read-only "transactions" consisting only of a single statement might be implemented more cheaply.