December 23, 2009 at 6:17 pm
Microsoft has strongly recommended that we use SNAPSHOT isolation with Change Tracking because of the improved row versioning. Can anybody tell me what benefits this has over Read Committed?
Cheers,
Tim
December 28, 2009 at 2:23 pm
READ COMMITTED works differently in SQL Server than it does in other databases such as Oracle. With SQL Server, READ COMMITTED works almost similar to SERIALIZABLE. Although transactions that read data that is currently being updated is suppossed to return the data in its committed state, what really happens in SQL Server is that the data is blocked.
Snapshot isolation will create a duplicate row of the data being updated in TEMPDB that can be read by other transactions during the update process. This will reduce the amount of blocking that takes place during heavy OLTP loads. I've used it in the past with great success.
MSDN has a good article on the subject:
http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx
January 3, 2010 at 10:29 pm
Cheers for the reply Mel. I was wondering what the benefits are specifically in relation to Change Tracking. As far as I can work out, it could be something to do with the reads not acquiring locks and therefore not blocking modifications, and consistency between multiple select statements but I was hoping that somebody clever than me could confirm this and maybe add to it too. Or more likely, give me the real reasons! 🙂
Cheers,
Tim
September 19, 2012 at 5:37 pm
I also didn't understand what would happen if I didn't use snapshot isolation with my Change Tracking process, but this helped.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply