{"id":2008,"date":"2018-06-10T17:36:36","date_gmt":"2018-06-10T17:36:36","guid":{"rendered":"http:\/\/nenadnoveljic.com\/blog\/?p=2008"},"modified":"2018-06-10T17:36:36","modified_gmt":"2018-06-10T17:36:36","slug":"dead-transaction-rollback","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/dead-transaction-rollback\/","title":{"rendered":"Dead Transaction Rollback"},"content":{"rendered":"<p>A crashed process can leave a dead transaction behind. Generally, SMON is the one who&#8217;ll be rolling it back. Under certain circumstances, though, a server process might lend a helping hand. That&#8217;s exactly the scenario we&#8217;ll be focusing on. The article is based on the Oracle version 12.2.0.1.180116.<\/p>\n<p>A dead transaction can be identified in the transaction table, which in turn is exposed in the memory structure x$ktuxe:<\/p>\n<pre><code>\r\ncolumn usn format 99\r\ncolumn slt format 99\r\n\r\nselect ktuxeusn usn, ktuxeslt slt, ktuxesqn sqn, ktuxesiz siz, \r\n    '0x' || to_char(ktuxeusn,'FM0xxxx') || '.' || to_char(ktuxeslt,'FM0xxx') ||\r\n    '.' || to_char(ktuxesqn,'FM0xxxxxxxx') xid \r\n  from x$ktuxe where KTUXECFL='DEAD' ;                                                                                                                            \r\n\r\nUSN SLT        SQN        SIZ XID\r\n--- --- ---------- ---------- -------------------------\r\n<span style=\"color:blue\">10  12<\/span>    4948315    5384191 <span style=\"color:red\">0x0000a.000c.0004b815b<\/span>\r\n<\/code><\/pre>\n<p>The meanings of the selected columns are as follows:<\/p>\n<ul>\n<li>ktuxusn  &#8211; undo segment number<\/li>\n<li>ktuxeslt &#8211; slot number in the transaction table<\/li>\n<li>ktuxesqn &#8211; the number of times that the slot has been used (a.k.a wrap#)<\/li>\n<li>ktuxesiz &#8211; remaining number of undo blocks to be applied in rollback<\/li>\n<li>xid      &#8211; transaction id (ktuxusn.ktuxeslt.ktuxesqn in hexadecimal)<\/li>\n<\/ul>\n<p>There are still 5384191 million undo blocks left to apply for rolling back the dead transaction above. During this time ktuxesiz keeps decreasing. After stopping the SMON with pstop, its value will remain constant, which proves that the SMON is the process doing the recovery.<\/p>\n<p>But what will happen if we try to change the data that still have to be rolled back?<\/p>\n<p>To answer this question, we first need to find a block containing some data changed by the dead transaction. Below is the excerpt from the dump of such block showing its interested transaction list (ITL):<\/p>\n<pre><code> Itl           Xid                  Uba         Flag  Lck        Scn\/Fsc\r\n0x01   0x000c.006.0058fe83  0x00d5d0f8.d426.33  C---    0  scn  0x00000004ed0206b5\r\n0x02   0x000c.010.00590c33  0x00d67d44.d42a.26  C---    0  scn  0x00000004ed023e3c\r\n0x03   <span style=\"color:red\">0x000a.00c.004b815b<\/span>  0x00c0cf8c.9116.2e  ----    1  fsc  0x021e.00000000<\/code><\/pre>\n<p>Our dead transaction <span style=\"color:red\">0x000a.00c.004b815b<\/span> is placed in the third slot in the blocks&#8217;s ITL.<\/p>\n<p>A DML statement that is changing data in this block won&#8217;t expectedly end until the rollback is finished. Session statistics collected with <a href=\"https:\/\/blog.tanelpoder.com\/about\/\">Tanel Poder&#8217;s<\/a> script <a href=\"http:\/\/tech.e2sn.com\/oracle-scripts-and-tools\/session-snapper\">Snapper<\/a> provide a cue about what the server process is doing during this time:<\/p>\n<pre><code>\r\n@snapper stats,gather=s 5 5 256\r\n------------------------------------------------------------------------------------------------------------------------------\r\nTYPE, STATISTIC                                , DELTA, HDELTA\/SEC,    %TIME, GRAPH       , NUM_WAITS,  WAITS\/SEC,   AVERAGES\r\n------------------------------------------------------------------------------------------------------------------------------\r\n...\r\nSTAT, <span style=\"color:brown\">rollback changes - undo records applied<\/span>  , 36009,      6.77k,         ,             ,          ,           ,   14.13k per execution\r\n...\r\n<\/code><\/pre>\n<p>As you can see, the server process is doing a rollback. Moreover, it applied 36009 undo records in the time interval covered by the snapshot. Yet no explicit rollbacks have been issued &#8211; neither &#8220;transaction rollback&#8221; nor &#8220;user rollback&#8221; counter has been incremented. <\/p>\n<p>ktuxesiz will keep decreasing after we stop the SMON, which proves that the dedicated process is rolling back our dead transaction. Also, we can double-check that by tracing the Oracle function kturRecoverUndoSegment. This function seems to accept undo segment number and slot as the first and the fifth argument, respectively.<\/p>\n<p>The DTrace script must start before the DML statement:<\/p>\n<pre><code># pragma D option quiet\r\npid$target:oracle:kturRecoverUndoSegment:entry\r\n{\r\n  printf(\"USN: %d, SLOT: %d \\n\",arg0,arg4);\r\n}<\/code><\/pre>\n<p>USN und SLOT correspond indeed with our dead transaction:<\/p>\n<pre><code>USN: <span style=\"color:blue\">10<\/span>, SLOT: <span style=\"color:blue\">12<\/span><\/code><\/pre>\n<p>In fact, implementing this behaviour was a smart decision &#8211; when a dedicated process wants to change the data that have to be rolled back, it starts doing rollback instead of just idly waiting on SMON to finish the work. \t<\/p>\n<p>References:<\/p>\n<ul>\n<li><a href=\"https:\/\/www.apress.com\/de\/book\/9781430239543\">Oracle Core: Essential Internals for DBAs and Developers<\/a>, <a href=\"https:\/\/jonathanlewis.wordpress.com\/\">Jonathan Lewis<\/a><\/li>\n<li>IF: Transaction Recovery or Rollback of Dead Transactions (Doc ID 1951738.1), Oracle Corporation<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>A crashed process can leave a dead transaction behind. Generally, SMON is the one who&#8217;ll be rolling it back. Under certain circumstances, though, a server process might lend a helping hand. That&#8217;s exactly the scenario we&#8217;ll be focusing on.  <a href=\"https:\/\/nenadnoveljic.com\/blog\/dead-transaction-rollback\/\" class=\"more-link\">Continue Reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[24,5],"tags":[],"class_list":["post-2008","post","type-post","status-publish","format-standard","hentry","category-dtrace","category-oracle"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Dead Transaction Rollback - All-round Database Topics<\/title>\n<meta name=\"description\" content=\"A crashed process can leave a dead transaction behind. Generally, SMON is the one who&#039;ll be rolling it back. Under certain circumstances, though, a server process might lend a helping hand. That&#039;s exactly the scenario we&#039;ll be focusing on.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/nenadnoveljic.com\/blog\/dead-transaction-rollback\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Dead Transaction Rollback - All-round Database Topics\" \/>\n<meta property=\"og:description\" content=\"A crashed process can leave a dead transaction behind. Generally, SMON is the one who&#039;ll be rolling it back. Under certain circumstances, though, a server process might lend a helping hand. That&#039;s exactly the scenario we&#039;ll be focusing on.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/nenadnoveljic.com\/blog\/dead-transaction-rollback\/\" \/>\n<meta property=\"og:site_name\" content=\"All-round Database Topics\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-10T17:36:36+00:00\" \/>\n<meta name=\"author\" content=\"Nenad Noveljic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@NenadNoveljic\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nenad Noveljic\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/dead-transaction-rollback\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/dead-transaction-rollback\\\/\"},\"author\":{\"name\":\"Nenad Noveljic\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"headline\":\"Dead Transaction Rollback\",\"datePublished\":\"2018-06-10T17:36:36+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/dead-transaction-rollback\\\/\"},\"wordCount\":463,\"commentCount\":2,\"articleSection\":[\"DTrace\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/dead-transaction-rollback\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/dead-transaction-rollback\\\/\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/dead-transaction-rollback\\\/\",\"name\":\"Dead Transaction Rollback - All-round Database Topics\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\"},\"datePublished\":\"2018-06-10T17:36:36+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"description\":\"A crashed process can leave a dead transaction behind. Generally, SMON is the one who'll be rolling it back. Under certain circumstances, though, a server process might lend a helping hand. That's exactly the scenario we'll be focusing on.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/dead-transaction-rollback\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/dead-transaction-rollback\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/dead-transaction-rollback\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Dead Transaction Rollback\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\",\"name\":\"All-round Database Topics\",\"description\":\"Nenad Noveljic\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\",\"name\":\"Nenad Noveljic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"caption\":\"Nenad Noveljic\"},\"sameAs\":[\"nenad-noveljic-9b746a6\",\"https:\\\/\\\/x.com\\\/NenadNoveljic\"],\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/author\\\/nenad\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Dead Transaction Rollback - All-round Database Topics","description":"A crashed process can leave a dead transaction behind. Generally, SMON is the one who'll be rolling it back. Under certain circumstances, though, a server process might lend a helping hand. That's exactly the scenario we'll be focusing on.","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:\/\/nenadnoveljic.com\/blog\/dead-transaction-rollback\/","og_locale":"en_US","og_type":"article","og_title":"Dead Transaction Rollback - All-round Database Topics","og_description":"A crashed process can leave a dead transaction behind. Generally, SMON is the one who'll be rolling it back. Under certain circumstances, though, a server process might lend a helping hand. That's exactly the scenario we'll be focusing on.","og_url":"https:\/\/nenadnoveljic.com\/blog\/dead-transaction-rollback\/","og_site_name":"All-round Database Topics","article_published_time":"2018-06-10T17:36:36+00:00","author":"Nenad Noveljic","twitter_card":"summary_large_image","twitter_creator":"@NenadNoveljic","twitter_misc":{"Written by":"Nenad Noveljic","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/nenadnoveljic.com\/blog\/dead-transaction-rollback\/#article","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/dead-transaction-rollback\/"},"author":{"name":"Nenad Noveljic","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"headline":"Dead Transaction Rollback","datePublished":"2018-06-10T17:36:36+00:00","mainEntityOfPage":{"@id":"https:\/\/nenadnoveljic.com\/blog\/dead-transaction-rollback\/"},"wordCount":463,"commentCount":2,"articleSection":["DTrace","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/nenadnoveljic.com\/blog\/dead-transaction-rollback\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/nenadnoveljic.com\/blog\/dead-transaction-rollback\/","url":"https:\/\/nenadnoveljic.com\/blog\/dead-transaction-rollback\/","name":"Dead Transaction Rollback - All-round Database Topics","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#website"},"datePublished":"2018-06-10T17:36:36+00:00","author":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"description":"A crashed process can leave a dead transaction behind. Generally, SMON is the one who'll be rolling it back. Under certain circumstances, though, a server process might lend a helping hand. That's exactly the scenario we'll be focusing on.","breadcrumb":{"@id":"https:\/\/nenadnoveljic.com\/blog\/dead-transaction-rollback\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/nenadnoveljic.com\/blog\/dead-transaction-rollback\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/nenadnoveljic.com\/blog\/dead-transaction-rollback\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/nenadnoveljic.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Dead Transaction Rollback"}]},{"@type":"WebSite","@id":"https:\/\/nenadnoveljic.com\/blog\/#website","url":"https:\/\/nenadnoveljic.com\/blog\/","name":"All-round Database Topics","description":"Nenad Noveljic","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/nenadnoveljic.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa","name":"Nenad Noveljic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","caption":"Nenad Noveljic"},"sameAs":["nenad-noveljic-9b746a6","https:\/\/x.com\/NenadNoveljic"],"url":"https:\/\/nenadnoveljic.com\/blog\/author\/nenad\/"}]}},"_links":{"self":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2008","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/comments?post=2008"}],"version-history":[{"count":1,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2008\/revisions"}],"predecessor-version":[{"id":2027,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2008\/revisions\/2027"}],"wp:attachment":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/media?parent=2008"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/categories?post=2008"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/tags?post=2008"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}