CODEWITHSUNDEEP
sql-serverdatabasetransactionsnested-transactions
Petar Minchev
Petar Minchev

Reputation: 47363

Purpose of nested transactions

I have never understood what a nested transaction is good for. Committing a nested transaction commits nothing - it just decreases @@TRANCOUNT. And ROLLBACK rolls back everything.

BEGIN TRANSACTION
   //do an update
   BEGIN TRANSACTION
     //do an insert
   COMMIT TRANSACTION
COMMIT TRANSACTION

What is the difference with this:

BEGIN TRANSACTION
     //do an update
     //do an insert
COMMIT TRANSACTION

Please give me an example why should nested transactions be used and how they make a difference.

Upvotes: 26

Views: 10779

Answers (2)

M. Rehan Niaz
M. Rehan Niaz

Reputation: 11

IF you are having scenario in which you call one SP which contains an other SP call in it. and that inner SP is also could be called independently from your application. In that case its necessary to place inner transaction(on Inner SP) as well as on parent SP.

Upvotes: 1

Lucero
Lucero

Reputation: 60190

Nested transactions allows your code to call other code (SPs for instance) which uses transactions itself without actually committing your transaction when they commit.

That said, you can use safepoints to roll back inside of a transaction.

There's a CodeProject article dedicated to that.

Upvotes: 19

Related Questions