sqlskills-logo-2015-white.png

Tracking isolation level

When I'm troubleshooting concurrency issues – be it deadlocking or long duration blocks, I’m immediately curious about the isolation level being used.   While I may be told that the isolation level is "XYZ", I don’t believe it until I actually see it for myself.

There are multiple areas where this information can be retrieved.  This post lays out a few areas that you can check (I highlight where you can find the isolation level):

Deadlock Output (for example via trace flag 1222)

Look for the "isolationlevel" attribute:

<deadlock-list>

 <deadlock victim="process5c26e08">

  <process-list>

   <process id="process5c26e08" taskpriority="0" logused="132" waitresource="OBJECT: 7:327672215:0 " waittime="3333" ownerId="98274" transactionname="user_transaction" lasttranstarted="2011-10-26T13:35:43.703" XDES="0x870a7900" lockMode="IS" schedulerid="4" kpid="8604" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2011-10-26T13:35:51.337" lastbatchcompleted="2011-10-26T13:35:43.703" clientapp="Microsoft SQL Server Management Studio – Query" hostname="JOSEPHSACK-PC" hostpid="1528" loginname="JosephSack-PC\JosephSack" isolationlevel="read committed (2)" xactid="98274" currentdb="7" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">

Blocked Process Report Event Class

Again, look for the "isolationlevel" attribute:

<blocked-process-report>

 <blocked-process>

  <process id="process5c4f4c8" taskpriority="0" logused="0" waitresource="OBJECT: 8:309576141:0 " waittime="29111" ownerId="52396" transactionname="user_transaction" lasttranstarted="2011-10-26T13:20:00.470" XDES="0x808f9950" lockMode="IS" schedulerid="8" kpid="1232" status="suspended" spid="54" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2011-10-26T13:20:00.467" lastbatchcompleted="2011-10-26T13:16:25.090" clientapp="Microsoft SQL Server Management Studio – Query" hostname="JOSEPHSACK-PC" hostpid="1528" loginname="JosephSack-PC\JosephSack" isolationlevel="serializable (4)" xactid="52396" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">

   <executionStack>

    <frame line="5" stmtstart="124" sqlhandle="0x02000000ee40c5333b10546e7e03ae64690403919e4a0968"/>

   </executionStack>

   <inputbuf>

Extended Events (for example – the system health session and 'xml_deadlock_report')

(You guessed it) look for the "isolationlevel" attribute:

For more on the system health session, see “An XEvent a Day (13 of 31) – The system_health Session” by Jonathan Kehayias.

<deadlock-list>

  <deadlock>

    <victim-list>

      <victimProcess id="process5c26e08" />

    </victim-list>

    <process-list>

      <process id="process5c26e08" taskpriority="0" logused="132" waitresource="OBJECT: 7:327672215:0 " waittime="3330" ownerId="98274" transactionname="user_transaction" lasttranstarted="2011-10-26T13:35:43.703" XDES="0x870a7900" lockMode="IS" schedulerid="4" kpid="8604" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2011-10-26T13:35:51.337" lastbatchcompleted="2011-10-26T13:35:43.703" clientapp="Microsoft SQL Server Management Studio – Query" hostname="JOSEPHSACK-PC" hostpid="1528" loginname="JosephSack-PC\JosephSack" isolationlevel="read committed (2)" xactid="98274" currentdb="7" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">

        <executionStack>

          <frame procname="" line="1" sqlhandle="0x0200000083ae07243ea5b277257eb9ac5886fa316abfa4ce" />

        </executionStack>

        <inputbuf>

DMVs

You can see the isolation level in two different DMVs (session / requests):

SELECT session_id, transaction_isolation_level

FROM sys.dm_exec_sessions

WHERE session_id = 53

 

SELECT session_id, transaction_isolation_level

FROM sys.dm_exec_requests

WHERE session_id = 53

 

** But be aware – this isn't an entirely reliable way of tracking isolation level. **  Why? Consider the following query:

 

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

WHILE 1=1

BEGIN

SELECT *

FROM HumanResources.Department

WITH (SERIALIZABLE)

END

 

If you checked either sys.dm_exec_sessions or sys.dm_exec_requests (if you catch it at the moment it is running that is) – you would see an isolation level of “2” (ReadCommitted) – even though the query itself is hinted to use SERIALIZABLE.

 

In-session checking

If you happen to be in the session that you’re curious about, you can use DBCC USEROPTIONS to validate isolation level:

               DBCC USEROPTIONS

Returns:

Set Option           Value

textsize 2147483647

language             us_english

dateformat         mdy

datefirst               7

lock_timeout      -1

quoted_identifier             SET

arithabort            SET

ansi_null_dflt_on              SET

ansi_warnings    SET

ansi_padding      SET

ansi_nulls            SET

concat_null_yields_null   SET

isolation level     serializable

Any other methods that you’re aware of that I didn't mention here?  If so, post a comment to share.

2 thoughts on “Tracking isolation level

  1. Hi Joe,
    That means the DMVs sys.dm_exec_sessions or sys.dm_exec_requests are misleading about current isolation level for session/spid. It is very good point to check with DBCC USEROPTIONS.
    Great post.
    Thanks
    Ponnu

  2. Thanks Ponnu!

    Yes – those DMVs can be misleading. However if I see them coming in with a more restrictive isolation level, it is still noteworthy.

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.