-
Notifications
You must be signed in to change notification settings - Fork 0
/
tsql-transactions-simple-nesting.html
7 lines (6 loc) · 1.75 KB
/
tsql-transactions-simple-nesting.html
1
2
3
4
5
6
7
title: TSQL Transactions - simple nesting
date: 2008/04/25 11:01:00 -0700
tags: [{"name":"SQLServer","id":58674754}]
author: Jane Dallaway
alias: /tsql-transactions-simple-nesting
<p>After <a href="http://bruceboughton.me.uk/">Bruce</a> and I were looking at <a href="http://code.google.com/p/dbverse/">DBVerse</a> <a href="http://jane.dallaway.com/database-change-management-dbverse-review">earlier</a>, we started specifiying how our tool would work, especially with regards to transaction. Our outer process will need to handle its own transactions, and will need to be aware of inner errors and inner transactions to ensure that errors don't ensue.<p />For example, in the following scenario<br /><span class="code">BEGIN TRAN<br />INSERT INTO COUNT VALUES (1)<br /> BEGIN TRAN<br /> INSERT INTO COUNT VALUES (4)<br /> ROLLBACK TRAN<br />COMMIT TRAN</span><br />the following error is produced<br /><span class="code">Msg 3902, Level 16, State 1, Line 17<br />The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.</span><p />This is because the inner <span class="code">ROLLBACK TRAN</span> rollsback ALL transactions, not just the inner transaction. So, by the time the <span class="code">COMMIT TRAN</span> is called there are no longer any transactions.<p />To get around this, the code can be changed to be:<br /><span class="code">BEGIN TRAN<br />INSERT INTO COUNT VALUES (1)<br /> BEGIN TRAN<br /> INSERT INTO COUNT VALUES (4)<br /> ROLLBACK TRAN<p />IF @@TRANCOUNT > 0<br /> COMMIT TRAN</span><br />which works happily and will not cause an error.<p />NOTE: Count is just a test table I created with a single column of Count which is defined as an int. Nothing very exciting.</p>