Classic Nest SP with Transaction Question

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
endThere'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 f
or
> 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 hav
e
> 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 count
er
> 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 tra
ns
> else
> rollback transaction
> return -1
> end
>
> commit transaction
> return 0
> end
>
>|||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:
>|||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 paren
t
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 thu
s
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 err
or
> 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 retur
n
> to the ParentSP, we find that @.@.TranCount is now 0, but it was 1 perform w
e
> 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 modif
y
> 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...
>
>|||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:
>|||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:
>
>
> 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...
stored
to
will
and
error.
another
a
handler,
perform
0.
fail,
that
calls
=
not
entered
--
outer
>|||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:
>
>
> 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...
stored
to
will
and
error.
another
a
handler,
perform
0.
fail,
that
calls
=
not
entered
--
outer
>|||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:
>
>
> 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...
stored
to
will
and
error.
another
a
handler,
perform
0.
fail,
that
calls
=
not
entered
--
outer
>|||This will take care of what is done in the child, but what I want to do is
rollback the entire outer transaction - the one initiated in the outer SP.
My example may have been too brief -- the outer SP may be making calls to
several child SPs. What I would like is that any error, whether
encountered in the outer SP or its child SPs, results in an immediate
rollback of all the work performed within the transaction initiated in the
outer SP.
The rule, enforced by the raising of error 266, that entry Trancount = exit
Trancount, seems to preclude doing this.
Ron Strong
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#2UHhStFFHA.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...
on
> the
=
> X
> results***
> calls
> in
0.
> do
> point
> stored
procedure
> to
> will
detected
rolls
> and
had
and
> error.
> another
not
call
> a
child,
proc,
> handler,
an
> perform
> 0.
message
> fail,
> that
on
entry.
> calls
a
be
> =
> not
seems
> entered
>
> --
> outer
>|||Well, you could nevertheless have the child manage itself, since it never
knows how it will be called. When it throws its error, it returns to the
calling proc, which then decides to rollback and exit, without calling any
more procs.
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:O2r1EmtFFHA.3648@.TK2MSFTNGP09.phx.gbl...
This will take care of what is done in the child, but what I want to do is
rollback the entire outer transaction - the one initiated in the outer SP.
My example may have been too brief -- the outer SP may be making calls to
several child SPs. What I would like is that any error, whether
encountered in the outer SP or its child SPs, results in an immediate
rollback of all the work performed within the transaction initiated in the
outer SP.
The rule, enforced by the raising of error 266, that entry Trancount = exit
Trancount, seems to preclude doing this.
Ron Strong
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#2UHhStFFHA.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...
on
> the
=
> X
> results***
> calls
> in
0.
> do
> point
> stored
procedure
> to
> will
detected
rolls
> and
had
and
> error.
> another
not
call
> a
child,
proc,
> handler,
an
> perform
> 0.
message
> fail,
> that
on
entry.
> calls
a
be
> =
> not
seems
> entered
>
> --
> outer
>