July 26, 2013 at 12:14 am
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,
July 26, 2013 at 3:54 pm
Dear,
Can you swap the order of Trans 1 and Trans 2 in order to check whether Trans 1 commits or not ?
July 26, 2013 at 4:02 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 26, 2013 at 5:50 pm
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.....
July 27, 2013 at 1:36 am
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.)
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply