Transactions Locks «Prev  Next»

Lesson 5 Nested transactions
Objective Define and identify nested Transactions.

Navigating the Complex Terrain of Nested Transactions in SQL Server 2019

In the multifaceted world of database administration, managing transactions stands as a pivotal responsibility. Specifically, for a SQL Server Database Administrator working with SQL Server 2019, the intricacies of nested transactions require a profound understanding and meticulous handling. This narrative aims to unfold the layers of nested transactions, guiding you through the process of defining and identifying them within your SQL Server environment.

Understanding the Essence of Nested Transactions

At their core, nested transactions are transactions that reside within other transactions. They represent a layer within a more extensive transactional operation, embodying a subset of actions that contribute to the broader transaction. However, it's crucial to comprehend that SQL Server does not provide true nested transactions. Rather, it gives the semblance of nesting, while in reality, it operates under a flat transaction model.

Defining Nested Transactions: The SQL Server Approach

In SQL Server 2019, when you initiate a transaction within another transaction, the system acknowledges the initiation of a nested transaction. However, it does not create a new transaction separate from the original. Instead, it increases the @@TRANCOUNT value, which represents the number of open transactions. 

BEGIN TRAN -- Start of the outer transaction
    PRINT 'Outer Transaction Start'
    @@TRANCOUNT  -- Displays 1

    BEGIN TRAN -- Start of the nested transaction
        PRINT 'Nested Transaction Start'
        @@TRANCOUNT -- Displays 2

    COMMIT TRAN -- Committing the nested transaction does not end the transaction
    @@TRANCOUNT -- Displays 1
COMMIT TRAN -- This commits the outer transaction, effectively ending the transaction
@@TRANCOUNT -- Displays 0

Identifying Nested Transactions: Reading Between the Lines

Identifying nested transactions necessitates a keen eye on the @@TRANCOUNT value. Each `BEGIN TRAN` increases the @@TRANCOUNT by 1, regardless of whether it's a standalone transaction or nested within another. Thus, a @@TRANCOUNT value greater than 1 signals the presence of nested transactions.

Navigating the Commit and Rollback Quirks

In the realm of SQL Server’s nested transactions, the `COMMIT` and `ROLLBACK` commands behave in ways that may initially seem counterintuitive. Committing a nested transaction does not truly commit the changes to the database; it merely decreases the @@TRANCOUNT by 1. Only when the outermost transaction is committed do the changes become permanent.
Conversely, issuing a `ROLLBACK` without specifying a savepoint or transaction name will completely roll back all transactions, setting the @@TRANCOUNT back to 0, and discarding all changes made during the transactions.

Harnessing Savepoints for Granular Control

For those situations demanding more granular control over the rollback of nested transactions, savepoints are your allies. Savepoints allow you to set markers within your transactions, to which you can roll back without affecting the entire transaction. 

BEGIN TRAN
    SAVE TRAN SavePointName
    -- SQL Commands
    ROLLBACK TRAN SavePointName -- Rolls back to the savepoint
COMMIT TRAN

In SQL Server 2019, nested transactions present a unique challenge, demanding a clear understanding and a meticulous approach. While SQL Server's model of nested transactions might be a departure from the true nested transaction paradigm, with the right knowledge and tools at hand, you can navigate this terrain with confidence and precision. Armed with this insight, you are now equipped to define, identify, and manage nested transactions, ensuring the integrity and efficiency of your database operations.

What is a nested Transaction?

Simply put, a nested transaction is a transaction within a transaction.
A nested transaction can only be used with explicit transactions, not with implicit or auto-commit transactions.

Why are Nested Transactions necessary?

Support for nested transactions is necessary because you could unknowingly call a procedure that begins and ends a transaction. For example, suppose you call a stored procedure, called sp_UpdateCustomer, written by a co-worker.
Question: Does this procedure contain a transaction? Unless you explicitly look at the stored procedure, you will not know. If your call to sp_UpdateCustomer is itself within a transaction, you will unintentionally find yourself in a nested transaction situation.
As an example, suppose you have these stored procedures:

Stored Procedure Name Transaction Within Stored Procedure
SP1 Begin, End
SP2 Begin, End

Looking at the table above, suppose that SP1 calls SP2. SP2 begins and ends the transaction without even knowing that it was called by SP1, which also contains a begin and end transaction. Therefore, as far as the database is concerned, the events are executed in this order:
  1. SP1:BEGIN Transaction
  2. SP2:BEGIN Transaction
  3. SP2:COMMIT Transaction
  4. SP1:COMMIT Transaction

As you can see, nested transactions can occur even when they are not planned.

Committed Transactions

It is important to know that even though you might have nested transactions, the innermost transactions (#2 and #3 above) have no effect if they are committed unless the outermost transaction is also committed. In other words, if SP2 above is committed, then the entire transaction is rolled back if SP1 is rolled back. The next lesson introduces you to locking.

Transaction Isolation - Quiz

Before moving on to the next module, click the Quiz link below to check your knowledge of the material covered in this module with a short, multiple-choice quiz.
Transaction Isolation - Quiz