Nested transaction - SAVE vs BEGIN [TRAN]

Nested transaction - SAVE vs BEGIN [TRAN]

Post by Tek Bo » Sat, 01 Nov 2003 02:57:18


After a lot of poking around on Google Groups, BOL and Query Analyzer, I've
finally figured out a way to make nested transactions work. However, I'd
like to know if what I'm doing is a Good Thing(tm), and if not, why.

A post in August 2001
( http://www.yqcomputer.com/
A.1588%40tkmsftngp02) has led me to believe that you could create atomic
stored procedures that could be safely nested within one another. I'm a fan
of object-oriented programming, even though I'm a relative newbie, so what I
did is create several internal-only (i.e. Private) stored procedures, which
I would then call from other stored procedures I would access via ASP/ADO.
Here's some pseudo-code to illustrate what I'm talking about:


Public SQL Sprocs
==================================
// Called from ASP via ADO -- Creates a new row in Forms table
// or updates the content of an existing row, depending on whether
// @intFormID is passed as a parameter.
CREATE PROCEDURE [dbo].[pForm]
@intFormID int,
@intResultID int OUTPUT, @intResultDesc varchar(200) OUTPUT
AS
SET NOCOUNT ON
IF (@@TRANCOUNT = 0) BEGIN TRAN SaveCustomerFormInfo
ELSE SAVE TRANSACTION SaveCustomerFormInfo

IF (...)
EXEC [dbo].[iForm]
@intResultID OUTPUT, @strResultDesc OUTPUT
ELSE
EXEC [dbo].[uForm]
@intForm, @intResultID OUTPUT, @strResultDesc OUTPUT

IF (@intResultID > 0)
COMMIT TRAN SaveCustomerFormInfo
ELSE
ROLLBACK TRAN SaveCustomerFormInfo
GO



Private SQL Sprocs
==================================
CREATE PROCEDURE [dbo].[iForm]
@intResultID int OUTPUT, @intResultDesc varchar(200) OUTPUT
AS
SET NOCOUNT ON
IF (@@TRANCOUNT = 0) BEGIN TRAN SaveCustomerFormInfo
ELSE SAVE TRANSACTION SaveCustomerFormInfo

// Call table INSERT code
// If there are errors, popluate the error-checking variables
// (@intErrorID, @strErrorDesc)

IF (@intResultID > 0)
BEGIN
COMMIT TRAN SaveCustomerFormInfo
SELECT @intResultID = 0, @strResultDesc = 'Form info added to
table'
ELSE
BEGIN
ROLLBACK TRAN SaveCustomerFormInfo
SELECT @intResultID = @intErrorID, @strResultDesc = @strErrorDesc
END
GO

CREATE PROCEDURE [dbo].[uForm]
@intFormID int,
@intResultID int OUTPUT, @intResultDesc varchar(200) OUTPUT
AS
...
GO



Is there anything inherently wrong with this strategy for nested
transactions? Any help, advice or critiquing would be greatly
appreciated.........


-=Tek Boy=-
 
 
 

Nested transaction - SAVE vs BEGIN [TRAN]

Post by Andrew J. » Sat, 01 Nov 2003 03:53:45

here is no such thing as individual transactions really when you nest them
with one exception that I will get to in a moment. Once you issue a BEGIN
TRAN and before you issue a COMMIT or ROLLBACK any other BEGIN TRAN's that
are issued are considered nested. That means it doesn't matter how many
COMMIT's you issue in the nested ones, if you ROLLBACK the outer one they
ALL get rolled back. In otherwords you can't commit a nested tran and have
it's changes saved if you rollback one that is higher up in the nested
level. The exception I mentioned is only for the ability to ROLLBACK a
nested tran and still commit the outer ones. You can used SAVETRAN for
that. But it only counts for rolling back the saved tran and has nothing
to do with commits.

--

Andrew J. Kelly
SQL Server MVP


"Tek Boy" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...
I've
(http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=OfF5uSvGBH
fan
I
which



 
 
 

Nested transaction - SAVE vs BEGIN [TRAN]

Post by David Brow » Sat, 01 Nov 2003 04:43:22


them
have

Right. And with that in mind, here is the pattern for a nestable, atomic
stored procedure that can be invoked whether or not the connection has
already started a transaction.


You really never have to query @@trancount.

David



create procedure sp_foo
as
begin transaction --possibly nested
save transaction sp_foo

[some insert/update/delete statement]
if @@error <> 0 goto eh

[some insert/update/delete statement]
if @@error <> 0 goto eh

[some insert/update/delete statement]
if @@error <> 0 goto eh

. . .

commit transaction --possibly nested
return 0

eh:
--undo just this sp's work
rollback transaction sp_foo

--just to decrement @@trancount
commit transaction

--let the calling SP know that this procedure failed
return 1
 
 
 

Nested transaction - SAVE vs BEGIN [TRAN]

Post by Louis Davi » Sat, 01 Nov 2003 05:26:44

recommend using save transaction (savepoints.) They are cleaner, and
give you full control over what is going on. The first example does not
isolate the subordinate procedure's activities. For example, say you have
the following pseudoprocedures:
--------------------------------------------------------------------
create proc childProc
as
begin transaction
save transaction childProc

do something
if errors
begin
rollback transaction childProc
commit transaction
raiserror to warn the client (not the calling procedure, we do
that later)
return -100 --use negative numbers for errors, less than -99
other negs are system reserved
end

commit transaction
go
---------------------------------------------------------------------
create proc parentProc
as
begin transaction
save transaction parentProc

do something else
if errors
begin
rollback transaction childProc
commit transaction
raiserror to warn the client (not the calling procedure, we do
that later)
return -100 --use negative numbers for errors, less than -99
other negs are system reserved
end

execute @retval = childProc
if @retval not in (0,-100)
begin
rollback transaction childProc
commit transaction
raiserror to warn the client (not the calling procedure, we do
that later)
return -100 --use negative numbers for errors, less than -99
other negs are system reserved
end

commit transaction
go

---------------------------------------------------------------------

Now, we have given the parentProc the right to say that it is happy with
return values of 0, or -100, but not any other value. In more real terms,
the return value or reason for the rollback of the savepoint may be
anything. This method gives you relatively complete control (until we can
trap errors, especially in triggers, you do not get complete control) over
what happens.
--
----------------------------------------------------------------------------
-----------
Louis Davidson ( XXXX@XXXXX.COM )
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

"Tek Boy" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...
I've
(http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=OfF5uSvGBH
fan
I
which


 
 
 

Nested transaction - SAVE vs BEGIN [TRAN]

Post by Tek Bo » Sun, 02 Nov 2003 02:58:48

read David Browne's post before yours, so things are making a bit more
sense now. I guess my description of what I was trying to do was a bit
flawed, but my intention was two-fold:

1) to create a master sproc, called from ASP via ADO, and then make sure
that all of the calls to other sprocs were either committed or rolled back
as a single unit;
2) to write all of the child (called) sprocs so that they could make use
of COMMIT TRAN or ROLLBACK TRAN to allow for execution of that individual
stored procedure (my definition of "individual transaction").


Anyway, between the two of your posts, I learned a lot of useful stuff
today. Thank you for taking the time to reply -- it really makes the whole
learning process a lot more palatable when you're able to get clear and
informed answers from the more experienced developers.


-=Tek Boy=-



"Andrew J. Kelly" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...
them
have
I'd
(http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=OfF5uSvGBH
what
ASP/ADO.
@strErrorDesc


 
 
 

Nested transaction - SAVE vs BEGIN [TRAN]

Post by Andrew J. » Sun, 02 Nov 2003 05:22:39

gt; 1) to create a master sproc, called from ASP via ADO, and then make
sure

OK, I thought you were looking to commit internal ones and rollback external
ones.


use

Yes, SAVED TRAN is the tickect for the internal ones if you need to roll
back individual trans.

--

Andrew J. Kelly
SQL Server MVP


"Tek Boy" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...
whole
BEGIN
that
they
nothing
(http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=OfF5uSvGBH
atomic
a


 
 
 

Nested transaction - SAVE vs BEGIN [TRAN]

Post by Delbert Gl » Sun, 02 Nov 2003 07:17:30


Change this:
--just to decrement @@trancount
commit transaction

to this:
rollback transaction

and then remove this:
save transaction sp_foo

and this:
--undo just this sp's work
rollback transaction sp_foo

.


The structure shown is all or nothing so just
use a transaction[1] -- no need for a save point.

[1] nested/non-nested will take care of itself.

Bye,
Delbert Glass


"David Browne" <davidbaxterbrowne no potted XXXX@XXXXX.COM > wrote in



BEGIN
that
they
nothing
 
 
 

Nested transaction - SAVE vs BEGIN [TRAN]

Post by David Brow » Mon, 03 Nov 2003 00:26:34


No. Don't do that. That would rollback the whole transaction. The point of
the pattern is that the sp is atomic, and doesn't decide the fate of a
transaction declared at a larger scope in case it fails.

David
 
 
 

Nested transaction - SAVE vs BEGIN [TRAN]

Post by Louis Davi » Tue, 04 Nov 2003 13:13:34

Not to mention that if you are calling this from another procedure, you may
get unwanted data modifications:

Create proc outer
as

begin transaction
exec inner

insert into table ...

commit transaction
go

if you had coded the inner proc in this manner, an error will occur stating
that a proc cannot change the @@trancount, control flow will come back to
the outer proc, the insert will occur, and then the commit will cause yet
another error saying that commit had no matching begin transaction.

This is bad advice. Well, the advice that David was commenting on, not
David's, it was good advice :)

--
----------------------------------------------------------------------------
-----------
Louis Davidson ( XXXX@XXXXX.COM )
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.yqcomputer.com/

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

"David Browne" <davidbaxterbrowne no potted XXXX@XXXXX.COM > wrote in



of
 
 
 

Nested transaction - SAVE vs BEGIN [TRAN]

Post by Delbert Gl » Wed, 05 Nov 2003 02:34:18

David said:

Opps, I goofed.

The outermost and any nested BEGIN TRANSACTIONs within it
are registered as the same BEGIN TRANSACTION.
Ends up, a ROLLBACK rollsback everything
to the outer most BEGIN TRANSACTION;
rather, the just the inner most BEGIN TRANSACTION.
Naming the transactions is not helpful in changing that behavior.

Meanwhile with save points one can do restrictive rollbacks.

And to ensure the save point related commands where beening
using inside a transaction David included a "wrapper" transaction.
Futhermore, as David showed, one needs to always utilize
COMMIT to complete that "wrapper" transaction
due to the fact that a ROLLBACK is non-restrictive
and thus would rollback too much if the procedure
was called from within a transaction and cause additional
problems as Louis pointed out.

----------

Louis, your outer proc needs to have error handling code
of it's own to control what the control-flow does.
For example, check the error code immediately following
the call to the inner proc.

----------

Bye,
Delbert Glass



may
stating
 
 
 

Nested transaction - SAVE vs BEGIN [TRAN]

Post by Louis Davi » Wed, 05 Nov 2003 04:24:00

gt; Louis, your outer proc needs to have error handling code

Yeah, I know :) Just being lazy.

--
----------------------------------------------------------------------------
-----------
Louis Davidson ( XXXX@XXXXX.COM )
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

"Delbert Glass" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...
to
yet


 
 
 

Nested transaction - SAVE vs BEGIN [TRAN]

Post by Delbert Gl » Wed, 05 Nov 2003 05:41:19

>Yeah, I know :) Just being lazy.

Yep, that's how I goofed too.

I knew there was a reason,
I had read about the transaction save points
some time ago but got lazy and didn't re-examine
rollback's actual behavior before posting.

Bye,
Delbert Glass