February 17, 2012 at 1:19 pm
Some background - our database(s) are set to Read Committed Snapshot (RCSI). When I run SQL Profiler I can see that some of our applications upon logging in specifiy either "set transaction isolation level read committed" or "set transaction isolation level serializable". After discussing internally and testing there's some confusion over how (and if) those actually override our database isolation level of Read Committed Snapshot. My biggest concern is in regards to effects on locking.
I've checked in BOL to make heads or tails out of this, and as far as I can tell the following is my understanding of both for when the database is set to Read Committed Snapshot. Can someone help confirm if this understanding is correct?
1. When database is Read Committed Snapshot but transaction isolation level specified is Read Committed:
Row versioning is used (basically overriding Read Committed) unless the READCOMMITTEDLOCK table hint is used. Basically specifying "set transaction isolation level read committed" in this case when the database is RCSI does nothing at all and we'll continue to get the Read Committed Snapshot behavior we'd expect.
2. When database is Read Committed Snapshot but transaction isolation level specified is Serializable:
This is the one that looks as though it actually makes a difference and can cause locking unexpected and undesirable when we're expecting Read Committed Snapshot behavior. Can someone confirm this?
Specifies the following:
* Statements cannot read data that has been modified but not yet committed by other transactions.
* No other transactions can modify data that has been read by the current transaction until the current transaction completes.
* Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.
Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. The range locks are held until the transaction completes. This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
I've sourced my information from here:
February 17, 2012 at 1:23 pm
RCSI is specifically built to override Read Committed Isolation behavior. Read Committed is the default behavior, and RCSI modifies that from pessimistic to optimistic locking, by using row versions. So, yes, specifying Read Committed is the same as leaving it alone in RCSI.
I haven't tested Serializable in RCSI, but my understanding is that its behavior is NOT changed by RCSI, and it still holds exclusive locks on data it touches. No row-versioning, no optimistic locking, just "it's mine and you can't have it", just like usual.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 17, 2012 at 1:37 pm
GSquared, so in your understanding specifying transaction isolation level of Serializable when the database is Read Committed Snapshot and the application is expecting Read Committed Snapshot would almost certainly lead to undesirable results, is that what I'm understanding?
February 20, 2012 at 6:29 am
Brian.cs (2/17/2012)
GSquared, so in your understanding specifying transaction isolation level of Serializable when the database is Read Committed Snapshot and the application is expecting Read Committed Snapshot would almost certainly lead to undesirable results, is that what I'm understanding?
That'll depend on what you consider "undesirable". If what you want is serializable reads, then it's desirable behavior. If what you want is optimistic locking, then it's undesirable. But if you want optimistic locking, you wouldn't normally specify serializable reads anyway.
So the question becomes, what is it that you're using serializable for? What's the purpose of using it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 20, 2012 at 6:48 am
GSquared (2/17/2012)
I haven't tested Serializable in RCSI, but my understanding is that its behavior is NOT changed by RCSI, and it still holds exclusive locks on data it touches. No row-versioning, no optimistic locking, just "it's mine and you can't have it", just like usual.
Correct. Snapshot isolation level is a different isolation level, it uses locks and holds those locks as long as the transaction is open.
The only isolation level that the read committed snapshot isolation level affects is read committed. The others (read uncommitted, repeatable read and serialisable) keep their normal behaviour.
The optimistic concurrency version of serialisable is the snapshot isolation level (SET TRANSACTION ISOLATION LEVEL SNAPSHOT)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2012 at 12:06 pm
Ok, my poking around on this topic was due to some behavior I was seeing in our database, and thankfully we've now also found the root cause of it. Dan Brown's MSDN blog on it sums it up nicely: http://blogs.msdn.com/b/dbrowne/archive/2010/06/03/using-new-transactionscope-considered-harmful.aspx
We found that connections were inadvertantly being set to Serializable isolation when we didn't expect so, because of how TransactionScope works (which most of the sane world views to be broken). But now we also better understand the odd behavior our application was experiencing as connections got set to Serializable and the database was in Read Committed Snapshot. Consider this case closed. Thanks again, this was helpful.
February 20, 2012 at 1:09 pm
Brian.cs (2/20/2012)
Ok, my poking around on this topic was due to some behavior I was seeing in our database, and thankfully we've now also found the root cause of it. Dan Brown's MSDN blog on it sums it up nicely: http://blogs.msdn.com/b/dbrowne/archive/2010/06/03/using-new-transactionscope-considered-harmful.aspxWe found that connections were inadvertantly being set to Serializable isolation when we didn't expect so, because of how TransactionScope works (which most of the sane world views to be broken). But now we also better understand the odd behavior our application was experiencing as connections got set to Serializable and the database was in Read Committed Snapshot. Consider this case closed. Thanks again, this was helpful.
That makes sense.
Weird decision on Microsoft's part to have something default to Serializable. It's not a good isolation level for most multi-user applications.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply