3

I kinda have trouble to identify the difference between SNAPSHOT and SNAPSHOT READ COMMITTED? READ COMMITTED is a pessimistic approach of concurrency and how is this to be applied into the optimistic concurrency? which in this case on SNAPSHOT isolation level

Thank you, so much appreciate for some enlightments

Alfin E. R.
  • 741
  • 1
  • 7
  • 24
  • READ COMITTED has nothing to do with optimistic vs pessimistic. It is an isolation level. Optimistic vs pessimistic reflects usually to the concurrency timing, not the isolation. Btw your question is two question in one – g.pickardou May 02 '18 at 08:58
  • I have heard that READ COMMITTED SNAPSHOT is a SNAPSHOT isolation-level (which is optimistic concurrency) that applied READ COMMITTED as in pessimistic concurrency, is it true? if it is true, how this scenario makes READ COMMITTED SNAPSHOT isolation level differ with the SNAPSHOT isolation level, what are the difference of each of them? – Alfin E. R. May 02 '18 at 09:06

2 Answers2

3

Both names are disturbingly misleading.

In SQL Server terminology both SNAPSHOT and SNAPSHOT READ COMMITTED are isolation levels and also an implementation way how SQL Server accomplish the isolation of the concurrent data access.

The main difference: In SNAPSHOT repeatable reads and phantom reads are prevented while in SNAPSHOT READ COMMITTED level you can experience non repeatable reads and also you may experience phantom reads.

With other words SNAPSHOT is a higher and stronger isolation than SNAPSHOT READ COMMITTED)

Regarding only the isolation level: SNAPSHOT is equivalent with SERIALIZABLE and SNAPSHOT READ COMMITTED is equivalent READ COMMITTED. However the implementation is different. While SNAPSHOT and SNAPSHOT READ COMMITTED uses record versions, the other hand SERIALIZABLE and READ COMMITTED uses blocking semantics to force block the concurrent process to the changed (or read) resource while the first one finishes its transaction.

I think concurrency and transaction isolation is tough enough to understand, and mixing it (almost always) with optimistic/pessimistic metaphor more hardens the understanding than helps.

g.pickardou
  • 32,346
  • 36
  • 123
  • 268
  • Serializable offers stronger guarantees than Snapshot, they are not equivalent. Snapshot does not prevent write skews. See this: https://github.com/ept/hermitage – Dmitry Aug 26 '18 at 04:20
1

Read committed

This is a default isolation level. Implemented with shared read locks.

Read committed snapshot

The same isolation level as Read committed, but implemented with row versioning / MVCC. The advantage is that writers don't block readers. Some people feel like this should be the default. Note that it is the same isolation level in a sense that it avoids the same anomalies.

Snapshot

A stronger isolation level that allows fewer anomalies.

Dmitry
  • 17,078
  • 2
  • 44
  • 70