{"id":701,"date":"2010-04-26T11:03:00","date_gmt":"2010-04-26T11:03:00","guid":{"rendered":"\/blogs\/paul\/post\/A-SQL-Server-DBA-myth-a-day-(2630)-nested-transactions-are-real.aspx"},"modified":"2017-07-27T11:17:47","modified_gmt":"2017-07-27T18:17:47","slug":"a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real\/","title":{"rendered":"A SQL Server DBA myth a day: (26\/30) nested transactions are real"},"content":{"rendered":"<p style=\"text-align: justify;\">(Look in the <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/category\/misconceptions\/\">Misconceptions<\/a> blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our <a href=\"https:\/\/www.sqlskills.com\/join-the-sqlskills-insider-community\/\" target=\"_blank\" rel=\"noopener noreferrer\">Insider list<\/a>, plus my online <a href=\"https:\/\/www.pluralsight.com\/courses\/sqlserver-myths\" target=\"_blank\" rel=\"noopener noreferrer\">Myths and Misconceptions training course<\/a> on Pluralsight.)<\/p>\n<p style=\"text-align: justify;\">Nested transactions are an evil invention designed to allow developers to make DBAs&#8217; lives miserable. In SQL Server, they are even more evil&#8230;<\/p>\n<p style=\"text-align: justify;\"><strong>Myth #26:<\/strong> <em>nested transactions are real in SQL Server.<\/em><\/p>\n<p style=\"text-align: justify;\"><strong><u><em>FALSE!!!<\/em><\/u><\/strong><\/p>\n<p style=\"text-align: justify;\">Nested transactions do not actually behave the way the syntax would have you believe. I have no idea why they were coded this way in SQL Server &#8211; all I can think of is someone from the dim and distant past is continually thumbing their nose at the SQL Server community and going &#8220;ha &#8211; fooled you!!&#8221;.<\/p>\n<p style=\"text-align: justify;\">Let me explain. SQL Server allows you to start transactions inside other transactions &#8211; called nested transactions. It allows you to commit them and to roll them back.<\/p>\n<p style=\"text-align: justify;\">The commit of a nested transaction has absolutely no effect &#8211; as the only transaction that really exists as far as SQL Server is concerned is the outer one. Can you say &#8216;uncontrolled transaction log growth&#8217;? Nested transactions are a common cause of transaction log growth problems because the developer thinks that all the work is being done in the inner transactions so there&#8217;s no problem.<\/p>\n<p style=\"text-align: justify;\">The rollback of a nested transaction rolls back the entire set of transactions &#8211; as there is no such thing as a nested transaction.<\/p>\n<p style=\"text-align: justify;\">Your developers should not use nested transactions. <em><u><strong>They are evil<\/strong><\/u><\/em>.<\/p>\n<p style=\"text-align: justify;\">If you don&#8217;t believe me, here&#8217;s some code to show you what I mean. First off &#8211; create a database with a table that each insert will cause 8KB in the log.<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nCREATE DATABASE &#x5B;NestedXactsAreNotReal];\r\nGO\r\nUSE &#x5B;NestedXactsAreNotReal];\r\nGO\r\nALTER DATABASE &#x5B;NestedXactsAreNotReal] SET RECOVERY SIMPLE;\r\nGO\r\nCREATE TABLE &#x5B;t1] (&#x5B;c1] INT IDENTITY, &#x5B;c2] CHAR (8000) DEFAULT 'a');\r\nCREATE CLUSTERED INDEX &#x5B;t1c1] ON &#x5B;t1] (&#x5B;c1]);\r\nGO\r\nSET NOCOUNT ON;\r\nGO\r\n<\/pre>\n<p><strong>Test #1:<\/strong> Does rolling back a nested transaction only roll back that nested transaction?<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nBEGIN TRAN OuterTran;\r\nGO\r\n\r\nINSERT INTO &#x5B;t1] DEFAULT VALUES;\r\nGO 1000\r\n\r\nBEGIN TRAN InnerTran;\r\nGO\r\n\r\nINSERT INTO &#x5B;t1] DEFAULT Values;\r\nGO 1000\r\n\r\nSELECT @@TRANCOUNT, COUNT (*) FROM &#x5B;t1];\r\nGO\r\n<\/pre>\n<p style=\"text-align: justify;\">I get back the results 2 and 2000. Now I&#8217;ll roll back the nested transaction and it should only roll back the 1000 rows inserted by the inner transaction&#8230;<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nROLLBACK TRAN InnerTran;\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nMsg 6401, Level 16, State 1, Line 1\r\nCannot roll back InnerTran. No transaction or savepoint of that name was found.\r\n<\/pre>\n<p>Hmm&#8230; from Books Online, I can only use the name of the outer transaction or no name. I&#8217;ll try no name:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nROLLBACK TRAN;\r\nGO\r\n\r\nSELECT @@TRANCOUNT, COUNT (*) FROM &#x5B;t1];\r\nGO\r\n<\/pre>\n<p style=\"text-align: justify;\">And I get the results 0 and 0. As Books Online explains, <em>ROLLBACK TRAN<\/em> rolls back to the start of the outer transaction and sets <em>@@TRANCOUNT<\/em> to 0. All changes are rolled back. The only way to do what I want is to use <em>SAVE TRAN<\/em> and <em>ROLLBACK TRAN<\/em> to the savepoint name.<\/p>\n<p><strong>Test #2:<\/strong> Does committing a nested transaction really commit the changes made?<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nBEGIN TRAN OuterTran;\r\nGO\r\n\r\nBEGIN TRAN InnerTran;\r\nGO\r\n\r\nINSERT INTO &#x5B;t1] DEFAULT Values;\r\nGO 1000\r\n\r\nCOMMIT TRAN InnerTran;\r\nGO\r\n\r\nSELECT COUNT (*) FROM &#x5B;t1];\r\nGO\r\n<\/pre>\n<p style=\"text-align: justify;\">I get the result 1000, as expected. Now I&#8217;ll roll back the outer transaction and all the work done by the inner transaction should be preserved&#8230;<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nROLLBACK TRAN OuterTran;\r\nGO\r\n\r\nSELECT COUNT (*) FROM &#x5B;t1];\r\nGO\r\n<\/pre>\n<p>And I get back the result 0. Oops &#8211; committing the nested transaction did not make its changes durable.<\/p>\n<p><strong>Test #3:<\/strong> Does committing a nested transaction at least let me clear the log?<\/p>\n<p>I recreated the database again before running this so the log was minimally sized to begin with, and the output from <em>DBCC SQLPERF<\/em> below has been edited to only include the <em>NestedXactsAreNotReal<\/em> database.<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nBEGIN TRAN OuterTran;\r\nGO\r\n\r\nBEGIN TRAN InnerTran;\r\nGO\r\n\r\nINSERT INTO &#x5B;t1] DEFAULT Values;\r\nGO 1000\r\n\r\nDBCC SQLPERF ('LOGSPACE');\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nDatabase Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Log Size (MB) Log Space Used (%) Status\r\n--------------------- ------------- ------------------ -----------\r\nNestedXactsAreNotReal 12.05469\u00a0\u00a0\u00a0\u00a0\u00a0 95.81983\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\n<\/pre>\n<p>Now I&#8217;ll commit the nested transaction, run a checkpoint (which will clear all possible <em>transaction<\/em> log in the SIMPLE recovery model), and check the log space again:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nCOMMIT TRAN InnerTran;\r\nGO\r\n\r\nCHECKPOINT;\r\nGO\r\n\r\nDBCC SQLPERF ('LOGSPACE');\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nDatabase Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Log Size (MB) Log Space Used (%) Status\r\n--------------------- ------------- ------------------ -----------\r\nNestedXactsAreNotReal 12.05469\u00a0\u00a0\u00a0\u00a0\u00a0 96.25324\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\n<\/pre>\n<p style=\"text-align: justify;\">Hmm &#8211; no change &#8211; in fact the <em>Log Space Used (%)<\/em> has increased slightly from writing out the checkpoint log records (see <em><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-do-checkpoints-work-and-what-gets-logged\/\">How do checkpoints work and what gets logged<\/a><\/em>). Committing the nested transaction did not allow the log to clear. And of course not, because a rollback can be issued at any time which will roll back all the way to the start of the outer transaction &#8211; so all log records are required until the outer transaction commits or rolls back.<\/p>\n<p>And to prove it, I&#8217;ll commit the outer transaction and run a checkpoint:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nCOMMIT TRAN OuterTran;\r\nGO\r\n\r\nCHECKPOINT;\r\nGO\r\n\r\nDBCC SQLPERF ('LOGSPACE');\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nDatabase Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Log Size (MB) Log Space Used (%) Status\r\n--------------------- ------------- ------------------ -----------\r\nNestedXactsAreNotReal 12.05469\u00a0\u00a0\u00a0\u00a0\u00a0 26.4339\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\n<\/pre>\n<p>And it drops right down.<\/p>\n<p><strong><em><u>Nested transactions: just say no!<\/u><\/em><\/strong> (a public service announcement from the nice folks at SQLskills.com :-)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>(Look in the Misconceptions blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Nested transactions are an evil invention designed to allow developers [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[61,66,98],"tags":[],"class_list":["post-701","post","type-post","status-publish","format-standard","hentry","category-misconceptions","category-performance-tuning","category-transaction-log"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>A SQL Server DBA myth a day: (26\/30) nested transactions are real - Paul S. Randal<\/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\/paul\/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A SQL Server DBA myth a day: (26\/30) nested transactions are real - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(Look in the Misconceptions blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Nested transactions are an evil invention designed to allow developers [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2010-04-26T11:03:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-07-27T18:17:47+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\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\/paul\/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real\/\",\"name\":\"A SQL Server DBA myth a day: (26\/30) nested transactions are real - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2010-04-26T11:03:00+00:00\",\"dateModified\":\"2017-07-27T18:17:47+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A SQL Server DBA myth a day: (26\/30) nested transactions are real\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"A SQL Server DBA myth a day: (26\/30) nested transactions are real - Paul S. Randal","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\/paul\/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real\/","og_locale":"en_US","og_type":"article","og_title":"A SQL Server DBA myth a day: (26\/30) nested transactions are real - Paul S. Randal","og_description":"(Look in the Misconceptions blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Nested transactions are an evil invention designed to allow developers [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real\/","og_site_name":"Paul S. Randal","article_published_time":"2010-04-26T11:03:00+00:00","article_modified_time":"2017-07-27T18:17:47+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real\/","name":"A SQL Server DBA myth a day: (26\/30) nested transactions are real - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2010-04-26T11:03:00+00:00","dateModified":"2017-07-27T18:17:47+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"A SQL Server DBA myth a day: (26\/30) nested transactions are real"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/701","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=701"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/701\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=701"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=701"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=701"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}