Transaction not rolling back - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Transaction not rolling back

Author

Topic

KiwiinAussie
Starting Member

5 Posts

Posted - 2007-03-18 : 05:59:47

I have a stored procedure that calls another stored procedure with the first stored procedure opening a transaction:

BEGIN
SET XACT_ABORT ON

BEGIN TRANSACTION

does various updates/inserts

calls 2nd stored procedure to proccess updates/inserts common to many other stored procedures

does more various updates/inserts

commit

END

The problem I'm having is that within the 2nd stored procedure is that if it encounters an error, it does not roll back the entire transaction and I finish up with missing records in the database. Am using this in the 2nd stored procedure:

if(@TypeId1 = @TypeId2 and @Line1 <> '' and @Line2 <> '')
begin
RAISERROR('error message', 16, 1)
RETURN
end

What could the problem be? From what I've read, it seems as though you can't have an open transaction within one sp that calls another sp and it maintains the same transactoin? Is this corrrect?

I tired the following too, and I still couldn't get it to work. Any ideas anyone?

************ sp 1 ***********

Declare @AddressError char(3)

SET XACT_ABORT ON
BEGIN TRANSACTION

exec Sp2
@AddressError OUTPUT,
@variable1,
@variable2,
etc. etc

************** sp 2 *****************

@AddressError char(3) OUTPUT,

if(@TypeId1 = @TypeId2 and @Line1 <> '' and @Line2 <> '')
begin
RAISERROR('error message', 16, 1)
RETURN
end

SET XACT_ABORT ON
BEGIN TRANSACTION

process updates/inserts

Set @AddressError = 'no'
Commit

******** back to sp 1************

If @AddressError <> 'no'
BEGIN
rollback transaction
END

continue doing updates/inserts

commit

Kristen
Test

22859 Posts

Posted - 2007-03-19 : 10:15:48

"calls 2nd stored procedure to proccess updates/inserts common to many other stored procedures"

You need to check the Return value from the 2ns Sproc and/or @@ERROR and issue a ROLLBACK if appropriate.

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-19 : 11:47:33

Also creating nested transactions is unnecessary. Before creating transaction, always check whether transaction is already in progress, if not then create new one.

IF @@TRANCOUNT = 0
  BEGIN TRANSACTION

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-03-19 : 14:55:06

If you are going to do that Harsh you will also need a local variable to remember whether you started the transaction, so that you can COMMIT it if there is no error, but otherwise leave it for the outer "layer" to Commit.

If you ROLLBACK the outer layer is going to have to "detect" that has happened.

What about if you want to ROLLBACK just the stuff in your local SProc Harsh? You'll need your own "transaction layer" for that ...

(We use some very convoluted logic to stop the outer layers getting snarled up by the "transaction level is not at the level expected" [sorry, forgotten the exact wording - especially as we don't see the message any more

] when we ROLLBACK an inner transaction layer.

Kristen

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-03-19 : 23:37:06

Surely best to handle the transaction at the client side. Who else 'knows' when the transaction starts and when it is complete?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-20 : 04:36:26

"If you are going to do that Harsh you will also need a local variable to remember whether you started the transaction, so that you can COMMIT it if there is no error, but otherwise leave it for the outer "layer" to Commit."

Why? Isn't @@TRANCOUNT sufficient to detect whether transaction is started or not? If there is an error, anyway we are going to ROLLBACK it or may be we can save error value to some OUTPUT variable to pass it on to outer layer to decide what to do with it.

"What about if you want to ROLLBACK just the stuff in your local SProc Harsh? You'll need your own "transaction layer" for that ..."

If you just want to rollback the stuff in local SP, you will need to make use of Savepoint. Simple Rollback Transaction will rollback till the outermost transaction.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-03-20 : 14:02:33

"or may be we can save error value to some OUTPUT variable to pass it on to outer layer to decide what to do with it"

That's the bit I mean. If you don't "remember" if you started the transaction you don't know whether to pass an Error Code back, or ROLLBACK the "single" layer transaction (that the current "layer" started).

We usually make a local transaction, roll that back if we need to (using SavePoint etc.) and pass back a "Failed" result code.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources