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”
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
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.