Author:Kalen Delaney
Link: http://sqlblog.com/blogs/kalen_delaney/archive/2007/08/13/did-you-know-nesting-transactions.aspx
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
