Re: SP execution on rollback tran?
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 02/25/05
- Next message: Andrew J. Kelly: "Re: Which one is faster?"
- Previous message: Mark Wilden: "Re: declare variable based on existing column"
- In reply to: JP: "Re: SP execution on rollback tran?"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 25 Feb 2005 12:52:56 -0500
If all three need to be complete (meaning it is all or nothing ) then there
is no reason to issue 3 separate Begin trans. The only nested tran that can
be rolled back is one that starts with a SAVE TRAN XX command , not a BEGIN
TRAN. Then you have to explicitly state the saved trans name in the
RollBack command. Other wise a ROLLBACK by itself rolls back ALL
transactions. A Commit will only commit the last tran. If the outer trans
are not committed then none are committed. Please read the topic in BOL
under BEGIN TRAN and SAVE TRAN for more details.
-- Andrew J. Kelly SQL MVP "JP" <JP@discussions.microsoft.com> wrote in message news:AF793E16-FC64-4068-9F8E-AA3933C6B257@microsoft.com... > So if I have > > trans1 > ... > trans2 > ... > trnas3 > ... > > and say I rollback trans 3, it will also rollback 1 and 2 b/c I didnt have > a > save trans? > All 3 trans must complete for the sp to be considered successsful. If any > fail then whole process needs to be washed out. Right now Im specifying > each > trans to rollback depending on where in the process it when wrong. > > "Andrew J. Kelly" wrote: > >> A rollback without any saved name will rollback ALL transactions for that >> connection. You should commit the inner transactions as soon as you are >> done processing that particular part of the tran. None really get >> committed >> until the final commit but you should always deal with each nested tran >> as >> if it were it's own as far as the commits. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "JP" <JP@discussions.microsoft.com> wrote in message >> news:B5E3E364-C0FE-4233-B071-B06A84189B28@microsoft.com... >> >I have a rather complex SP. In order to try and prevent items from >> >executing >> > if something goes wrong Ive placed 4 'begin trans (trans name)' >> > statements >> > in >> > verious palces in the SP. and then at the end of the SP, I commit trans >> > (tran >> > name) in reverse (inner to outer). >> > >> > My question is, if a rollback occurs does the SP execution stop or will >> > it >> > try execute the remaining transactions? >> > >> > >> > -- >> > JP >> > .NET Software Develper >> >> >>
- Next message: Andrew J. Kelly: "Re: Which one is faster?"
- Previous message: Mark Wilden: "Re: declare variable based on existing column"
- In reply to: JP: "Re: SP execution on rollback tran?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
