THE SQL Server Blog Spot on the Web

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

Alexander Kuznetsov

Avoiding nested transactions might not improve performance.

Beginning a transaction only when @@TRANCOUNT=0 might not improve performance at all. At least, I did not notice any difference whatsoever. No matter if I use this pattern:

BEGIN TRAN ;
-- (snip)
COMMIT ;

or a more complex one:

DECLARE @trancount INT ;
SET @trancount = @@TRANCOUNT ;
IF 
@trancount = 0 BEGIN ;
  
BEGIN TRAN ;
END ;
--(snip)
IF 
@trancount = 0 BEGIN ;
  
COMMIT ;
END ;
 

the performance stays the same: I was not able to notice any difference. Here are my benchmarks.

Prerequisites

All we need is the following two tables:

CREATE TABLE dbo.Toggle1
    
(
      
id INT NOT NULL
            
PRIMARY KEY ,
      
i INT NOT NULL
    ) ;
GO
INSERT  INTO dbo.Toggle1
        
( id, i )
VALUES  ( 1, 0 ) ;
GO
CREATE TABLE dbo.Toggle2
    
(
      
id INT NOT NULL
            
PRIMARY KEY ,
      
i INT NOT NULL
    ) ;
GO
INSERT  INTO dbo.Toggle2
        
( id, i )
VALUES  ( 1, 0 ) ;
 

Simple benchmarking

First, let us run a very simple case, without nested stored procedure calls:

CREATE PROCEDURE dbo.ToggleWithNestedTrans
  
@NumIterations INT
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
SET XACT_ABORT ON ;
        
DECLARE @i INT ;
        
SET @i = 0 ;
        
WHILE @i < @NumIterations
            
BEGIN ;
                
BEGIN TRAN ;
                
UPDATE  dbo.Toggle1
                
SET     i = 1 - i
                
WHERE   id = 1 ;
                
BEGIN TRAN ;
                
UPDATE  dbo.Toggle2
                
SET     i = 1 - i
                
WHERE   id = 1 ;
                
COMMIT ;
                
COMMIT ;
                
SET @i = @i + 1 ;
            
END ;
    
END ;
GO

CREATE PROCEDURE dbo.ToggleAvoidingNestedTrans
  
@NumIterations INT
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
SET XACT_ABORT ON ;
        
DECLARE @i INT ;
        
SET @i = 0 ;
        
WHILE @i < @NumIterations
            
BEGIN ;
                
BEGIN TRAN ;
                
UPDATE  dbo.Toggle1
                
SET     i = 1 - i
                
WHERE   id = 1 ;
                
DECLARE @trancount INT ;
                
SET @trancount = @@TRANCOUNT ;
                
IF 
@trancount = 0 BEGIN ;
                  
BEGIN TRAN ;
              
END ;
                
UPDATE  dbo.Toggle2
                
SET     i = 1 - i
                
WHERE   id = 1 ;
                
IF
@trancount = 0 BEGIN ;
                  
COMMIT ;
              
END ;
                
COMMIT ;
                
SET @i = @i + 1 ;
            
END ;
    
END ;
GO

CREATE PROCEDURE dbo.ToggleNoNestedTrans
  
@NumIterations INT
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
SET XACT_ABORT ON ;
        
DECLARE @i INT ;
        
SET @i = 0 ;
        
WHILE @i < @NumIterations
            
BEGIN ;
                
BEGIN TRAN ;
                
UPDATE  dbo.Toggle1
                  
SET     i = 1 - i
                  
WHERE   id = 1 ;
                
UPDATE  dbo.Toggle2
                  
SET     i = 1 - i
                  
WHERE   id = 1 ;
                
COMMIT ;
                
SET @i = @i + 1 ;
            
END ;
    
END ;
GO

EXEC dbo.ToggleWithNestedTrans @NumIterations = 100000;
GO
EXEC dbo.ToggleAvoidingNestedTrans @NumIterations = 100000;
GO

EXEC dbo.ToggleWithNestedTrans @NumIterations = 100000;
GO
EXEC dbo.ToggleAvoidingNestedTrans @NumIterations = 100000;
GO

EXEC dbo.ToggleWithNestedTrans @NumIterations = 100000;
GO
EXEC dbo.ToggleAvoidingNestedTrans @NumIterations = 100000;

GO

EXEC dbo.ToggleNoNestedTrans @NumIterations = 100000;
GO
EXEC dbo.ToggleNoNestedTrans @NumIterations = 100000;
GO
EXEC dbo.ToggleNoNestedTrans @NumIterations = 100000;
GO
 
 

In fact, real execution costs are the same for both approaches, and not different from then run without nested transactions at all.

Benchmarking with nested stored procedure calls

Let us benchmark another, possibly more realistic scenario, when the avoiding nested transaction logic is inside another, nested stored procedure. Here is the benchmarking script:

CREATE PROCEDURE dbo.Toggle2WithNestedTran
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
SET XACT_ABORT ON ;
        
BEGIN TRAN ;
        
UPDATE  dbo.Toggle2
        
SET     i = 1 - i
        
WHERE   id = 1 ;
        
COMMIT ;
    
END ;
GO

ALTER PROCEDURE dbo.ToggleWithNestedTrans
  
@NumIterations INT
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
SET XACT_ABORT ON ;
        
DECLARE @i INT ;
        
SET @i = 0 ;
        
WHILE @i < @NumIterations
            
BEGIN ;
                
BEGIN TRAN ;
                
UPDATE  dbo.Toggle1
                
SET     i = 1 - i
                
WHERE   id = 1 ;
                
EXEC dbo.Toggle2WithNestedTran ;
                
COMMIT ;
                
SET @i = @i + 1 ;
            
END ;
    
END ;
GO

CREATE PROCEDURE dbo.Toggle2AvoidingNestedTrans
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
SET XACT_ABORT ON ;
        
DECLARE @trancount INT ;
        
SET @trancount = @@TRANCOUNT ;
        
IF
@trancount = 0 BEGIN ;
          
BEGIN TRAN ;
      
END ;
        
UPDATE  dbo.Toggle2
        
SET     i = 1 - i
        
WHERE   id = 1 ;
        
IF 
@trancount = 0 BEGIN ;
          
COMMIT ;
      
END ;
    
END ;
GO

ALTER PROCEDURE dbo.ToggleAvoidingNestedTrans
  
@NumIterations INT
AS
    BEGIN
;
        
SET NOCOUNT ON ;
        
SET XACT_ABORT ON ;
        
DECLARE @i INT ;
        
SET @i = 0 ;
        
WHILE @i < @NumIterations
            
BEGIN ;
                
BEGIN TRAN ;
                
UPDATE  dbo.Toggle1
                
SET     i = 1 - i
                
WHERE   id = 1 ;
                
EXEC dbo.Toggle2AvoidingNestedTrans ;
                
COMMIT ;
                
SET @i = @i + 1 ;
            
END ;
    
END ;
GO

Again, I was not able to notice any difference.

Conclusion

Apparently the following logic does not affect performance at all:

DECLARE @trancount INT ;
SET @trancount = @@TRANCOUNT ;
IF @TRANCOUNT = 0 BEGIN ;
  
BEGIN TRAN ;
END ;
--(snip)
IF @TRANCOUNT = 0 BEGIN ;
  
COMMIT ;
END ;

As such, this pattern seems to be just an unnecessary complication.

What do you think - are you getting the same results when you benchmark on your system?

Are you using this pattern for other than performance? What are those?

Published Wednesday, February 08, 2012 1:09 PM by Alexander Kuznetsov

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

 

Shawn aka gbn said:

Hello again :-)

It prevents error 266 (mostly, except say a trigger rollback) when @@trancount doesn't match on entry and exit to a Stored procedure

SET XACT_ABORT suppress error 266 because it forces a rollback anyway.

Thirdly, there is no such thing as a nested transaction says Paul Randal

I've updated my SO answer here: http://stackoverflow.com/a/2074139/27535

Cheers

February 8, 2012 1:42 PM
 

phil Factor said:

SQL Server tolerates nesting in transactions but nested transactions don't behave like anything else that is nested. To get the sort of rollback behavior you might expect, then use savepoints. There is no way of committing a nested transaction before the root transaction is committed. Otherwise the root transaction would be only partially committed, which is a relational nightmare. Surely, you've just proved that, performance-wise as in every other way, nested transactions are tolerated but ignored.

February 8, 2012 2:11 PM
 

Alexander Kuznetsov said:

Hi Shawn,

I just left a comment to your answer. In this post, I was mostly interested in the performance price we are paying for our design choices, not in the reasons we are making those choices.

February 8, 2012 2:13 PM
 

Gail said:

I'm curious as to why you have

IF @@TRANCOUNT = 0 BEGIN ;

          COMMIT ;

      END

If @@TranCount is 0, then there are no open transactions and hence there's nothing to commit.

If I try running that when @@TranCount really is 0, I get an error

Msg 3902, Level 16, State 1, Line 2

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Shouldn't that be IF @@TranCount>0 COMMIT? Or IF @SomeVariableToIndicateANewTransactionWasStarted = 1 Commit?

p.s. Since all that a nested BEGIN TRAN does is bump @@TranCount (it doesn't even get logged), I wouldn't expect it to have any effect on performance.

February 8, 2012 2:20 PM
 

Alexander Kuznetsov said:

Gail,

Good catch! I fixed the typo. I did mean this:

IF @trancount = 0 BEGIN ;

  BEGIN TRAN ;

END ;

--(snip)

IF @trancount = 0 BEGIN ;

  COMMIT ;

END ;

February 8, 2012 2:27 PM
 

Alexander Kuznetsov said:

Phil Factor,

Savepoints do not really work: if the transaction is doomed, we cannot rollback to a savepoint. Details here:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/11/15/avoid-mixing-old-and-new-styles-of-error-handling.aspx

February 8, 2012 2:29 PM
 

Julian Kuiters said:

HI Alexander

Might want to update the code in your conclusion to match your heading - it still has the incorrect tran count check (@@TRANCOUNT)

Julian

February 10, 2012 6:09 AM
 

Alexander Kuznetsov said:

Hi Julian.

I fixed the typo. Thanks!

February 10, 2012 8:48 AM
 

TheSQLGuru said:

I wonder if the testing methodology isn't flawed.  Your onesy transactions are probably getting overwhelmed by the log buffer flushes.  Try a combination of these doing 100 or 1000 rows at a time between commits and see how the benchmarking compares.

February 20, 2012 10:25 AM
 

Alexander Kuznetsov said:

Guru,

I am testing exactly what I want: a real life scenario from developer's perspective. To ensure atomicity, we wrap related modifications, just a few rows in two tables, in a transaction, and we want to know if we pay any performance penalty.

February 20, 2012 11:59 AM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

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