Binaryworld - How to implement nested transactions ... [ SQL Server -> Locks-Transactions ]

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. Transact-SQL allows you to nest transaction operations by issuing nested BEGIN TRAN commands. The @@TRANCOUNT automatic variable can be queried to determine the level of nesting - 0 indicates no nesting , 1 indicates nesting one level deep, and so fourth.

A COMMIT issued against any transaction except the outermost one doesn't commit any changes to disk - it merely decrements the@@TRANCOUNT automatic variable. A ROLLBACK, on the other hand, works regardless of the level at which it is issued, but rolls back all transactions, regardless of the nesting level. Though this is counterintuitive, there's a very good reason for it. If a nested COMMIT actually wrote changes permanently to disk, an outer ROLLBACK wouldn't be able to reverse those changes since they would already be recorded permanently.

When you explicitly begin a transaction, the @@TRANCOUNT automatic variable count increases from 0 to 1; when you COMMIT, the count decreases by one; when you ROLLBACK, the count is reduced to 0. As you see, the behavior of COMMIT and ROLLBACK is not symmetric. If you nest transactions, COMMIT always decreases the nesting level by 1, as you can see illustrated in Figure 1. The ROLLBACK command, on the other hand, rolls back the entire transaction, illustrated in Figure 2. This asymmetry between COMMIT and ROLLBACK is the key to handling errors in nested transactions.

As you can see from Figure 1 and Figure 2, you can nest transactions and use the @@TRANCOUNT automatic variable to detect the level. You also learned that COMMIT and ROLLBACK do not behave symmetrically; COMMIT just decreases the value of @@TRANCOUNT, while ROLLBACK resets it to 0. The implication is that a transaction is never fully committed until the last COMMIT is issued. No matter how deeply you nest a set of transactions, only the last COMMIT has any effect.

Here is an example of a nested transaction :

Click here to copy the following block

USE pubs
SELECT 'Before BEGIN TRAN', @@TRANCOUNT -- The value of @@TRANCOUNT is 0
BEGIN TRAN
  SELECT 'After BEGIN TRAN', @@TRANCOUNT -- The value of @@TRANCOUNT is 1
  DELETE sales
  BEGIN TRAN nested
    SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
          -- The value of @@TRANCOUNT is 2
    DELETE titleauthor
  COMMIT TRAN nested
          -- Does nothing except decrement the value of @@TRANCOUNT

  SELECT 'After COMMIT TRAN nested', @@TRANCOUNT
          -- The value of @@TRANCOUNT is 1
ROLLBACK TRAN

SELECT 'After ROLLBACK TRAN', @@TRANCOUNT -- The value of @@TRANCOUNT is 0
-- because ROLLBACK TRAN always rolls back all transactions and sets
-- @@TRANCOUNT to 0.

SELECT TOP 5 au_id FROM titleauthor

In this example we see that despite the nested COMMIT TRAN, the outer ROLLBACK still reverses the effects of the DELETE titleauthor command.

Here is another similar example of nested transaction :

Click here to copy the following block

USE pubs
SELECT 'Before BEGIN TRAN', @@TRANCOUNT -- The value of @@TRANCOUNT is 0
BEGIN TRAN
  SELECT 'After BEGIN TRAN', @@TRANCOUNT -- The value of @@TRANCOUNT is 1
  DELETE sales
  BEGIN TRAN nested
    SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
        -- The value of @@TRANCOUNT is 2
    DELETE titleauthor
  ROLLBACK TRAN

  SELECT 'After COMMIT TRAN nested', @@TRANCOUNT
  -- The value of @@TRANCOUNT is 0 because
  -- ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT
  -- to 0.

IF (@@TRANCOUNT > 0) BEGIN
  COMMIT TRAN -- Never makes it here cause of the ROLLBACK
  SELECT 'After COMMIT TRAN', @@TRANCOUNT
END

SELECT TOP 5 au_id FROM titleauthor

In this example, execution never reaches the out COMMIT TRAN because the ROLLBACK TRAN reverses all transactions currently in progress and sets @@TRANCOUNT to 0. Unless ROLLBACK TRAN is called with a save point, ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT to 0, regardless of the context in which it's called.