locked
snapshot isolation level RRS feed

  • Question

  • Hi,

    I have a stored proc reading from a table that gets updated (with update statements) by the second. I want all the queries in my stored proc to be aligned with each other (I don't want for example in my third query to read new data from the table that wasn't read by the first query...)

    Now, one option I have is to copy the entire table at the beginning of the stored proc into a temp table - but this is expensive performance wise and no indices used (unless I recreate them on the temp table which is also some addition time). Anyway, isn't using snapshot isolation level my best (and only?) option here? Are there any safety issues I should look at when using this isolation level?

    Also, my database (server) is configured to read committed snapshot, which i know is something different. Any "read committed snapshot" does not give me what i'm looking for as it will always read the latest committed... but I just realized that if "read committed snapshot" is there for non-blocked queries - then if i use just snapshot isolation level then I don't really need database configured on "read committed snapshot" because snapshot is also not blocking (and it's also a more consistent query) - am i right here in the way of thinking? btw, I've seen applications use read uncommitted just for the query to be non-blocking (something i don't like doing) - is it safe to right to say that it's much better using snapshot then read uncommitted?

    Thanks,

    Dror

    Sunday, March 25, 2012 4:59 PM

Answers

  • I think snapshot isolation is a good choice for the scenario u described instead of copying the data into a temporary table.

    As per your description of the problem,read committed snapshot is not the option for you,as this is snapshot isolation at statement level,That means it is guaranteed that you get the same data during the execution of a single statement not across statements.

    As per your requirement you want the data to be same for a set of sql statemtns with in your procedure,so go with snapshot isolation level.

    And read uncommiited is clearly not an option in this case.so big NO.

    Also monitor the tempdb usage as the version store relies on the tempdb to store the multiple versions of the data.

    these are some helpful links in monitoring tempdb usage.

    http://msdn.microsoft.com/en-us/library/ms175492.aspx

    http://strictlysql.blogspot.com/2010/03/whats-causing-my-tempdb-to-grow-sql_17.html


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer



    • Edited by Vinay Valeti Sunday, March 25, 2012 5:20 PM
    • Marked as answer by Kalman Toth Saturday, March 31, 2012 11:59 PM
    Sunday, March 25, 2012 5:16 PM
  • Right, snapshot isolation is your choice. And it is your only choice. Read committed is not. Serliazable is not. And copying data to a temp table is not - the table could be updated while you are copying it. Snapshot isolation is the only way to get a consistent view of a point in time of the database.

    then if i use just snapshot isolation level then I don't really need > database configured on "read committed snapshot" because snapshot is also > not blocking

    You probably still need both. To use snapshot isolation, you need to explicitly set the isolation level. This is not needed for RCSI. Also, pure SI takes a higher toll on the version store, since the snapshots must be held longer.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Saturday, March 31, 2012 11:59 PM
    Sunday, March 25, 2012 7:31 PM

All replies

  • I think snapshot isolation is a good choice for the scenario u described instead of copying the data into a temporary table.

    As per your description of the problem,read committed snapshot is not the option for you,as this is snapshot isolation at statement level,That means it is guaranteed that you get the same data during the execution of a single statement not across statements.

    As per your requirement you want the data to be same for a set of sql statemtns with in your procedure,so go with snapshot isolation level.

    And read uncommiited is clearly not an option in this case.so big NO.

    Also monitor the tempdb usage as the version store relies on the tempdb to store the multiple versions of the data.

    these are some helpful links in monitoring tempdb usage.

    http://msdn.microsoft.com/en-us/library/ms175492.aspx

    http://strictlysql.blogspot.com/2010/03/whats-causing-my-tempdb-to-grow-sql_17.html


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer



    • Edited by Vinay Valeti Sunday, March 25, 2012 5:20 PM
    • Marked as answer by Kalman Toth Saturday, March 31, 2012 11:59 PM
    Sunday, March 25, 2012 5:16 PM
  • Right, snapshot isolation is your choice. And it is your only choice. Read committed is not. Serliazable is not. And copying data to a temp table is not - the table could be updated while you are copying it. Snapshot isolation is the only way to get a consistent view of a point in time of the database.

    then if i use just snapshot isolation level then I don't really need > database configured on "read committed snapshot" because snapshot is also > not blocking

    You probably still need both. To use snapshot isolation, you need to explicitly set the isolation level. This is not needed for RCSI. Also, pure SI takes a higher toll on the version store, since the snapshots must be held longer.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Saturday, March 31, 2012 11:59 PM
    Sunday, March 25, 2012 7:31 PM