SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Skipping transaction...or...rolling back just one


Skipping transaction...or...rolling back just one

Author
Message
Deki
Deki
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 266
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,
iamsql1
iamsql1
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 139
Dear,
Can you swap the order of Trans 1 and Trans 2 in order to check whether Trans 1 commits or not ?
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5148 Visits: 875
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
Deki
Deki
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 266
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.....
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5148 Visits: 875
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search