Transaction Isolation Levels

Question

Transaction Isolation Levels

WayneS

SSC Guru

Points: 95460
More actions
February 12, 2014 at 10:40 pm

#289526

Comments posted to this topic are about the item Transaction Isolation Levels
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Viewing 9 posts - 1 through 8 (of 8 total)

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

jemerson 16199 SSC Rookie Points: 46 More actions · Answer 1

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.

Anuj Kumar Singh SSC Rookie Points: 32 More actions · Answer 2

Anuj Kumar Singh

SSC Rookie

Points: 32

February 13, 2014 at 9:07 am

#1688804

Great article, Well done! thank you!

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 3

Misha_SQL

SSCertifiable

Points: 5407

February 13, 2014 at 2:18 pm

#1688925

Great explanation and examples!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Nice article Wayne.

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

WayneS SSC Guru Points: 95460 More actions · Answer 5

Thank you Anuj, Misha and Jason.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 6

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
Author - SQL Server T-SQL Recipes

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

jemerson 16199 SSC Rookie Points: 46 More actions · Answer 7

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.

Shcherbunov Neil SSCommitted Points: 1525 More actions · Answer 8

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.