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 ;

score 1 · Answer 1 · answered Jun 04 '19 at 05:50

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.

Here is another excellent article on the myth of nested transactions. https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/ — Sean Lange, Jun 04 '19 at 13:11
Nice one @Sean, actually *your* link is the one I was trying to find, but hadn't in my fav list! — George Menoutis, Jun 04 '19 at 13:12

score 0 · Answer 2 · answered Jun 04 '19 at 02:55

0

Yes transaction can be nested. But once your outer transaction fails, your inner transaction will be rollback too.

Nested transactions in Sql Server

answered Jun 04 '19 at 02:55

Jervs

344
1
12

Nested transactions are a myth. https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/ – Sean Lange Jun 04 '19 at 13:10

score -2 · Answer 3 · answered Jun 04 '19 at 02:43

-2

So far as I am aware, transactions cannot be nested.

answered Jun 04 '19 at 02:43

Mike Robinson

8,490
5
28
41

Does SQL Server support nested transactions?

3 Answers3