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.
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
As an example, suppose you have these stored procedures:
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.
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