Achieving Repeatable Reads in Snpashot Isolation

• shrikant-279400

  SSCommitted

  Points: 1651

  More actions

  January 15, 2007 at 5:56 am

  #176444

  Hi

  With Snapshot Isolation in SQL 2005,We can achieve Repeatable Read Isolation without much locking. Oracle does provide same feature with Isolation level Serializable.

  consider a below example.  

  - UserA begin Tx1 Select * from table1 ---leave session open

  - UserB Begin Tx2 Update all rows of table1 commit tran Tx2

  - UserA(already open session) select * from table1 commit tran Tx1.

  Can anyone know how sql server 2005 maintains transactions and SCN (System change Number) (I know. SCN is not valid term in SQL server). Does transactions are associated with SCN ? (like Oracle when, transaction gets committed, SCN gets incremented by 1..so at step3, select * from table1 will always select from data blocks with SCN-1)

  Regards
  Shrikant Kulkarni

• Johan Bijnens

  SSC Guru

  Points: 135369

  More actions

  January 16, 2007 at 12:01 am

  #683400

  - Oracle does not have locking, it has versioning ! Different concept.

  - SQL2005 Snapshot isolation level mimics this behaviour.

  maybe these art. clarify a bit :

  http://www.databasejournal.com/features/mssql/article.php/3560451

  http://msdn2.microsoft.com/en-us/library/ms130975.aspx

  http://msdn2.microsoft.com/en-us/library/ms189050.aspx

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me