Warning: Constant WP_TEMP_DIR already defined in /var/www/html/blogs/joe/wp-config.php on line 93
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/joe/wp-config.php:93) in /var/www/html/blogs/joe/wp-includes/rest-api/class-wp-rest-server.php on line 1902
{"id":520,"date":"2011-10-26T07:34:00","date_gmt":"2011-10-26T07:34:00","guid":{"rendered":"\/blogs\/joe\/post\/Tracking-isolation-level.aspx"},"modified":"2011-10-26T07:34:00","modified_gmt":"2011-10-26T07:34:00","slug":"tracking-isolation-level","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/tracking-isolation-level\/","title":{"rendered":"Tracking isolation level"},"content":{"rendered":"\nWhen I'm troubleshooting concurrency issues – be it deadlocking or long duration blocks, I’m immediately curious about the isolation level being used. <\/span>While I may be told<\/em> that the isolation level is "XYZ", I don’t believe it until I actually see it for myself.<\/font><\/font>\n<\/p>\n\nThere are multiple areas where this information can be retrieved. <\/span>This post lays out a few areas that you can check (I highlight where you can find the isolation level):<\/font><\/font>\n<\/p>\n\nDeadlock Output (for example via trace flag 1222)<\/font><\/font><\/strong>\n<\/p>\n\nLook for the "isolationlevel" attribute:<\/font>\n<\/p>\n\n<deadlock-list><\/font><\/font>\n<\/p>\n\n <\/font><\/span><deadlock victim="process5c26e08"><\/font><\/font>\n<\/p>\n\n <\/font><\/span><process-list><\/font><\/font>\n<\/p>\n\n <\/font><\/span><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)"<\/font><\/span> xactid="98274" currentdb="7" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200"><\/font><\/font>\n<\/p>\n\n…<\/font><\/font>\n<\/p>\n\n<\/font><\/font>\n<\/p>\n\nBlocked Process Report Event Class<\/font><\/font><\/strong>\n<\/p>\n\nAgain, look for the "isolationlevel" attribute:<\/font>\n<\/p>\n\n…<\/font><\/font>\n<\/p>\n\n<blocked-process-report><\/font><\/font>\n<\/p>\n\n <\/font><\/span><blocked-process><\/font><\/font>\n<\/p>\n\n <\/font><\/span><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)"<\/font><\/span> xactid="52396" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200"><\/font><\/font>\n<\/p>\n\n <\/font><\/span><executionStack><\/font><\/font>\n<\/p>\n\n <\/font><\/span><frame line="5" stmtstart="124" sqlhandle="0x02000000ee40c5333b10546e7e03ae64690403919e4a0968"\/><\/font><\/font>\n<\/p>\n\n <\/font><\/span><\/executionStack><\/font><\/font>\n<\/p>\n\n <\/font><\/span><inputbuf><\/font><\/font>\n<\/p>\n\n…<\/font><\/font>\n<\/p>\n\nExtended Events (for example – the system health session and 'xml_deadlock_report')<\/font><\/font><\/strong>\n<\/p>\n\n(You guessed it) look for the "isolationlevel" attribute:<\/font>\n<\/p>\n\nFor more on the system health session, see “<\/font><\/font>An XEvent a Day (13 of 31) – The system_health Session<\/u><\/font><\/a><\/font>” by Jonathan Kehayias. <\/font><\/font><\/em>\n<\/p>\n\n<deadlock-list><\/font><\/font>\n<\/p>\n\n <\/font><\/span><deadlock><\/font><\/font>\n<\/p>\n\n <\/font><\/span><victim-list><\/font><\/font>\n<\/p>\n\n <\/font><\/span><victimProcess id="process5c26e08" \/><\/font><\/font>\n<\/p>\n\n <\/font><\/span><\/victim-list><\/font><\/font>\n<\/p>\n\n <\/font><\/span><process-list><\/font><\/font>\n<\/p>\n\n <\/font><\/span><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)"<\/font><\/span> xactid="98274" currentdb="7" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200"><\/font><\/font>\n<\/p>\n\n <\/font><\/span><executionStack><\/font><\/font>\n<\/p>\n\n <\/font><\/span><frame procname="" line="1" sqlhandle="0x0200000083ae07243ea5b277257eb9ac5886fa316abfa4ce" \/><\/font><\/font>\n<\/p>\n\n <\/font><\/span><\/executionStack><\/font><\/font>\n<\/p>\n\n <\/font><\/span><inputbuf><\/font><\/font>\n<\/p>\n\n<\/font><\/font>\n<\/p>\n\nDMVs<\/font><\/font><\/strong>\n<\/p>\n\nYou can see the isolation level in two different DMVs (session \/ requests):<\/font><\/font>\n<\/p>\n\nSELECT session_id, transaction_isolation_level<\/font><\/font>\n<\/p>\n\nFROM sys.dm_exec_sessions<\/font><\/font>\n<\/p>\n\nWHERE session_id = 53<\/font><\/font>\n<\/p>\n\n \n<\/p>\n
\n<\/font><\/font>\n<\/p>\n\nSELECT session_id, transaction_isolation_level<\/font><\/font>\n<\/p>\n\nFROM sys.dm_exec_requests<\/font><\/font>\n<\/p>\n\nWHERE session_id = 53<\/font><\/font>\n<\/p>\n\n \n<\/p>\n
\n<\/font><\/font>\n<\/p>\n\n** But be aware – this isn't an entirely reliable way of tracking isolation level. ** <\/font><\/font>Why? Consider the following query:<\/font><\/font>\n<\/p>\n\n \n<\/p>\n
\n<\/font><\/font>\n<\/p>\n\nSET TRANSACTION ISOLATION LEVEL READ COMMITTED<\/font><\/font>\n<\/p>\n\n<\/font><\/font>\n<\/p>\n\nWHILE 1=1<\/font><\/font>\n<\/p>\n\nBEGIN<\/font><\/font>\n<\/p>\n\nSELECT *<\/font><\/font>\n<\/p>\n\nFROM HumanResources.Department<\/font><\/font>\n<\/p>\n\nWITH (SERIALIZABLE)<\/font><\/font>\n<\/p>\n\nEND<\/font><\/font>\n<\/p>\n\n \n<\/p>\n
\n<\/font><\/font>\n<\/p>\n\nIf 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.<\/font><\/font>\n<\/p>\n\n \n<\/p>\n
\n<\/font><\/font>\n<\/p>\n\nIn-session checking<\/font><\/font><\/strong>\n<\/p>\n\nIf you happen to be in the session that you’re curious about, you can use DBCC USEROPTIONS to validate isolation level:<\/font><\/font>\n<\/p>\n\n <\/font><\/span>DBCC USEROPTIONS<\/font><\/font>\n<\/p>\n\nReturns:<\/font><\/font>\n<\/p>\n\nSet Option