Commit Nested Transaction

How do I commit a nested transaction while rolling back the outer transaction
BEGIN MASTER TRANSACTION
Action 1
BEGIN NESTED TRANSACTION
Action2
COMMIT NESTED TRANSACTION
ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave action
2 committed.)You can't. If you roll back an outer transaction all the nested ones roll
back as well. If you used a SAVE TRAN you can roll it back but any work
done inside that (even nested trans ) are rolled back as well. If you issue
a plain ROLLBACK (not associated with a nested tran) then everything is
rolled back.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> How do I commit a nested transaction while rolling back the outer
> transaction
> BEGIN MASTER TRANSACTION
> Action 1
> BEGIN NESTED TRANSACTION
> Action2
> COMMIT NESTED TRANSACTION
> ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> action
> 2 committed.)|||What about if one of the action's is a call to another stored procedure. The
action of that call will be rolled back right? Or is just if the action is a
statement in the same procedure?
"Andrew J. Kelly" wrote:
> You can't. If you roll back an outer transaction all the nested ones roll
> back as well. If you used a SAVE TRAN you can roll it back but any work
> done inside that (even nested trans ) are rolled back as well. If you issue
> a plain ROLLBACK (not associated with a nested tran) then everything is
> rolled back.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> > How do I commit a nested transaction while rolling back the outer
> > transaction
> >
> > BEGIN MASTER TRANSACTION
> >
> > Action 1
> >
> > BEGIN NESTED TRANSACTION
> > Action2
> > COMMIT NESTED TRANSACTION
> >
> > ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> > action
> > 2 committed.)
>
>|||On Fri, 2 Sep 2005 13:37:02 -0700, Scott wrote:
>What about if one of the action's is a call to another stored procedure. The
>action of that call will be rolled back right? Or is just if the action is a
>statement in the same procedure?
Hi Scott,
EVERYTHING in a transaction is rolled back on a ROLLBACK. That's the
only way to satsify the "A" in the ACID properties. From Books Online:
>>A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction:
>>Atomicity
>>A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them is performed.
(snip)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Anything you do after a Begin Tran is governed by that. It doesn't matter
if the statements are in called sp's or not.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:BCCFD268-AB8C-4158-8A47-169C4F57C912@.microsoft.com...
> What about if one of the action's is a call to another stored procedure.
> The
> action of that call will be rolled back right? Or is just if the action
> is a
> statement in the same procedure?
> "Andrew J. Kelly" wrote:
>> You can't. If you roll back an outer transaction all the nested ones
>> roll
>> back as well. If you used a SAVE TRAN you can roll it back but any work
>> done inside that (even nested trans ) are rolled back as well. If you
>> issue
>> a plain ROLLBACK (not associated with a nested tran) then everything is
>> rolled back.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
>> > How do I commit a nested transaction while rolling back the outer
>> > transaction
>> >
>> > BEGIN MASTER TRANSACTION
>> >
>> > Action 1
>> >
>> > BEGIN NESTED TRANSACTION
>> > Action2
>> > COMMIT NESTED TRANSACTION
>> >
>> > ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
>> > action
>> > 2 committed.)
>>