Scripts and Transactions in Transact-SQL - Nested Transactions

(Page 5 of 5 )

SQL Server allows you to nest transactions. Basically, this feature means that a new transaction can start even though the previous one is not complete:

Begin transaction
...
    Begin transaction
    ...
    Commit transaction
...
Commit transaction

Usually this situation occurs when one stored procedure containing a transaction calls another stored procedure that also contains a transaction. In the following example, ap_Order_Complete_1 completes an order by setting its completion date and changing the status of the order, and then looping through associated order items and calling ap_OrderItem_Complete_1 to complete each of them. ap_OrderItem_Complete_1 sets the completion date of an order item to the last ChargeLog date associated with that OrderItem. Both of these procedures contain a transaction.

Create Procedure ap_Order_Complete_1
-- Complete all orderItems and then complete order
    @intOrderId int,
    @dtsCompletionDate smalldatetime
As
set nocount on

Declare @intErrorCode int,
        @i int,
        @intCountOrderItems int,
        @intOrderItemId int
Select @intErrorCode = @@Error

If @intErrorCode = 0
    Begin Transaction

-- complete order
If @intErrorCode = 0
Begin
     Update dbo.[Order]
     Set CompletionDate = @dtsCompletionDate,
         OrderStatusId = 4 -- completed
     Where OrderId = @intOrderId

     Select @intErrorCode = @@Error
End

-- loop through OrderItems and complete them
If @intErrorCode = 0
Begin
     Create Table #OrderItems(
          id int identity(1,1),
          OrderItemId int)

     Select @intErrorCode = @@Error
End

-- collect orderItemIds
If @intErrorCode = 0
Begin
     Insert Into #OrderItems(OrderItemId)
          Select ItemId
          From dbo.OrderItem
          Where OrderId = @intOrderId  
          Select @intErrorCode = @@Error End

If @intErrorCode = 0
Begin
     Select @intCountOrderItems = Max(Id),
            @i = 1
     From #OrderItems

     Select @intErrorCode = @@Error
End

while @intErrorCode = 0 and @i <= @intCountOrderItems
Begin
     If @intErrorCode = 0
     Begin
          Select @intOrderItemId = OrderItemId
          From #OrderItems
          Where id = @i
          Select @intErrorCode = @@Error  
     End

     If @intErrorCode = 0
          Exec @intErrorCode = dbo.ap_OrderItem_Complete_1 @intOrderItemId

    If @intErrorCode = 0
          Set @i = @i + 1
End

If @intErrorCode = 0 and @@trancount > 0
      Commit Transaction
Else
      Rollback Transaction
return @intErrorCode
Go

create Procedure dbo.ap_OrderItem_Complete_1
-- Set CompletionDate of OrderItem to date -- of last ChargeLog record associated with OrderItem.
     @intOrderItemId int
As
set nocount on
Declare @intErrorCode int
Select @intErrorCode = @@Error

If @intErrorCode = 0
     Begin Transaction

-- Set CompletionDate of OrderItem to date -- of last ChargeLog record associated with OrderItem.
If @intErrorCode = 0
Begin
     update dbo.OrderItem
     Set CompletionDate = (Select Max(ChargeDate)
                           from dbo.ChargeLog
                           where ItemId = @intOrderItemId)
    Where ItemId = @intOrderItemId

    Select @intErrorCode = @@Error
End

If @intErrorCode = 0
Begin
     exec @intErrorCode = dbo.ap_NotifyAccounting @intOrderItemId
End

If @intErrorCode = 0 and @@trancount > 0
     Commit Transaction
Else

      Rollback Transaction
Return @intErrorCode

In the case of nested transactions, no Commit statements except the outer one will save changes to the database. Only after the last transaction is committed will all changes to the database become permanent. Up to that point, it is still possible to roll back all changes.

The interesting question is how SQL Server knows which transaction is the last one. It keeps the number of opened transactions in the @@trancount global variable for each user connection. When SQL Server encounters a Begin Transaction statement, it increments the value of the @@trancount, and when SQL Server encounters a Commit Transaction statement, it decrements the value of the @@trancount. Therefore, the only effect of a nested (internal) Commit Transaction statement is a change to the @@trancount value. Only the outer Commit Transaction statement (when @@trancount = 1) stores changes in data tables rather than in the transaction log.

The following is a purely academic example that does not perform any real processing, but it demonstrates the effect of nested transactions on the @@trancount global variable:

print 'Trancount = ' + Convert(varchar(4), @@trancount)
BEGIN TRANSACTION
    print 'Trancount = ' + Convert(varchar(4), @@trancount)
    BEGIN TRANSACTION
    print 'Trancount = ' + Convert(varchar(4), @@trancount)
    COMMIT TRANSACTION
    print 'Trancount = ' + Convert(varchar(4), @@trancount)
COMMIT TRANSACTION
print 'Trancount = ' + Convert(varchar(4), @@trancount)

Each transactional statement will increment and decrement the @@trancount:

Trancount = 0
Trancount = 1
Trancount = 2
Trancount = 1
Trancount = 0

An interesting inconsistency to observe is in the behavior of the Rollback Transaction statement. No matter how many transaction levels deep execution extends, the Rollback Transaction statement will cancel all changes caused by all transactions (and bring the @@trancount value down to zero). In fact, if you execute an additional Rollback Transaction statement after the first one, SQL Server will report an error.

print 'Trancount = ' + Convert(varchar(4), @@trancount)
BEGIN TRANSACTION
    print 'Trancount = ' + Convert(varchar(4), @@trancount)
    BEGIN TRANSACTION
    print 'Trancount = ' + Convert(varchar(4), @@trancount)
    ROLLBACK TRANSACTION
    print 'Trancount = ' + Convert(varchar(4), @@trancount)
ROLLBACK TRANSACTION
print 'Trancount = ' + Convert(varchar(4), @@trancount)

The following is the result of this example:

Trancount = 0
Trancount = 1
Trancount = 2
Trancount = 0
Server: Msg 3903, Level 16, State 1, Line 8 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Trancount = 0

TIP

---

I have to admit that I had many problems with this issue at one time. Be careful.

To prevent this error, you need to test for the value of the @@trancount variable before you execute the Rollback Transaction statement. A simple way to test for this value works something like this:

if @@trancount > 0
    Rollback Transaction

You will find a much better solution in Chapter 6.

Please check back next week for the conclusion of this article.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: Scripts and Transactions in Transact-SQL

· This article is an excerpt from the book "Microsoft SQL Server 2005 Stored Procedure...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter five of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Buy this book now.