{"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           <\/span>Value<\/font><\/font>\n<\/p>\n

\ntextsize <\/span>2147483647<\/font><\/font>\n<\/p>\n

\nlanguage             <\/span>us_english<\/font><\/font>\n<\/p>\n

\ndateformat         <\/span>mdy<\/font><\/font>\n<\/p>\n

\ndatefirst               <\/span>7<\/font><\/font>\n<\/p>\n

\nlock_timeout      <\/span>-1<\/font><\/font>\n<\/p>\n

\nquoted_identifier             <\/span>SET<\/font><\/font>\n<\/p>\n

\narithabort            <\/span>SET<\/font><\/font>\n<\/p>\n

\nansi_null_dflt_on              <\/span>SET<\/font><\/font>\n<\/p>\n

\nansi_warnings    <\/span>SET<\/font><\/font>\n<\/p>\n

\nansi_padding      <\/span>SET<\/font><\/font>\n<\/p>\n

\nansi_nulls            <\/span>SET<\/font><\/font>\n<\/p>\n

\nconcat_null_yields_null   <\/span>SET<\/font><\/font>\n<\/p>\n

\nisolation level     <\/span>serializable<\/font><\/font><\/span>\n<\/p>\n

\n<\/font><\/font>\n<\/p>\n

\nAny other methods that you’re aware of that I didn't mention here?  <\/span>If so, post a comment to share.<\/font><\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20,25,28],"tags":[],"class_list":["post-520","post","type-post","status-publish","format-standard","hentry","category-deadlocking","category-locking","category-performance"],"yoast_head":"\nTracking isolation level - Joe Sack<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/tracking-isolation-level\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Tracking isolation level - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/tracking-isolation-level\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2011-10-26T07:34:00+00:00\" \/>\n<meta name=\"author\" content=\"Joseph Sack\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joseph Sack\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/tracking-isolation-level\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/tracking-isolation-level\/\",\"name\":\"Tracking isolation level - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2011-10-26T07:34:00+00:00\",\"dateModified\":\"2011-10-26T07:34:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/tracking-isolation-level\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/tracking-isolation-level\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/tracking-isolation-level\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Deadlocking\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/deadlocking\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Tracking isolation level\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\",\"name\":\"Joe Sack\",\"description\":\"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\",\"name\":\"Joseph Sack\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"caption\":\"Joseph Sack\"},\"description\":\"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.\",\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/joe\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Tracking isolation level - Joe Sack","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/joe\/tracking-isolation-level\/","og_locale":"en_US","og_type":"article","og_title":"Tracking isolation level - Joe Sack","og_description":"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 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/tracking-isolation-level\/","og_site_name":"Joe Sack","article_published_time":"2011-10-26T07:34:00+00:00","author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/tracking-isolation-level\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/tracking-isolation-level\/","name":"Tracking isolation level - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2011-10-26T07:34:00+00:00","dateModified":"2011-10-26T07:34:00+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/tracking-isolation-level\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/tracking-isolation-level\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/tracking-isolation-level\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Deadlocking","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/deadlocking\/"},{"@type":"ListItem","position":3,"name":"Tracking isolation level"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/","name":"Joe Sack","description":"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648","name":"Joseph Sack","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","caption":"Joseph Sack"},"description":"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.","sameAs":["http:\/\/3.209.169.194\/blogs\/joe","https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack"],"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/520","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/comments?post=520"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/520\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=520"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=520"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=520"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}