Beginners Guide to Nested SQL Server Transactions

Sometimes it is necessary to have Nested SQL Server Transactions although I can't, off the top of my head, think of a reason or good real-life example at the moment. A nested transaction is where you have an outer and inner transaction. It is possible to do this using the Save transaction command. For our example, create the following table.

set NoCount ON

if (select count(*) from sys.tables where name = 'Students') = 0
    Begin
        Create Table Students (Id int identity(1,1), Name varchar(200), Teacher varchar(200), Grade varchar(1))
    End

truncate table students

    Insert into Students Values ('Joe Friday','Mrs Carter','D')
    Insert into Students Values ('James Bond','Mrs Carter','A')
    Insert into Students Values ('Simon Templare','Mrs Carter','B')
    Insert into Students Values ('Jacques Clouseau','Mrs Carter','C')
    Insert into Students Values ('Eve Moneypenny','Mrs Carter','D')
    Insert into Students Values ('Joanna Dark','Mrs Carter','B')
    Insert into Students Values ('Felix Leiter','Mr Smith','B')
    Insert into Students Values ('Jack Ryan','Mr Smith','B')
    Insert into Students Values ('Derek Flint','Mr Smith','C')
    Insert into Students values ('Luke Skywalker','Mr Lucas','A')
    Insert into Students Values ('Han Solo','Mr Lucas','A')
    Insert into Students Values ('Leia Organa','Mr Lucas','A')
    Insert into Students Values ('Anakin Skywalker','Mr Lucas','D')
    Insert into Students Values ('Sheev Palpatine','Mr Lucas','D')

You'll see some familiar faces there. All names are of their respective owners :).

Begin Transaction First
    Update Students set Teacher = 'Mr Jones' where Teacher = 'Mr Smith'
Save Transaction Second
    Update students set Teacher = 'Mr Pugh' where Teacher = 'Mr Lucas'
    Select distinct Teacher from Students
Rollback Transaction Second
    Select distinct Teacher from Students
Commit Transaction

You don't need to give a transaction name for beginning a transaction, but when doing nested or embedded transactions, it certainly helps. The above example shows that Jones is now Smith, but Mr Lucas has not changed.

If you don't have the rollback transaction, then the commit transaction will commit both transactions, and the result will be both Mr Jones and Mr Pugh as teachers along with Mr Carter. You can experiment with the above SQL to see what happens.

You can change the Save Transaction to Begin Transaction, and the @@RowCount at each stage will increase or decrease. You must be aware, though, if you roll back or commit, it will apply to both. To do nested transactions, you need to use Save Transaction. Try the following example, and you will see that the commit transaction will not work because it has been rolled back.

Begin Transaction First
    Update Students set Teacher = 'Mr Jones' where Teacher = 'Mr Smith'
Begin Transaction Second
    Update students set Teacher = 'Mr Pugh' where Teacher = 'Mr Lucas'
    Select distinct Teacher, @@TRANCOUNT from Students
Rollback Transaction Second
    Select distinct Teacher, @@TRANCOUNT from Students
Commit Transaction

Tags - SQL   Transaction

Last Modified : June 2023