Does SQL Server support nested transactions?

Question

I wondered if SQL Server supports nested transactions?

I can see other questions similar.

Also - How do I know if I am in a nested transaction?

This may seem a strange question .. but .. in SSMS I might have run the command BEGIN TRANSACTION more than once by accident .. for example .. Will this mean that I need to run the COMMIT TRANSACTION more than once to finalise the transaction?

I'm thinking nested transactions should generally be avoided.

SELECT
    [Initial Assess -  Sweating], 
    COUNT(*) 
FROM
    Clinical.SAASCaseCards 
GROUP BY
    [Initial Assess -  Sweating]

BEGIN TRANSACTION;

UPDATE Clinical.SAASCaseCards 
SET [Initial Assess -  Sweating] = '1' 
WHERE [Initial Assess -  Sweating]= '01'

COMMIT TRANSACTION ;

George Menoutis George Menoutis · Accepted Answer · 2019-06-04T05:50:13

You can use the global variable @@TRANCOUNT to see how this works.

If you run select @@TRANCOUNT and you have no transactions around you will get 0.

Each time begin transaction is run, @@TRANCOUNT increases by 1. Each time commit (transaction) is run, @@TRANCOUNT decreases by 1. If that made @@TRANCOUNT to be set to 0, then the transaction(s) are indeed committed.

On the other hand, a rollback (transaction) will set @@TRANCOUNT to 0 and roll back all changes regardless of the value of @@TRANCOUNT at that time.

So, there is no functional nesting. There is only this counter in order to allow different modules to make their own transaction handling.

I recommend Erland Sommerskog's article for further reading.

Does SQL Server support nested transactions?

3 Answers