July 27, 2009 at 11:43 am
In the following code, in order to test my error handling, I have purposely commented out the "delete from aoiprograms" so that I'll get a PK error on the insert statement. But when I run this, I get the error message logged to my ErrorLog table
"Violation of PRIMARY KEY constraint 'AOIPrograms$PrimaryKey'. Cannot insert duplicate key in object 'dbo.AOIPrograms'."
as expected,
BUT... exact_state returns a (1) and I get back the @vmsg "N'Transaction is committed! Yay!", which is NOT what I expected.
WHY would the code still consider this a "committable transaction????"
declare @delErr int,@inserr int,@ErrorLogID int
set @delerr=0
set @inserr=0
begin try
begin tran
--delete from aoiprograms
INSERT INTO AOIPrograms ( noun, tag, LastTestDate )
SELECT Panels.noun,
case
when tagset is null then 'none'
else tagset
end
as tag,
Max(Panels.testdate) AS LastTestDate
--'hi there'
FROM Panels
GROUP BY Panels.noun,
case
when tagset is null then 'none'
else tagset
end;
commit transaction
set @vmsg='Transaction Committed'
end try
begin catch
exec dbo.uspLogError @ErrorLogID=@ErrorLogID OUTPUT
exec errordetails
print 'one-' + convert(nvarchar(5),xact_state())
if (xact_state())=-1
begin
set @vmsg='gonna roll back transaction'
print N'the transaction cant be committed'
rollback transaction
exec dbo.uspLogError @ErrorLogID=@ErrorLogID OUTPUT
end
print 'two-' + convert(nvarchar(5),xact_state())
if xact_state()=1
begin
print N'Transaction is committed! Yay!'
commit transaction
end
print 'three-' + convert(nvarchar(5),xact_state())
end catch
select * from dbo.errorlog where errorlogid=@errorlogid
END
select @vmsg
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
July 27, 2009 at 11:53 am
You need to have Xact_Abort set to "on" for it to work the way you expect. Check out "set Xact_Abort" in Books Online for the details.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 27, 2009 at 12:17 pm
Since the insert failed, the transaction is still committable...because there is nothing IN the transaction.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 27, 2009 at 12:35 pm
Thanks! That's what I was missing. Although I can't right now think of an example where I'd want Xact_abort set to OFF.
I am really struggling with implementing error handling in my code, as it's very different from error handling in Access, which I am converting from.
Can you tell me what effect, if any, that Xact_abort has outside of an explicit transaction?
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
July 27, 2009 at 1:17 pm
I don't see any need to get XACT_ABORT on for that code. It should fail on the PK violation and jump to the CATCH. Unless I'm missing something thing..?
SET XACT_ABORT OFF
DECLARE @Foo TABLE (ID INT PRIMARY KEY)
INSERT @Foo SELECT 1
BEGIN TRY
--SELECT 1/0
INSERT @Foo SELECT 1
PRINT 'No Error'
END TRY
BEGIN CATCH
PRINT 'ERROR'
END CATCHEDIT: Bah.. yeah I missed the "if xact_state()=1" My bad.
July 27, 2009 at 1:37 pm
She Through Whom All Data Flows (7/27/2009)
Thanks! That's what I was missing. Although I can't right now think of an example where I'd want Xact_abort set to OFF.I am really struggling with implementing error handling in my code, as it's very different from error handling in Access, which I am converting from.
Can you tell me what effect, if any, that Xact_abort has outside of an explicit transaction?
Yeah, it's quite different.
So far as I know, it has no effect other than exactly for managing explicit transactions.
I usually don't bother with it. I usually have a rollback command in my catch block, and that's almost always exactly what I want. If I want to commit a piece of it separately, I manage that with commit commands in the exact places that I want them. Keeps it simple.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 27, 2009 at 3:34 pm
But my point is, to clarify, that it does fail on the PK violation and jump to the Catch, but xact_state is still =1. I have changed this now to use
if xact_state()0
which seems to work.
So, my take-away is that the two methods outlined below would be equivalent. In the first case, xact_abort would cause the transaction to fail if any one step in it fails (like the PK violation). In the 2nd case, even though it's a "commitable transaction", the code should jump to the CATCH statement on the PK violation, and xact_state will not be zero because the transaction, although committable, is still waiting to be committed.
Case #1
declare @delErr int,@inserr int,@ErrorLogID int
set @delerr=0
set @inserr=0
begin try
set xact_abort=on
begin tran
--delete from aoiprograms
INSERT INTO AOIPrograms ( noun, tag, LastTestDate )
SELECT Panels.noun,
case
when tagset is null then 'none'
else tagset
end
as tag,
Max(Panels.testdate) AS LastTestDate
FROM Panels
GROUP BY Panels.noun,
case
when tagset is null then 'none'
else tagset
end;
commit transaction
set @vmsg='Transaction Committed'
end try
begin catch
exec dbo.uspLogError @ErrorLogID=@ErrorLogID OUTPUT
exec errordetails
print 'one-' + convert(nvarchar(5),xact_state())
if (xact_state())=-1
begin
set @vmsg='gonna roll back transaction'
print N'the transaction cant be committed'
rollback transaction
exec dbo.uspLogError @ErrorLogID=@ErrorLogID OUTPUT
end
end catch
select * from dbo.errorlog where errorlogid=@errorlogid
END
select @vmsg
OR
declare @delErr int,@inserr int,@ErrorLogID int
set @delerr=0
set @inserr=0
begin try
set exact_abort=off
begin tran
--delete from aoiprograms
INSERT INTO AOIPrograms ( noun, tag, LastTestDate )
SELECT Panels.noun,
case
when tagset is null then 'none'
else tagset
end
as tag,
Max(Panels.testdate) AS LastTestDate
--'hi there'
FROM Panels
GROUP BY Panels.noun,
case
when tagset is null then 'none'
else tagset
end;
commit transaction
set @vmsg='Transaction Committed'
end try
begin catch
exec dbo.uspLogError @ErrorLogID=@ErrorLogID OUTPUT
exec errordetails
print 'one-' + convert(nvarchar(5),xact_state())
if (xact_state())0
begin
set @vmsg='gonna roll back transaction'
print N'the transaction cant be committed'
rollback transaction
exec dbo.uspLogError @ErrorLogID=@ErrorLogID OUTPUT
end
end catch
select * from dbo.errorlog where errorlogid=@errorlogid
END
select @vmsg
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
July 28, 2009 at 6:52 am
Those will work, but if you implement code like that, it's much harder to document, because it doesn't have as natural a logic-flow. Make sure you include a full description in comments any/every time you use that in production code, and even then, expect someone to be confused by it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 28, 2009 at 10:44 am
"Much harder to document" than what??? I am struggling with the best method of error handling for transactions. If there's a clearer way, I'd really appreciate an example.
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
July 29, 2009 at 7:03 am
It took a paragraph for you to explain what you were doing in one of the examples. That's what I mean by harder to document. The one with xact_abort can cite the relevant portions of Books Online for clear definition. You don't have to document xact_abort and xact_state when they are used together, because Microsoft has already done so. Anyone looking at the code can look them up and it will be clear what's being done.
The other example, where xact_abort isn't used, and you use xact_state = 0 in the catch block, isn't really covered in BOL, so you'd have to include a description of what it does and WHY it is designed that way, in every proc that has it, or you risk causing a problem for other developers later on. Or for yourself if you have to look at the code a year from now and don't remember precisely why you did it that way.
That's the only point I'm making. Yes, they both end up with the same end result. No, they don't get there the same way. One path is documented for you in published, readily available formats. The other is a variation from the standard methodology, and thus requires more explanation. That's all I'm saying here.
Sorry that wasn't clear when I first wrote it. Does this help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2009 at 12:11 pm
There are tons of ways to handle errors.. Personally I do not use the XACT_STATE. That doens't mean it isn't valuable. But, I guess that might depend on the complexity of the processing. You an create functions that capture all teh error information and build new strings so you can get the original error number and all that junk. Obviously, you can do a lot of stuff in regards to error handling. I tend to keep it as simple as I can. Not that there is anything wrong with with you are doing but here is a, realtivly, simple template for handling errors so you can see another way of doing it. I've left out some details like returns codes and such. But, hopefully, you'll get the gist. DECLARE @ErrorNumberINT
DECLARE @ErrorSeverityINT
DECLARE @ErrorStateINT
DECLARE @ErrorProcedureNVARCHAR(126)
DECLARE @ErrorLineINT
DECLARE @ErrorMessageNVARCHAR(4000)
BEGIN TRY
-- Stuff
END TRY
BEGIN CATCH
-- Could use a WHILE loops to rollback nested transactions
IF (@@TRANCOUNT > 0)
BEGIN
PRINT N'Rolling Back...'
ROLLBACK TRANSACTION
END
-- Get Details
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorProcedure = ERROR_PROCEDURE(),
@ErrorLine = ERROR_LINE(),
@ErrorMessage = ERROR_MESSAGE(),
-- Attempt To Log Failure
BEGIN TRY
-- Log junk
END TRY
BEGIN CATCH
-- Do Nothing
END CATCH
END CATCH Just as a side note: I tend to write as much of my code as atomically as possible, so I rarely need to even check for open transactions.
August 3, 2009 at 9:54 am
GSquared, yes, that helps. Thanks!
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
August 3, 2009 at 9:59 am
Lamprey13, thanks. One question: What good does it do to have a try--catch block where the catch block does nothing? How is this different from just doing the "log junk" outside of a try--catch?
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
August 3, 2009 at 11:16 am
Naw, probably not much different than doing a junk log.
My thought was that it seemed like good form to wrap it in a try-catch. Then if,in the future we ever wanted to add logging to the event log incase something goes really wrong we could. But, so far it hasn't been needed.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply