Purpose of nested transactions

Question

I have never understood what a nested transaction is good for. Committing a nested transaction commits nothing - it just decreases @@TRANCOUNT. And ROLLBACK rollbacks 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.

score 17 · Accepted Answer · answered Jun 09 '12 at 11:45

17

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.

answered Jun 09 '12 at 11:45

Lucero

59,176
9
122
152

1

I can use safepoints without using nested transactions. The stored procedure is a good point(I have never thought of that). But if I don't use a stored procedure in my transaction(or don't call other code), why should I need nested transactions? I constantly see examples with nested transactions and I don't see the point of them. – Petar Minchev Jun 09 '12 at 11:52
So are nested transactions useful only when calling external transactional code? – Petar Minchev Jun 09 '12 at 11:56
1

I agree with you and I think that they are useful for calling external transactional code. The technical ability of SQL Server to handle nested transactions is important and not available in all DB systems. There are some cases (such as with the mentioned SPs - where you should also use [`SET XACT_ABORT ON`](http://stackoverflow.com/questions/1150032/what-is-the-benefit-of-using-set-xact-abort-on-in-a-stored-procedure)) where they may nest "naturally". I never used explicit nested transactions either. – Lucero Jun 09 '12 at 11:57
Thanks a bunch! I was so confused, because I have a book and it gives examples for nested transactions, explains the `@@TRANCOUNT` and stuff like that. But it doesn't say what are they good for(btw they weren't using any external transactional code). – Petar Minchev Jun 09 '12 at 12:01
2

Also see http://rusanu.com/2009/06/11/exception-handling-and-nested-transactions/ – Aaron Bertrand Jun 09 '12 at 12:04
Does a stored procedure in MSSQL start a transaction automatically? I mean is the whole stored procedure transactional itself by default? – Petar Minchev Jun 09 '12 at 12:10
@PetarMinchev, no it doesn't. And when you then do use an explicit transaction, you need the `XACT_ABORT` stuff to ensure expected behavior (see also [Dan's post](http://weblogs.sqlteam.com/dang/archive/2007/10/20/Use-Caution-with-Explicit-Transactions-in-Stored-Procedures.aspx) on that). – Lucero Jun 09 '12 at 12:11
@Lucero - Thanks good to know, because I was using mainly PostgreSQL and there the stored procedure executes in a transaction. – Petar Minchev Jun 09 '12 at 12:13

score 1 · Answer 2 · answered May 15 '15 at 06:24

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.

Purpose of nested transactions

2 Answers2

Linked