Isolation Levels

Question

Isolation Levels

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

jasona.work SSC Guru Points: 50117 More actions · Answer 1

paul.knibbs (11/21/2012)
Can someone explain in what way SNAPSHOT doesn't meet the stated requirements? That's the answer I selected and I always like to know exactly how I've been a helpless newb... 😛

I was going to ask the same question, but then read a little better the MSDN page linked.

It's the 2nd requirement that rules out Snapshot:

Other transactions should not be able to modify the data that has been read by your transaction

Snapshot allows other transaction to modify the data that was read...

Jason.

paul.knibbs SSCoach Points: 15320 More actions · Answer 2

D'oh! I should have spotted that...if the intent was just to prevent the data that's actually being returned from changing, as I was thinking, that would already be covered by the first requirement. *slaps self round head*

Andre Ranieri SSCrazy Points: 2819 More actions · Answer 3

Great back to the basics question, it's always good to keep the foundations of SQL sharp in my mind.

Thanks,

Andre Ranieri

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Good question - thanks

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

call.copse (11/21/2012)
...I am surprised at only 34% right so far.

I'm not surprised at all. Most of the isolation levels provided by SQL Server cause way to much blocking to be practical in a production OLTP system. The ones that get used the most are Read Committed which is most people's choice for balance of locking vs consistancy, Read Uncommitted (or NOLOCKS hints) which really can do more harm than good and cause unexpected or wrong results but allows greater concurrency, and then modern database systems use row versioning such as SQL Server's Snapshot isolation for an even better mix of low locking and high consistancy. Does anyone actually use Repeatable Read isolation level?

TomThomson SSC Guru Points: 104773 More actions · Answer 6

jasona.work (11/21/2012)
paul.knibbs (11/21/2012)
Can someone explain in what way SNAPSHOT doesn't meet the stated requirements? That's the answer I selected and I always like to know exactly how I've been a helpless newb... 😛
I was going to ask the same question, but then read a little better the MSDN page linked.
It's the 2nd requirement that rules out Snapshot:
Other transactions should not be able to modify the data that has been read by your transaction
Snapshot allows other transaction to modify the data that was read...
Jason.

I think that's an instance of an error in BoL. MS has two implementations of READ COMMITTED, and which is used is determined by the database option READ_COMMITTED_SNAPSHOT. What READ COMMITTED is is determined by the ISO Standard, and according to MS both implementations conform to that standard. The requirement is NOT to prevent data modification of data an SQL statement in a transaction with READ COMMITTED isolation has read before the statement completes, but to ensure that it is not affected by any such updates. In the implementation used when READ_COMMITTED_SNAPSHOT is on, this achieved by row versioning, not by locking against modification, and the reading transaction takes only schema stability locks for the data it reads, not shared read locks. This is all clearly documented at http://msdn.microsoft.com/en-us/library/ms189122%28v=SQL.105%29.aspx. I think this indicates the right approach to interpreting the criteria assuming that they are criteria for isolation levels.

The SNAPSHOT isolation level on the other hand is not a new implementation of an existing isolation level, but a new isolation level. But some of the same arguments apply: when talking about isolation levels (as opposed to particular implementations) we need to interpret criteria into the terms used by ISO to define the isolation levels, since those criteria is what MS is committed to.

So the second criterion needs to be interpreted as saying that unrepeatable reads are not allowed, not being about an implementation detail like locking and blocking (ie if a transaction reads a particular attribute of a particular row more than once, it gets the same value every time, unless it itself has changed the value). That is satisfied both by the repeatable read isolation level (that's why it's called that) and by the snapshot isolation level.

Finally, the third criterion has to be interpreted as saying that if a transaction does the same search twice, it may get different results each time (even if it itself has neither added nor deleted some rows that match the search criteria); or in other words phantom reads are permitted. The repeatable read isolation level allows this. The snapshot isolation level doesn't. That's why snapshot is disqualified.

At least I hope that's where the question's author was coming from, and that he didn't believe the stuff about blocking behaviour in BoL was intended to be treated as an accurate description of any isolation level. As I said in a previous post, I used to spend a lot of time trying to make people understand this stuff, and it was really hard work, and I don't want to feel I need to start doing it again.

edit: just for fun, people might like to take a look at this paper about isolation levels,

Tom