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.