Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transaction Isolation Levels Expand / Collapse
Author
Message
Posted Wednesday, February 12, 2014 10:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 6,594, Visits: 8,879
Comments posted to this topic are about the item Transaction Isolation Levels

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1541022
Posted Thursday, February 13, 2014 6:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 1, 2014 9:53 AM
Points: 2, Visits: 20
As you point out Read Committed Snapshot provides a transactionally consistent set of data at the time the Statement began. Therefore, if T1 reads a row; T2 later commits changes to that row; and T1 reads that row again it will get a different result on the second read. So, Repeatable Read is not guaranteed by Read Committed Snapshot.
Post #1541165
Posted Thursday, February 13, 2014 9:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 13, 2014 9:06 AM
Points: 1, Visits: 13
Great article, Well done! thank you!
Post #1541271
Posted Thursday, February 13, 2014 2:18 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:37 PM
Points: 536, Visits: 757
Great explanation and examples!


Post #1541412
Posted Thursday, February 13, 2014 11:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 21,644, Visits: 15,317
Nice article Wayne.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1541485
Posted Friday, February 14, 2014 10:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 6,594, Visits: 8,879
Thank you Anuj, Misha and Jason.

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1541666
Posted Friday, February 14, 2014 10:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 6,594, Visits: 8,879
jemerson 16199 (2/13/2014)
As you point out Read Committed Snapshot provides a transactionally consistent set of data at the time the Statement began. Therefore, if T1 reads a row; T2 later commits changes to that row; and T1 reads that row again it will get a different result on the second read. So, Repeatable Read is not guaranteed by Read Committed Snapshot.


Thanks for reading the article and responding!

If we take a closer look at the snapshot example in the article, "T1" is reading the same set of rows twice, and during that time "T2" is adding a row. Since "T1" is doing both reads in a transaction, both of the reads are returning the same data, without the modifications that "T2" is making.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1541667
Posted Friday, February 14, 2014 10:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 1, 2014 9:53 AM
Points: 2, Visits: 20
That's true for snapshot isolation mode. However,the chart shows that Read Committed Snapshot also ensures Repeatable Read, no phantom reads, and no missing/double reads. Since for Read Committed Snapshot, consistency is guaranteed only for the Statement, I believe you can still have repeatable read (and the other) problems. When the same statement is run a second time, it will get a (possibly different) consistent set of rows as they existed when the second statement started.

Post #1541677
Posted Saturday, March 29, 2014 7:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 8:31 AM
Points: 57, Visits: 278
Since for Read Committed Snapshot, consistency is guaranteed only for the Statement, I believe you can still have repeatable read (and the other) problems.


Of course, and anyone need no more but 5 min. only to check this fact on the practice. So, the second table from the start of the article is TOTALLY incorrect.
Post #1556213
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse