Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Skipping transaction...or...rolling back just one Expand / Collapse
Author
Message
Posted Friday, July 26, 2013 12:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 9, 2014 8:19 AM
Points: 9, Visits: 217
Hello,

I have sql code set up like this:

set xact_abort on

declare @ErrorMessage nvarchar(4000)
declare @ErrorSeverity int
declare @ErrorState int

begin try
begin transaction
Transaction 1 -- insert

Transaction 2 ---insert
commit
end try
begin catch
if (XACT_STATE ()) = -1
begin
rollback transaction
end

if (XACT_STATE ()) = 1
begin
commit transaction
end

select @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
raiserror (
@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
)

return 1
end catch

I'm experiencing following symptoms:
Transaction 2 is succesfull (the data is inserted into a table), and transaction 1 is "skipped".
The code is in sp and there are no nested transactions. It's not called from an application but rather from a job.
There are no raised errors.

What's wrong with this folks?

Thanks,



Post #1477870
Posted Friday, July 26, 2013 3:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 16, 2013 11:20 AM
Points: 45, Visits: 139
Dear,
Can you swap the order of Trans 1 and Trans 2 in order to check whether Trans 1 commits or not ?
Post #1478228
Posted Friday, July 26, 2013 4:02 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, October 11, 2014 11:41 AM
Points: 806, Visits: 723
We don't know what's in those transactions so it is hard to tell. But CATCH handler looks wrong. Why would you commit if you get an error? Shouldn't you always roll back?

Then again you have XACT_ABORT ON (Good boy!), so very few errors should lead to xact_state being 1.



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1478231
Posted Friday, July 26, 2013 5:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 9, 2014 8:19 AM
Points: 9, Visits: 217
Sorry, replace Transaction 1 and Transaction 2 with Insert 1 and Insert 2. As it is, it might give an impression of nested transactions and that's not the case. Inserts are ordinary inserts into two tables from one temp table.
I get your point about commit in catch...still...if the catch block was reached, raiserror should have raised an error or am I wrong?
So, if the catch block wasn't reached, that would mean that error severity was 0-10. Is there such an error of that severity that would cause only successfull Insert 2?
But then again, set xact_abort is ON....
Maybe I'm looking at this from a wrong angle.....
Post #1478250
Posted Saturday, July 27, 2013 1:36 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, October 11, 2014 11:41 AM
Points: 806, Visits: 723
Yes, looks like you need to look deeper. Either that first INSERT is not doing what you think it does, or something else is deleting rows from that table.

You could add a trigger for INSERT, UDPATE and DELETE on that table to track what is going on. Don't forget to log zero-row operations somewhere! (This trigger and the audit tables would only to debug this issue, so you would delete them later.)


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1478270
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse