Transactions Locks «Prev  Next»

Lesson 5 Nested transactions
Objective Define and identify nested Transactions.

SQL-Server Nested Transactions

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