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 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.

Clarabelle

Answer 1

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.

Monospermous

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. – Clarabelle

So are nested transactions useful only when calling external transactional code? – Clarabelle

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) where they may nest "naturally". I never used explicit nested transactions either. – Monospermous

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). – Clarabelle

Also see rusanu.com/2009/06/11/… – Conveyor

Does a stored procedure in MSSQL start a transaction automatically? I mean is the whole stored procedure transactional itself by default? – Clarabelle

@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 on that). – Monospermous

@Monospermous - Thanks good to know, because I was using mainly PostgreSQL and there the stored procedure executes in a transaction. – Clarabelle

Answer 2

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.

Inexertion