locked
Isolation Level Snapshot RRS feed

  • Question

  • Hi All

    We use isolation level read committed transaction for our DB. In the same time there are lot of staging Jobs which query data every 5 to 10 secs which locks DB transactions. Could Snapshot isolation help in this scenario. Snapshot has overhead of row version. 

    I did not have much experience with Snapshot isolation. Is this used widely or it has huge drawback?

    Thanks

    CP

    Tuesday, February 9, 2016 3:21 PM

Answers

  • Read_committed_snapshot is the database option that affects behavior of the readers in READ COMMITTED isolation level. When you enable this option on database level (requires exclusive db access to change it), all your readers in READ COMMITTED isolation level will start to use row-versioning reading old(committed) versions from the version store rather than being blocked by (S)/(X) locks incompatibility. This option does not require any code changes (assuming you readers are using READ COMMITTED). It would not change behavior of writers (writers still block each other) nor behavior of readers in any other transaction isolation level.

     

    Snapshot isolation level is full blown transaction isolation level. It needs to be explicitly specified in the code. Enabling snapshot isolation level on DB level does not change behavior of queries in any other isolation levels. In that option you are eliminating all blocking even between writers (assuming they do not update the same rows) although it could lead to 3960 errors (data has been modified by other sessions).

     

    Speaking of consistency, RCSI gives you statement level consistency – readers ignores the data changes done after statement has been started. Snapshot – transaction level consistency – session deals with “snapshot” of the data at the moment transaction started. Again, it could lead to error 3960 in the system with volatile data.

     

    Downsides:

    1.       Excessive tempdb usage due to version store activity. Think about session that deletes 1M rows. All those rows must be copied to version store regardless of session transaction isolation level and/or if there are other sessions that running in optimistic isolation levels at the moment when deletion started.
    2.       Extra fragmentation – SQL Server adds 14-byte version tag (version store pointer) to the rows in the data files when they are modified. This tag stayed until index is rebuild
    3.       Development challenges – again, error 3960 with snapshot isolation level. Another example in both isolation levels – trigger or code based referential integrity. You can always solve it by adding with (READCOMMITTED) hint if needed.

     

    While switching to RCSI could be good emergency technique to remove blocking between readers and writers (if you can live with overhead AND readers are using read committed), I would suggest to find root cause of the blocking. Confirm that you have locking issues – check if there are shared lock waits in wait stats, that there is no lock escalations that block readers, check that queries are optimized, etc.  


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Eric__Zhang Thursday, February 11, 2016 8:25 AM
    • Marked as answer by Eric__Zhang Thursday, February 18, 2016 2:10 AM
    Tuesday, February 9, 2016 3:23 PM
    Answerer