locked
SERIALIZABLE isolation level question RRS feed

  • Question

  • Hi All,

    in our database we have set the SQL Server default isolation level as Read Commited, but when we checked the Connection settings from the application the Set option is Serializable isolation level.

    so we have couple of statments which has nolock hint to it e.g.

    select id,name from employee with (nolock)

    my question is with serializable isolation level if an insert or update is happening on the employee table then the does the select query will be blocked even though it has the nolock hint.

    please clarify.
    Tuesday, February 12, 2013 3:43 PM

Answers

  • Lock hint on the table takes precedence over the session-level setting for the isolation level.

    NOLOCK is a hint you should use only if you understand the full implications of it - and this is very difficult even for an experienced developer.(*)

    At the same time, SERIALIZABLE is an isolation which is not so good for concurrency and is prone to create deadlocks. In many cases some form of snapshot is good if you want to avoid blocking.

    (*) More precisely, what is difficult to understand is the effect of concurrent operations, because there are so many possibilities. While blocking is always visible, the errors that occurs when you don't have everything in order are less visible and when they occur they are difficult to understand because they are not repeatable. I've just completed troubleshooting a situation in our system where things went wrong when several things happened at the same time. I was able to solve it solely by looking at the data and the code, but it took quite a while until the penny dropped. This case did not involve NOLOCK, but only the default isolation level READ COMMITTED. (What happened was that one process could delete data in a work table than an other process used as input for a number of updates.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Gert-Jan Strik Wednesday, February 13, 2013 4:22 PM
    • Marked as answer by Iric Wen Wednesday, February 20, 2013 8:52 AM
    Tuesday, February 12, 2013 4:06 PM