THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Did you Know? Nesting Transactions

 

Transact-SQL provides three different methods of transaction control: autocommit transactions, explicit transactions and implicit transactions.

An autocommit transaction is any individual INSERT, UPDATE or DELETE operation, no matter how many rows are affected. As soon as the statement is completed, it is committed, which means that all the corresponding log records, include a COMMIT TRAN log record, are written to the transaction log on disk (your .ldf file).

If you want to be able to combine multiple statements into a single transaction, or if you want to be able to conditionally rollback the transaction, you can use explicit transaction control, with the BEGIN TRANSACTION (or BEGIN TRAN) and COMMIT TRANSACTION (or COMMIT TRAN) control statements. For example, to make sure both T1 and T2 are updated, or neither one is updated, you can use the following pseudo-code:

BEGIN TRAN
    UPDATE T1 ...
    UPDATE T2 ...
COMMIT TRAN   

If there is a system failure after T1 is updated, the transaction will not be committed. It will only be committed if both updates can succeed.

Also, by using explicit transactions, you can decide to rollback a transaction, after checking for an error condition or for the number of rows affected:

BEGIN TRAN
    UPDATE T1 ...
    IF @@error > 0 ROLLBACK TRAN
        ELSE COMMIT TRAN   

The third type of transaction control, implicit transactions, is not considered a default behavior and is part of the product only to support compatibility with other database products. I'll say a couple of things about it shortly.

Here is a little quiz I frequently give my students. Suppose I have the following batch:

BEGIN TRAN
UPDATE T1
BEGIN TRAN
UPDATE T2
BEGIN TRAN
UPDATE T3
COMMIT TRAN

The quiz question is: What gets committed?

I usually get a variety of answers including: All the updates, none of the updates, the update to T1, the update to T3. (Nobody suggests the update to T2.)

It turns out the answer is: None of the above.

SQL Server keeps an internal counter of how many times BEGIN TRAN has been executed, and you need to execute the same number of COMMIT TRANs to get the real commit to take place. Each BEGIN TRAN increments this internal counter, and each COMMIT TRAN decrements it. Only when the counter gets to 0, will the log records be written out to disk and the transaction will be truly committed. You can look at the value of this counter with the function @@trancount. You can use this function to also see that when you do a ROLLBACK, the counter is immediately set all the way back to 0.

BEGIN TRAN
SELECT @@trancount
BEGIN TRAN
SELECT @@trancount
BEGIN TRAN
SELECT @@trancount

ROLLBACK TRAN
SELECT @@trancount

So why would you want to have nested transactions, if it really doesn't give you any advantage?  The answer to that will have to wait for a future post. This post is about the relationship of nesting transactions to the third type of transaction control: implicit transactions.

The name 'implicit transactions' is a bit of a misnomer. It is only the BEGIN TRAN that is implicit; the COMMIT TRAN must always be explicit. This means that no transaction will be committed until a COMMIT TRAN is issued. Transactions will begin any time an INSERT, UPDATE, DELETE or SELECT is executed (as well as a few other statements, such as CREATE and DROP, that you can read about in the Books Online) as long as there is not already an open transaction.

As mentioned, implicit transaction mode is not SQL Server's default. You have to request implicit transaction mode either with a SET statement:

SET IMPLICIT_TRANSACTIONS ON;

or by setting the user_options configuration option to have the 2-bit set to 1. You can read about sp_configure 'user options' in the Books Online. Although I don't recommend using implicit transactions, if you're going to use them, I recommend having all sessions use them, but setting the configuration option. To have some sessions using implicit transactions and requiring a COMMIT for every individual INSERT, UPDATE and DELETE and to have other sessions not requiring that closure, seems to be asking for trouble.

So what happens if you use a BEGIN TRAN when you are in implicit transaction mode? I used to think that the BEGIN TRAN would just be ignored, but it turns out I was wrong. Think about what would happen if you had a normal, implicit mode transaction like this:

UPDATE T1
    SET ...
COMMIT TRAN

and then suppose someone executes this batch from within an explicit transaction, and put the BEGIN/COMMIT around it:

BEGIN TRAN
UPDATE T1
    SET ...
COMMIT TRAN
COMMIT TRAN

If the BEGIN TRAN was ignored, the first COMMIT would would set @@trancount to 0 and the second would give an error. You can see this behavior by just executing COMMIT TRAN all by itself. SQL Server generates this message:

Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

So if you are in implicit transaction mode, issuing a BEGIN TRAN (which you really shouldn't do) sets @@trancount to 2. This really surprised me when I first noticed it, but then I realized the point of it. When you perform any DML operation, since you are already in a transaction, @@trancount will not be incremented. When you issue the COMMIT for your DML, @@trancount will be decremented to 1. When you issue the COMMIT TRAN to match the BEGIN TRAN, @@trancount will decrement to 0 and the transaction will really be committed.  Here's a full script to illustrate the behavior of @@trancount with implicit transactions:

SET IMPLICIT_TRANSACTIONS OFF;
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE name = 'T1' AND type = 'U')
DROP TABLE T1;
GO
CREATE TABLE T1 (col1 int);
GO
INSERT INTO T1 SELECT 1;
GO

SET IMPLICIT_TRANSACTIONS ON;
GO
BEGIN TRAN;
SELECT @@trancount;
UPDATE T1
SET col1 = col1 + 1;
COMMIT TRAN;
SELECT @@trancount;
COMMIT TRAN;
SELECT @@trancount;
GO
SET IMPLICIT_TRANSACTIONS OFF;
GO

Have fun!

~Kalen

Published Monday, August 13, 2007 4:20 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

RichB said:

Great article.  I will pass it on to some developers I know who just love implicit transactions... :s

Rich

August 14, 2007 7:28 AM
 

Luciano Evaristo Guerche said:

Valuable post. I sometimes also use named transactions ans save points.

My two cents to this post:

SELECT 'Before BEGIN TRANSACTION t1', @@TRANCOUNT

BEGIN TRANSACTION t1

SELECT 'After BEGIN TRANSACTION t1', @@TRANCOUNT

SELECT 'Before SAVE TRANSACTION s1', @@TRANCOUNT

SAVE TRANSACTION s1

SELECT 'After SAVE TRANSACTION s1', @@TRANCOUNT

SELECT 'Before BEGIN TRANSACTION t2', @@TRANCOUNT

BEGIN TRANSACTION t2

SELECT 'After BEGIN TRANSACTION t2', @@TRANCOUNT

SELECT 'Before COMMIT TRANSACTION t2', @@TRANCOUNT

COMMIT TRANSACTION t2

SELECT 'After COMMIT TRANSACTION t2', @@TRANCOUNT

SELECT 'Before ROLLBACK TRANSACTION s1', @@TRANCOUNT

ROLLBACK TRANSACTION s1

SELECT 'After ROLLBACK TRANSACTION s1', @@TRANCOUNT

SELECT 'Before COMMIT TRANSACTION t1', @@TRANCOUNT

COMMIT TRANSACTION t1

SELECT 'After COMMIT TRANSACTION t1', @@TRANCOUNT

--Try changing the SAVE TRANSACTION TO BEGIN TRANSACTION and see what happens when ROLLBACK TRANSACTION s1 is run

/*

 Luciano Evaristo Guerche (Gorše)

 Taboão da Serra, SP, Brazil

*/

August 14, 2007 10:24 AM
 

Bill said:

Why none of the above?

Is it because the 1st BEGIN TRAN has not been committed?

BEGIN TRAN -- 1st

UPDATE T1

BEGIN TRAN -- 2nd

UPDATE T2

BEGIN TRAN -- 3rd

UPDATE T3

COMMIT TRAN --commit 3rd

It turns out the answer is: None of the above.

September 10, 2007 4:43 AM
 

Eric Hutchinson said:

While "None of the above" may be the correct answer, "None of the updates" is also correct, at least until there are two more commits.

December 6, 2007 1:03 PM
 

Kalen Delaney said:

I've said many times that my favorite new feature in SQL Server 2005 is the new metadata, in particular

June 29, 2008 11:50 PM
 

Kerry Sainsbury said:

"I've said many times that my favorite new feature in SQL Server 2005 is the new metadata, in particular"

You weren't kidding, were you?

March 5, 2009 9:00 PM
 

ramu said:

very helpful

June 25, 2009 10:25 AM
 

Graeme Martin said:

KALEN! Why, then, if I "stop execution" during the following, do ALL the TRANSACTIONs ROLLBACK?

WHILE 1=1

BEGIN

--Wrap DELETEs in a TRANSACTION to avoid stacking up resources until the end.

BEGIN TRANSACTION;

--DELETE in chunks, to avoid locking up the table.

DELETE TOP (@NRows)

FROM

TransactionHistory

WHERE

TransactionDate <= @cutOffDate

;

SELECT @Rows = @@ROWCOUNT;

COMMIT TRANSACTION;

--Take status of how many rows are deleted so far.

SELECT @RowsComplete = @RowsComplete + @Rows;

IF @RowsComplete % 1000 = 0

BEGIN

SELECT @Statement = 'DELETEd ' + CAST( @RowsComplete AS VARCHAR(32)) + ' of ' + CAST( @TotalRows AS VARCHAR(32)) + ' Rows.';

RAISERROR( @Statement, 0, 1) WITH NOWAIT;

END

--We can exit the loop, if there is nothing else to DELETE.

IF @Rows = 0 BREAK;

--Allow other TRANSACTIONs to complete.

WAITFOR DELAY '00:00:05';

END

September 12, 2012 1:34 PM
 

Graeme Martin said:

*I should mention, I stopped execution and restarted several times--it was a sandbox.  Then I disconnected without ROLLBACK. :-)

September 12, 2012 1:35 PM
 

Graeme Martin said:

There is an implied "outer" transaction that rolls back everything.

September 13, 2012 1:03 PM
 

SQL Transactions | The SQL DBA said:

May 12, 2014 1:38 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement