{"id":41965,"date":"2025-12-15T11:42:51","date_gmt":"2025-12-15T10:42:51","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=41965"},"modified":"2025-12-22T22:56:08","modified_gmt":"2025-12-22T21:56:08","slug":"the-truth-about-nested-transactions-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/the-truth-about-nested-transactions-in-sql-server\/","title":{"rendered":"The truth about nested transactions in SQL Server"},"content":{"rendered":"\n<p>Working with transactions in SQL Server can feel like navigating a maze blindfolded. On paper, nested transactions look simple enough, start one, start another, commit them both, but under the hood, SQL Server plays by a very different set of rules. And that\u2019s exactly where developers get trapped.<\/p>\n\n\n\n<p>In this post, we\u2019re going to look at what really happens when you try to use nested transactions in SQL Server. We\u2019ll walk through a dead-simple demo, expose why <code>@@TRANCOUNT<\/code> is more illusion than isolation, and see how a single rollback can quietly unravel your entire call chain. If you\u2019ve ever assumed nested transactions can behave the same way as in Oracle for example, this might clarify a few things you didn\u2019t expect !<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-practical-example\">Practical example<\/h2>\n\n\n\n<p>Before diving into the demonstration, let\u2019s set up a simple table in <code>tempdb <\/code>and illustrate how nested transactions behave in SQL Server.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>IF OBJECT_ID('tempdb..##DemoLocks') IS NOT NULL\n    DROP TABLE ##DemoLocks;\n\nCREATE TABLE ##DemoLocks (id INT IDENTITY, text VARCHAR(50));\n\nBEGIN TRAN MainTran;\n\nBEGIN TRAN InnerTran;\nINSERT INTO ##DemoLocks (text) VALUES ('I''m just a speedy insert ! Nothing to worry about');\nCOMMIT TRAN InnerTran;\n\nWAITFOR DELAY '00:00:10';\n\nROLLBACK TRAN MainTran;<\/code><\/pre>\n\n\n\n<p>Let\u2019s see how locks behave after committing the nested transaction and entering the <code>WAITFOR <\/code>phase. If nested transactions provided isolation between each other, no locks should remain since the transaction no longer works on any object. The following query shows all locks associated with my query specifically and the <code>##Demolocks<\/code> table we are working on. <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \n    l.request_session_id AS SPID,\n    r.blocking_session_id AS BlockingSPID,\n    resource_associated_entity_id,\n    DB_NAME(l.resource_database_id) AS DatabaseName,\n    OBJECT_NAME(p.object_id) AS ObjectName,\n    l.resource_type AS ResourceType,\n    l.resource_description AS ResourceDescription,\n    l.request_mode AS LockMode,\n    l.request_status AS LockStatus,\n    t.text AS SQLText\nFROM sys.dm_tran_locks l\nLEFT JOIN sys.dm_exec_requests r\n    ON l.request_session_id = r.session_id\nLEFT JOIN sys.partitions p\n    ON l.resource_associated_entity_id = p.hobt_id\nOUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t\nwhere t.text like 'IF OBJECT%'\n    and OBJECT_NAME(p.object_id) = '##DemoLocks'\nORDER BY l.request_session_id, l.resource_type;<\/code><\/pre>\n\n\n\n<p>And the result :<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"90\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/12\/image-26-1024x90.png\" alt=\"\" class=\"wp-image-41967\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/12\/image-26-1024x90.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/12\/image-26-300x26.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/12\/image-26-768x67.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/12\/image-26.png 1371w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>All of this was just smoke and mirrors !<br>We clearly see in the image 2 persistent locks of different types:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>LockMode IX: Intent lock on a data page of the <code>##DemoLocks<\/code> table. This indicates that a lock is active on one of its sub-elements to optimize the engine\u2019s lock checks.<\/li>\n\n\n\n<li>LockMode X: Exclusive lock on a <code>RID <\/code>(Row Identifier) for data writing (here, our <code>INSERT<\/code>).<br><br>For more on locks and their usage : <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/system-dynamic-management-views\/sys-dm-tran-locks-transact-sql?view=sql-server-ver17\">sys.dm_tran_locks (Transact-SQL) &#8211; SQL Server | Microsoft Learn<\/a><\/li>\n<\/ul>\n\n\n\n<p>In conclusion, SQL Server does not allow nested transactions to maintain isolation from each other, and causes nested transactions to remain dependent on their main transaction, which prevents the release of locks. Therefore, the rollback of <code>MainTran<\/code> causes the above query to leave the table <span style=\"text-decoration: underline\"><strong>empty<\/strong><\/span>, even with a <code>COMMIT<\/code> at the nested transaction level. This behavior still respects the ACID properties (Atomicity, Consistency, Isolation, and Durability), which are crucial for maintaining data validity and reliability in database management systems.<\/p>\n\n\n\n<p>Now that we have shown that nested transactions have no useful effect on lock management and isolation, let\u2019s see if they have even worse consequences. To do this, let\u2019s create the following code and observe how SQL Server behaves under intensive nested transaction creation. This time, we will add SQL Server\u2019s native <code><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/functions\/trancount-transact-sql?view=sql-server-ver17\">@@TRANCOUNT<\/a><\/code> variable, which allows us to analyze the number of open transactions currently in progress.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code> CREATE PROCEDURE dbo.NestedProc\n    @level INT\nAS\nBEGIN\n    BEGIN TRANSACTION;\n\n    PRINT 'Level ' + CAST(@level AS VARCHAR(3)) + ', @@TRANCOUNT = ' + CAST(@@TRANCOUNT AS VARCHAR(3));\n\n    IF @level &lt; 100\n    BEGIN\n        SET @level += 1\n        EXEC dbo.NestedProc @level;\n    END\n\n    COMMIT TRANSACTION;\nEND\nGO\n\nEXEC dbo.NestedProc 1;<\/code><\/pre>\n\n\n\n<p>This procedure recursively creates 100 nested transactions, if we manage to go that far\u2026 Let\u2019s look at the output.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Level 1, @@TRANCOUNT = 1\n&#091;...]\nLevel 32, @@TRANCOUNT = 32\n\nMsg 217, Level 16, State 1, Procedure dbo.NestedProc, Line 12 &#091;Batch Start Line 15]\nMaximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).<\/code><\/pre>\n\n\n\n<p>Indeed, SQL Server imposes various <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/sql-server\/maximum-capacity-specifications-for-sql-server?view=sql-server-ver17\">limitations <\/a>on nested transactions which imply that if they are mismanaged, the application may suddenly suffer a killed query, which can be very dangerous. These limitations are in place to act as safeguards against infinite nesting loops of nested transactions.<br>Furthermore, we see that <code>@@TRANCOUNT<\/code> increments with each new <code>BEGIN TRANSACTION<\/code>, but it does not reflect the true number of active main transactions; i.e., there are 32 transactions ongoing but only 1 can actually release locks.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-ok-but-we-still-didn-t-see-what-a-real-nested-transaction-would-look-like\">Ok, but we still didn&#8217;t see what a real nested transaction would look like !<\/h2>\n\n\n\n<p id=\"h-ok-but-we-still-didn-t-see-any-real-nested-transaction\">I understand, we cannot stop here. I need to go get my old Oracle VM from my garage and fire it up.<br>Oracle has a pragma called <a href=\"https:\/\/docs.oracle.com\/cd\/B13789_01\/appdev.101\/b10807\/13_elems002.htm\">AUTONOMOUS_TRANSACTION<\/a> that allows creating independent transactions inside a main transaction. Let\u2019s see this in action with a small code snippet.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE test_autonomous (\n    id NUMBER PRIMARY KEY,\n    msg VARCHAR2(100)\n);\n\/\n\nCREATE OR REPLACE PROCEDURE auton_proc IS\n    PRAGMA AUTONOMOUS_TRANSACTION;\nBEGIN\n    INSERT INTO test_autonomous (id, msg) VALUES (2, 'Autonomous transaction');\n    COMMIT;\nEND;\n\/\n\nCREATE OR REPLACE PROCEDURE main_proc IS\nBEGIN\n    INSERT INTO test_autonomous (id, msg) VALUES (1, 'Main transaction');\n    auton_proc;\n    ROLLBACK;\nEND;\n\/<\/code><\/pre>\n\n\n\n<p>In this code, we create two procedures:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>main_proc<\/code>, the main procedure, inserts the first row into the table.<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>auton_proc<\/code>, called by main_proc, adds a second row to the table.<\/li>\n<\/ul>\n\n\n\n<p><code>auton_proc <\/code>is committed while <code>main_proc <\/code>is rolled back. Let\u2019s observe the result:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; SELECT * FROM test_auton;\n\n        ID MSG\n---------- --------------------------------------------------\n         2 Autonomous transaction<\/code><\/pre>\n\n\n\n<p>Now, that is true isolation between transactions! Here, the inner transaction achieves transactional independence and can persist regardless of the fate of its main transaction. While autonomous transactions in Oracle are not qualified as purely nested because they do not share a common locking context, they serve the same architectural purpose as the one we are demonstrating in this article: allowing a sub-unit of work to decouple its success from the parent flow.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-summary\">Summary<\/h2>\n\n\n\n<p>In summary, SQL Server and Oracle can handle transactions in different ways. In SQL Server, <em>nested transactions<\/em> do not create real isolation: <code>@@TRANCOUNT<\/code> may increase, but a single main transaction actually controls locks and the persistence of changes. Internal limits, like the maximum nesting of 32 procedures, show that excessive nested transactions can cause critical errors.<\/p>\n\n\n\n<p>In contrast, Oracle, through <code>PRAGMA AUTONOMOUS_TRANSACTION<\/code>, allows for truly independent transactions launched from within a main flow. These autonomous transactions can be committed or rolled back without affecting the parent transaction, providing a practical mechanism to decouple specific tasks from the main transactional outcome.<\/p>\n\n\n\n<p>As <a href=\"https:\/\/www.brentozar.com\/archive\/2023\/02\/can-you-nest-transactions-in-sql-server\/\">Brent Ozar<\/a> points out, SQL Server also has a <code>SAVE TRANSACTION<\/code> <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/language-elements\/save-transaction-transact-sql?view=sql-server-ver17\">command<\/a>, which allows you to save a state after a nested transaction has been committed, for example. This command therefore provides more flexibility in managing nested transactions but does not provide complete isolation of sub-transactions. Furthermore, as Brent Ozar emphasizes, this command is complex and requires careful analysis of its behavior and the consequences it entails.<br>Another approach to bypass SQL Server\u2019s nested-transaction limitations is to manage transaction coordination directly at the application level, where each logical unit of work can be handled independently.<\/p>\n\n\n\n<p>The lesson is clear: appearances can be deceiving! Understanding the actual behavior of transactions in each DBMS is crucial for designing reliable applications and avoiding unpleasant surprises.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Nested transactions in SQL Server don\u2019t provide real isolation; @@TRANCOUNT rises, but only the main transaction controls locks.<\/p>\n","protected":false},"author":157,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198,99],"tags":[3776,51],"type_dbi":[],"class_list":["post-41965","post","type-post","status-publish","format-standard","hentry","category-database-management","category-sql-server","tag-nested-transactions","tag-sql-server"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>The truth about nested transactions in SQL Server - dbi Blog<\/title>\n<meta name=\"description\" content=\"Nested transactions in SQL Server don\u2019t provide real isolation; @@TRANCOUNT rises, but only the main transaction controls locks.\" \/>\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.dbi-services.com\/blog\/the-truth-about-nested-transactions-in-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The truth about nested transactions in SQL Server\" \/>\n<meta property=\"og:description\" content=\"Nested transactions in SQL Server don\u2019t provide real isolation; @@TRANCOUNT rises, but only the main transaction controls locks.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/the-truth-about-nested-transactions-in-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2025-12-15T10:42:51+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-12-22T21:56:08+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/12\/image-26.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1371\" \/>\n\t<meta property=\"og:image:height\" content=\"120\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Louis Tochon\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Louis Tochon\" \/>\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\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-truth-about-nested-transactions-in-sql-server\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-truth-about-nested-transactions-in-sql-server\\\/\"},\"author\":{\"name\":\"Louis Tochon\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/e4195b0cb120295b3407a502c23e75b6\"},\"headline\":\"The truth about nested transactions in SQL Server\",\"datePublished\":\"2025-12-15T10:42:51+00:00\",\"dateModified\":\"2025-12-22T21:56:08+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-truth-about-nested-transactions-in-sql-server\\\/\"},\"wordCount\":931,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-truth-about-nested-transactions-in-sql-server\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2025\\\/12\\\/image-26-1024x90.png\",\"keywords\":[\"nested transactions\",\"SQL Server\"],\"articleSection\":[\"Database management\",\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-truth-about-nested-transactions-in-sql-server\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-truth-about-nested-transactions-in-sql-server\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-truth-about-nested-transactions-in-sql-server\\\/\",\"name\":\"The truth about nested transactions in SQL Server - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-truth-about-nested-transactions-in-sql-server\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-truth-about-nested-transactions-in-sql-server\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2025\\\/12\\\/image-26-1024x90.png\",\"datePublished\":\"2025-12-15T10:42:51+00:00\",\"dateModified\":\"2025-12-22T21:56:08+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/e4195b0cb120295b3407a502c23e75b6\"},\"description\":\"Nested transactions in SQL Server don\u2019t provide real isolation; @@TRANCOUNT rises, but only the main transaction controls locks.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-truth-about-nested-transactions-in-sql-server\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-truth-about-nested-transactions-in-sql-server\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-truth-about-nested-transactions-in-sql-server\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2025\\\/12\\\/image-26.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2025\\\/12\\\/image-26.png\",\"width\":1371,\"height\":120},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-truth-about-nested-transactions-in-sql-server\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The truth about nested transactions in SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/e4195b0cb120295b3407a502c23e75b6\",\"name\":\"Louis Tochon\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/ce0ee48c64e763e6c4076e21c80729d15bc4493288aeb8695125c69082100e10?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/ce0ee48c64e763e6c4076e21c80729d15bc4493288aeb8695125c69082100e10?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/ce0ee48c64e763e6c4076e21c80729d15bc4493288aeb8695125c69082100e10?s=96&d=mm&r=g\",\"caption\":\"Louis Tochon\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/louistochon\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"The truth about nested transactions in SQL Server - dbi Blog","description":"Nested transactions in SQL Server don\u2019t provide real isolation; @@TRANCOUNT rises, but only the main transaction controls locks.","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.dbi-services.com\/blog\/the-truth-about-nested-transactions-in-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"The truth about nested transactions in SQL Server","og_description":"Nested transactions in SQL Server don\u2019t provide real isolation; @@TRANCOUNT rises, but only the main transaction controls locks.","og_url":"https:\/\/www.dbi-services.com\/blog\/the-truth-about-nested-transactions-in-sql-server\/","og_site_name":"dbi Blog","article_published_time":"2025-12-15T10:42:51+00:00","article_modified_time":"2025-12-22T21:56:08+00:00","og_image":[{"width":1371,"height":120,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/12\/image-26.png","type":"image\/png"}],"author":"Louis Tochon","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Louis Tochon","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/the-truth-about-nested-transactions-in-sql-server\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-truth-about-nested-transactions-in-sql-server\/"},"author":{"name":"Louis Tochon","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/e4195b0cb120295b3407a502c23e75b6"},"headline":"The truth about nested transactions in SQL Server","datePublished":"2025-12-15T10:42:51+00:00","dateModified":"2025-12-22T21:56:08+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-truth-about-nested-transactions-in-sql-server\/"},"wordCount":931,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-truth-about-nested-transactions-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/12\/image-26-1024x90.png","keywords":["nested transactions","SQL Server"],"articleSection":["Database management","SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/the-truth-about-nested-transactions-in-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/the-truth-about-nested-transactions-in-sql-server\/","url":"https:\/\/www.dbi-services.com\/blog\/the-truth-about-nested-transactions-in-sql-server\/","name":"The truth about nested transactions in SQL Server - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-truth-about-nested-transactions-in-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-truth-about-nested-transactions-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/12\/image-26-1024x90.png","datePublished":"2025-12-15T10:42:51+00:00","dateModified":"2025-12-22T21:56:08+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/e4195b0cb120295b3407a502c23e75b6"},"description":"Nested transactions in SQL Server don\u2019t provide real isolation; @@TRANCOUNT rises, but only the main transaction controls locks.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-truth-about-nested-transactions-in-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/the-truth-about-nested-transactions-in-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/the-truth-about-nested-transactions-in-sql-server\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/12\/image-26.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/12\/image-26.png","width":1371,"height":120},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/the-truth-about-nested-transactions-in-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"The truth about nested transactions in SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/e4195b0cb120295b3407a502c23e75b6","name":"Louis Tochon","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/ce0ee48c64e763e6c4076e21c80729d15bc4493288aeb8695125c69082100e10?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/ce0ee48c64e763e6c4076e21c80729d15bc4493288aeb8695125c69082100e10?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ce0ee48c64e763e6c4076e21c80729d15bc4493288aeb8695125c69082100e10?s=96&d=mm&r=g","caption":"Louis Tochon"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/louistochon\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/41965","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/157"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=41965"}],"version-history":[{"count":24,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/41965\/revisions"}],"predecessor-version":[{"id":42200,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/41965\/revisions\/42200"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=41965"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=41965"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=41965"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=41965"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}