3 ms·

Serializable and snapshot are indistinguishable for read-only transactions, no? That is, a system which guarantees serializability can legally implement any rea

by orlp 3y ago

Serializable and snapshot are indistinguishable for read-only transactions, no? That is, a system which guarantees serializability can legally implement any read-only transaction using snapshot isolation. So you might as well state that there is only a single isolation level we need: serializable.

btilly 3y ago
In the real world, performance also can matter. With an MVCC architecture, snapshot can have much better performance than serializable. This is exactly why Oracle originally became popular.
dathinab 3y ago
Depending on the system you use snapshot isolation sometimes allows you to get an snapshot id which allows you to use the same snapshot in other (read only) transactions. 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".
pulisse 3y ago
> Serializable and snapshot are indistinguishable for read-only transactions, no? No. See "A read-only transaction anomaly under snapshot isolation" [1]. [1] https://dl.acm.org/doi/10.1145/1031570.1031573 https://dl.acm.org/doi/10.1145/1031570.1031573
- karmakaze 3y ago
  > we exhibit an example contradicting these assumptions: it is possible for an SI [snapshot isolation] history to be non-serializable while the sub-history containing all update transactions is serializable. 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.
- deleted 3y ago
  [deleted]
- orlp 3y ago
  I see, that is a tricky case. When serializability would have required a transaction order that is different from commit order for two transactions, inserting a third snapshot-isolated read in between can violate the (logical) reordering of the write transactions needed to justify serializability.

by orlp 3y ago

btilly 3y ago

In the real world, performance also can matter. With an MVCC architecture, snapshot can have much better performance than serializable. This is exactly why Oracle originally became popular.

dathinab 3y ago

Depending on the system you use snapshot isolation sometimes allows you to get an snapshot id which allows you to use the same snapshot in other (read only) transactions. 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".

pulisse 3y ago

> Serializable and snapshot are indistinguishable for read-only transactions, no? No. See "A read-only transaction anomaly under snapshot isolation" [1]. [1] https://dl.acm.org/doi/10.1145/1031570.1031573 https://dl.acm.org/doi/10.1145/1031570.1031573

karmakaze 3y ago
> we exhibit an example contradicting these assumptions: it is possible for an SI [snapshot isolation] history to be non-serializable while the sub-history containing all update transactions is serializable. 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.
deleted 3y ago
[deleted]
orlp 3y ago
I see, that is a tricky case. When serializability would have required a transaction order that is different from commit order for two transactions, inserting a third snapshot-isolated read in between can violate the (logical) reordering of the write transactions needed to justify serializability.