Re: Classic Nest SP with Transaction Question

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 02/19/05

Next message: Ron Strong: "Re: Classic Nest SP with Transaction Question"
Previous message: Tom Moreau: "Re: Classic Nest SP with Transaction Question"
In reply to: Tom Moreau: "Re: Classic Nest SP with Transaction Question"
Next in thread: Ron Strong: "Re: Classic Nest SP with Transaction Question"
Messages sorted by: [ date ] [ thread ]

Date: Sat, 19 Feb 2005 18:06:37 -0500

Typo:

create proc dbo.ChildProc
as
set nocount on

declare @trancount int

set @trancount = @@TRANCOUNT

if @trancount = 0 -- No existing tran
begin tran ChildProcTran
else -- Existing tran
save tran ChildProcTran

/*
Do some stuff
*/

if @@ERROR > 0
begin
    raiserror ('We have a problem.', 16, 1)
    rollback ChildProcTran
    return
end
else if @trancount = 0 -- began our own
    commit tran
go

-- 
   Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinnaclepublishing.com
.
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message 
news:%232UHhStFFHA.3972@TK2MSFTNGP15.phx.gbl...
PMFJI, but if your child proc is using an explicit tran, then it can be
coded as follows:
create proc dbo.ChildProc
as
set nocount on
declare @trancount int
set @trancount = @@TRANCOUNT
if @trancount > 0
    begin tran ChildProcTran
else
    save tran ChildProcTran
/*
    Do some stuff
*/
if @@ERROR > 0
begin
    raiserror ('We have a problem.', 16, 1)
    rollback ChildProcTran
    return
end
else if @trancount = 0    -- began our own
    commit tran
go
This way, only the child proc's txn will be rolled back by the child proc.
The parent proc will  be unaffected.
-- 
   Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinnaclepublishing.com
.
"Ron Strong" <rstrong@DoNotSpamerols.com> wrote in message
news:ulKZzJtFFHA.1528@TK2MSFTNGP09.phx.gbl...
Brian & Chad
I believe I'm having the same issue as Chad with nested stored procedures
inside a transaction.
What I'd like to do is begin a transaction in an outer SP.  If all goes
well, the transaction will be committed in the outer stored procedure - no
problem there.  However, if an error or other unexpected condition is
encountered, I would like to rollback as close to the error as possible (in
the statement following detection, if possible).
Problem is this might involve a transaction begun in an outer SP being
rollbed back in an inner SP.  Due to the fact that on entry to inner SP
@@Trancount == 1 but on exit @@Trancount == 0, a new error, Error 266, gets
generated.
Following illustrates the problem:
--  INNER SP
create procedure InnerSP as begin
 declare @ErrNo int
 /* Do something here */
 select @ErrNo = @@ERROR
 if @ErrNo <> 0 begin
    if @@TRANCOUNT > 0  rollback transaction
    return @ErrNo    --    on return new error (266) generated
 end
 return 0
end
--  OUTER SP
create procedure  OuterSP as begin
 declare @ErrNo int
 Begin Transaction
 exec @ErrNo = InnerSP
     --    if InnerSP failed, @@ERROR = 266 here
 if @ErrNo <> 0 begin
  if @@TRANCOUNT > 0  rollback transaction
  return @ErrNo
 end
 commit transaction
 return 0
end
I could hold off on performing the rollback until OuterSP examines the
return value from InnerSP, but this is not ideal:
    (1)    Immediately after the error I may want to do some logging or
other fixup. If these are done before the rollback, they will be wiped out
by the rollback.
    (2)    In code subsequent to ther error there is always the possibility
that execution will be terminated due to a severe error, preventing the
enclosing SP from ever executing the rollback.  While the lack of a
subsequent COMMIT will ultimately lead to the transaction being rolled back,
I would have no control over when the rollback occurs.
I can avoid this problem via the kludge of a new "Begin Transaction"
statement just before returning the error code from InnerSP to OuterSP.  Is
there a cleaner way to resolve this problem (beyond waiting for SQL Server
2005 try...catch blocks)?
Ron Strong
"Chad" <chad.dokmanovich@unisys.com> wrote in message
news:cv7rc2$h6a$1@trsvr.tr.unisys.com...
> Brian,
>
> Thank you again for your feedback. I appreciate the tip, in particular on
> handling concurrency problems using RowVersion, and I believe understand
the
> thrust of your points.
>
> However, I would like to place a spot light on a point I originally made
> that I feel may not have been addressed:
>
> *** If @@TRANCOUNT is = X in ParentSP when ChildSP is called, it must be =
X
> immediately after returning from the CHILD call. , else an error
results***
>
> If feel that this is the situation in the example you proposed.
>
> If the ParentSP BEGINs a TRANSACTON (Transaction count is now 1), then
calls
> ChildSP, which does a ROLLBACK within Child, TranCount will be 0 when
> control is returned to the Parent. Since TranCount was 1 just prior to
> calling the Child and it is zero immeditely after returning, this result
in
> an ERROR:
>
> > Server: Msg 50000, Level 16, State 1, Procedure ChildSP, Line 10
> > an error was raised
> > Server: Msg 266, Level 16, State 2, Procedure ChildSP, Line 26
> > Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> > TRANSACTION statement is missing. Previous count = 1, current count = 0.
>
>
> This is the part that I am missing. It seems to me that the Child cannot
do
> the rollback if the Parent already began a Transaction.
>
> I hope I am not trying your patience. I would really like to get this
point
> down.
>
> Thanks,
> Chad
>
>
> "Brian Selzer" <BrianSelzer@discussions.microsoft.com> wrote in message
> news:9E615D99-D61A-4AF3-BEFB-09C2C12281D3@microsoft.com...
> > The code I provided will work when called directly or from another
stored
> > procedure.  Use it as a template for both the parent and the child
> > procedure--in fact use this mechanism in all of your procedures.
> >
> > You should declare an additional variable, @RC, in the parent procedure
to
> > receive the return code from the stored procedure call. Otherwise you
will
> > lose the error code that originally caused the failure, for example:
> >
> > DECLARE @RC INT, @_ERROR INT
> > EXEC @RC = ChildProc
> > SET @_ERROR = @@ERROR
> > IF @RC != 0 OR @_ERROR != 0 GOTO ERROR
> >
> > The key to this approach is that any error, regardless of the reason
> > (Constraint violation, out of memory, Deadlock victim, etc.) is detected
> > and
> > handled immediately after it occurs, and the error handling code rolls
> > back
> > the transaction.  When an error occurs in the child procedure, it rolls
> > back
> > any pending transaction and returns the error code to the caller.  The
> > parent
> > procedure detects that an error occurred by examining the return code,
and
> > transferrs control to its own error handler.  Since the transaction had
> > already been rolled back in the child procedure, @@TRANCOUNT is zero and
> > thus
> > a rollback in the parent's error handler would cause an additional
error.
> > The condition IF @@TRANCOUNT > 0  prevents this.  (It also prevents an
> > additional error in the event the procedure is chosen as a deadlock
> > victim.)
> >
> > I often extend this mechanism to detect concurrency problems.  For
> > example:
> >
> >    DECLARE @_ERROR INT, @_ROWCOUNT INT
> >
> >    BEGIN TRANSACTION
> >
> >    UPDATE t1 SET col1 = @col1 where key1 = @Key and ver1 = @version
> >    SELECT @_ERROR = @@ERROR, @_ROWCOUNT = @@ROWCOUNT
> >    IF @_ERROR != 0 OR @_ROWCOUNT = 0 GOTO ERROR
> >
> >    COMMIT TRANSACTION
> >    RETURN 0
> >
> > ERROR:
> >    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
> >    IF @_ERROR = 0 AND @_ROWCOUNT = 0
> >        RETURN -1          -- indicate that a record was changed by
another
> > user
> >    ELSE
> >        RETURN @_ERROR
> > END
> >
> > ver1 is a rowversion (timestamp) column, which is changed any time a
> > record
> > is changed.  If another user changes the record after the time it was
> > read,
> > then ver1 will be different than @version, the update statement will not
> > affect any rows, and consequently @@ROWCOUNT will be zero.
> >
> >
> > "Chad" wrote:
> >
> >> Thank you for the response. I don't fully understand.
> >>
> >> In my example, I wanted to be able to call a ChildSP directly, or call
a
> >> ParentSP which calls the ChildSP, and if an error occurs in the child,
> >> everything gets rolled back. Your exaple only included one stored proc,
> >> so I
> >> was a little unclear.
> >>
> >> I tried to create a 2 SP example using your style. In your error
handler,
> >> you check to see if @@TranCount > 0. If so, you know that there was an
> >> error
> >> above. Howver, if we take this approach in the ChildSP, performing a
> >> Rollback would cause the @@TranCount to be set to zero, and when you
> >> return
> >> to the ParentSP, we find that @@TranCount is now 0, but it was 1
perform
> >> we
> >> called ChildSP, and so we ge the error:
> >>
> >> Server: Msg 50000, Level 16, State 1, Procedure ChildSP, Line 10
> >> an error was raised
> >> Server: Msg 266, Level 16, State 2, Procedure ChildSP, Line 26
> >> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> >> TRANSACTION statement is missing. Previous count = 1, current count =
0.
> >>
> >> Try running the code below.
> >>
> >> I would be very much indebted if you could take the 2 SP example and
> >> modify
> >> it to a approach that works and is sane.
> >>
> >>
> >> CREATE TABLE [dbo].[Table1] (
> >>  [col1] [int] NULL ,
> >>  [col2] [int] NULL
> >> ) ON [PRIMARY]
> >>
> >>
> >> CREATE procedure ParentSP
> >>
> >> as
> >>
> >> begin
> >>
> >> DECLARE @_ERROR INT
> >>
> >> BEGIN TRANSACTION
> >>
> >> exec @_ERROR = ChildSP 1
> >>
> >> SELECT @_ERROR = @@ERROR
> >>
> >> IF @_ERROR != 0 GOTO ERROR
> >>
> >> COMMIT TRANSACTION
> >>
> >> RETURN 0
> >>
> >> ERROR:
> >>
> >> IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
> >>
> >> RETURN @_ERROR
> >>
> >> end
> >>
> >>
> >>
> >> CREATE procedure ChildSP
> >>
> >> (@RaiseError bit)
> >>
> >> as
> >>
> >> begin
> >>
> >> DECLARE @_ERROR INT
> >>
> >> BEGIN TRANSACTION
> >>
> >> if (@RaiseError = 1)
> >>
> >> RAISERROR ('an error was raised', 16, 1)
> >>
> >> ELSE
> >>
> >> UPDATE table1 set col1 = 1
> >>
> >>
> >> SELECT @_ERROR = @@ERROR
> >>
> >> IF @_ERROR != 0 GOTO ERROR
> >>
> >> UPDATE table1 set col2 = 2
> >>
> >> SELECT @_ERROR = @@ERROR
> >>
> >> IF @_ERROR != 0 GOTO ERROR
> >>
> >> COMMIT TRANSACTION
> >>
> >> RETURN 0
> >>
> >> ERROR:
> >>
> >> IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
> >>
> >> RETURN @_ERROR
> >>
> >> end
> >>
> >>
> >>
> >> "Brian Selzer" <BrianSelzer@discussions.microsoft.com> wrote in message
> >> news:97330484-71EA-4142-9B25-DAE902EA8836@microsoft.com...
> >> > There's a few things you should know:
> >> > First: always check for errors after each DML statement or SP call
> >> > within
> >> > a
> >> > transaction, because it is possible for an early DML statement to
fail,
> >> > and
> >> > later ones to pass which causes an insidious data consistency bug
that
> >> > is
> >> > extremely difficult to find.  Here's what I do:
> >> >
> >> > DECLARE @_ERROR INT
> >> >
> >> > BEGIN TRANSACTION
> >> >
> >> > UPDATE t1 set col1 = @col1
> >> > SELECT @_ERROR = @@ERROR
> >> > IF @_ERROR != 0 GOTO ERROR
> >> >
> >> > UPDATE t2 set col2 = @col2
> >> > SELECT @_ERROR = @@ERROR
> >> > IF @_ERROR != 0 GOTO ERROR
> >> >
> >> > COMMIT TRANSACTION
> >> > RETURN 0
> >> >
> >> > ERROR:
> >> > IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
> >> > RETURN @_ERROR
> >> >
> >> > This approach should make your inquiry moot, since @@ERROR is set on
> >> > exit
> >> > from a procedure if @@TRANCOUNT is less than what it was upon entry.
> >> >
> >> >
> >> > I only use save points if I want to roll back only part of a
> >> > transaction,
> >> > here's what I do:
> >> >
> >> > DECLARE @_TRANCOUNT INT SET @_TRANCOUNT = @@TRANCOUNT
> >> > DECLARE @_ERROR INT
> >> >
> >> > IF @_TRANCOUNT = 0
> >> >    BEGIN TRANSACTION savePoint
> >> > ELSE
> >> >    SAVE TRANSACTION savePoint
> >> >
> >> > UPDATE t1 set col1 = @col1
> >> > SELECT @_ERROR = @@ERROR
> >> > IF @_ERROR != 0 GOTO ERROR
> >> >
> >> > UPDATE t2 set col2 = @col2
> >> > SELECT @_ERROR = @@ERROR
> >> > IF @_ERROR != 0 GOTO ERROR
> >> >
> >> > IF @_TRANCOUNT = 0
> >> >    COMMIT TRANSACTION savePoint
> >> >
> >> > RETURN 0
> >> >
> >> > ERROR:
> >> > IF @@TRANCOUNT > @_TRANCOUNT
> >> >    ROLLBACK TRANSACTION savePoint
> >> >
> >> > RETURN @_ERROR
> >> >
> >> >
> >> >
> >> >
> >> > "Chad" wrote:
> >> >
> >> >> I have 2 Stored Procedures, "Parent" and "Child".  The Parent SP
calls
> >> >> the
> >> >> Child SP, but also the Child may be called directly.
> >> >>
> >> >> If the Child returns an error (which occurs whenever it is passed a
> >> >> value
> >> >> of
> >> >> 2), I want all updates to be rolled out.
> >> >>
> >> >> I have a couple of working version of these 2 SPs, but what I am
> >> >> looking
> >> >> for
> >> >> is "What is a the best (or a good way) of doing this?"
> >> >>
> >> >> This is what I understand about transactions:
> >> >> 1) Performing a BEGIN TRAN increments @@TRANCOUNT
> >> >> 2) Performing an END TtRAN decrements @@TRANCOUNT
> >> >> 3) A ROLLBACK TRAN returns @@TRANCOUNT to 0
> >> >> 4) If @@TRANCOUNT is = X in Parent when CHild is called, it must be
=
> >> >> X
> >> >> immediately after returning from the CHILD call.
> >> >>
> >> >> I've played around with SAVE POINTs within a transaction, but I do
not
> >> >> have
> >> >> a sample here.
> >> >>
> >> >> I am looking for the  simpilist, most intuative sane and hopefully
> >> >> common
> >> >> approach to take here, I'm not sure I like what I've done-It's seems
> >> >> counter
> >> >> intuitive.
> >> >>
> >> >> Please alter my example as you would code it. Many thanks.
> >> >>
> >> >> --****************EXAMPLE 1
> >> >> --Setup: Create a table
> >> >>
> >> >> CREATE TABLE [dbo].[Table1] (
> >> >>  [col1] [int] NULL ,
> >> >>  [col2] [int] NULL
> >> >> ) ON [PRIMARY]
> >> >> GO
> >> >>
> >> >> --Throw a rec into it
> >> >> INSERT INTO table1 (col1,col2) values (1,1)
> >> >>
> >> >> --Create the Parent SP
> >> >> CREATE procedure dbo.parent
> >> >> as
> >> >> begin
> >> >>
> >> >> declare @res int
> >> >>
> >> >> begin transaction
> >> >>
> >> >> update table1 set col1 = 1
> >> >> update table1 set col1 = 2
> >> >>
> >> >> exec @res = child 2
> >> >>
> >> >> if @Res = -1
> >> >>     begin
> >> >>   rollback transaction
> >> >>   return @res
> >> >>     end
> >> >>
> >> >> commit transaction
> >> >>
> >> >> return 0
> >> >>
> >> >> end
> >> >>
> >> >> --CREATE CHILD SP -
> >> >>
> >> >> CREATE procedure Child
> >> >> @col2 int
> >> >> as
> >> >> begin
> >> >>
> >> >> begin transaction
> >> >>
> >> >> update table1 set col1 = @col2
> >> >> update table1 set col2 = @col2
> >> >>
> >> >> if @col2 = 2 --DONT PASS 2!! It's an error!
> >> >>  begin
> >> >>   IF @@Trancount = 1
> >> >>    Rollback TRANSACTION
> >> >>   else
> >> >>    Commit transaction --needs to be the same value as when we
entered
> >> >> this
> >> >> SP
> >> >>   PRINT 'TRANCOUNT IN CHILD ' + CAST(@@TRANCOUNT AS VARCHAR(10))
> >> >>   return -1
> >> >>  end
> >> >>
> >> >>
> >> >> commit transaction
> >> >>
> >> >> return 0
> >> >>
> >> >> end
> >> >>
> >>
>> -------------------------------------------------------------------------
--------------
> >> >> --****************EXAMPLE 2
> >> >>
> >> >> ALTER procedure dbo.parent
> >> >> as
> >> >> begin
> >> >>
> >> >> declare @res int
> >> >>
> >> >> begin transaction
> >> >>
> >> >> update table1 set col1 = 1
> >> >> update table1 set col1 = 2
> >> >>
> >> >> exec @res = child 2
> >> >>
> >> >> if @Res = -1
> >> >>     begin
> >> >>         rollback transaction
> >> >>   return @res
> >> >>     end
> >> >>
> >> >> commit transaction
> >> >>
> >> >> return 0
> >> >>
> >> >> end
> >> >>
> >> >>
> >> >> ALTER procedure Child
> >> >> @col2 int
> >> >> as
> >> >> begin
> >> >>
> >> >> begin transaction
> >> >>
> >> >> update table1 set col1 = @col2
> >> >> update table1 set col2 = @col2
> >> >>
> >> >> if @col2 = 2 --DONT PASS 2!! It's an error!
> >> >>  begin
> >> >>
> >> >>      if @@trancount > 1  --outer SP started the transaction
> >> >>          commit transaction --leave it to the parent to rollback
outer
> >> >> trans
> >> >>   else
> >> >>       rollback transaction
> >> >>
> >> >>   return -1
> >> >>  end
> >> >>
> >> >>
> >> >> commit transaction
> >> >>
> >> >> return 0
> >> >>
> >> >> end
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Next message: Ron Strong: "Re: Classic Nest SP with Transaction Question"
Previous message: Tom Moreau: "Re: Classic Nest SP with Transaction Question"
In reply to: Tom Moreau: "Re: Classic Nest SP with Transaction Question"
Next in thread: Ron Strong: "Re: Classic Nest SP with Transaction Question"
Messages sorted by: [ date ] [ thread ]

Relevant Pages

Re: SQL SERVER Rollback Problems
... It will not be fully committed until you issue a COMMIT TRAN. ... If your sp had an error or was manually stopped the transaction is still ... issue the ROLLBACK ALL operations are rolled back up until that first BEGIN ... > execution. ...
(microsoft.public.sqlserver.programming)
Re: Is statement.executeBacth() a transaction?
... point and did not make my question clearer enough. ... rollback() in the catch since the driver already does that, ... The reason I am confused about it is that the "transaction" nature ... A commit is a commit, ...
(comp.lang.java.programmer)
Re: how to roll back
... You can only use rollback within a transaction block. ... If you commit a ... COMMIT TRAN, then ROLLBACK TRAN to ...
(microsoft.public.sqlserver.server)
Re: help - transaction control
... transaction to successfully commit before any of the inner ones are ... If a Rollback is issued anywhere along the way ALL transactions ... ROLLBACK TRAN ... > My understanding of the way that transactions nest> is that I should be able to have each SP> begin and commit a transaction within itself,> and if the SP happens to be called from some context> that has begun a transaction, then the SP would participate> in that transaction context. ...
(microsoft.public.sqlserver.programming)
Re: TRANSACTION question
... Basically it will only commit to the database it ... The first time you do a Begin Transaction its set to 1, ... through a calling sp 1 is added to @@TRANCOUNT. ... So its normally the calling sp that finally commits it to ...
(microsoft.public.sqlserver.programming)