Why are my transactions running in SERIALIZABLE isolation level?

  • Hi All

    We have an in-house application that has frequent deadlocks. When I inspect the deadlock reports, I see plenty of the following statements:

    isolationlevel="serializable (4)"

    When I trace the blocking sessions, I also see that the isolation level for these transactions is SERIALIZABLE.

    However, when I use DBCC USEROPTIONS, I find that my SQL instance's isolation level is set to READ COMMITTED. Inspecting the stored procedures that cause the deadlocks show no sign of setting the isolation level to SERIALIZABLE.

    So, my question is: Why is SERIALIZABLE being used for these transactions? What other factors would cause a transaction to run SERIALIZABLE?

    Apparently, the .NET Framework TransactionScope object can request an isolation level of SERIALIZABLE when connecting to SQL Server, as per this link. However, our Developers have confirmed that this isn't he case here.

    Thanks very much!

    - Innerise

  • Innerise (11/18/2014)


    So, my question is: Why is SERIALIZABLE being used for these transactions? What other factors would cause a transaction to run SERIALIZABLE?

    A SET TRANSACTION ISOLATION LEVEL statement or a request from the application to run in serialisable. May want to go back to the devs....

    Check the connection strings that .net is using, check the session options that it sets (you may need to run some targeted sql traces)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • You can also look in sys.dm_exec_sessions and the transaction_isolation_level column to find out if the devs know what they are doing. Here's the mapping from BOL:

    0 = Unspecified

    1 = ReadUncomitted

    2 = ReadCommitted

    3 = Repeatable

    4 = Serializable

    5 = Snapshot

  • Thanks very much for the helpful replies.

    I've used SYS.DM_EXEC_SESSIONS to look for running sessions using a SERIALIZABLE isolation level and we have many. I've looked into the query text for these sessions (all SPs) and the majority of them don't include a SET TRANSACTION ISOLATION LEVEL statement.

    Strangely, one of the SPs includes a SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED statement, but was still running as SERIALIZABLE :unsure:

    So, I guess that the only other possibility is a request from the application to run in SERIALIZABLE. I'll go back to the Developers with this new information and see what they say.

    Another quick question: When using SYS.DM_EXEC_CONNECTIONS, I see multiple rows for the same session ID, all of them SERIALIZABLE. Some of the rows have different CONNECT_TIMEs and different QUERY_TEXTs, so is this simply the same session running multiple queries/transactions?

  • My thought would be that you're spending a whole lot of time on something that might not help. IMHO, it would be much more fruitful to find the cause of the deadlocks in the code rather than trying to find the cause at a system level that probably shouldn't be changed.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (11/19/2014)


    My thought would be that you're spending a whole lot of time on something that might not help.

    In this case I'd disagree. Serialisable, when used unnecessarily, can easily cause deadlocks that would not occur in any other isolation level

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply