August 17, 2009 at 6:55 am
Hi
I've been dealing with a doomed transaction error on a ss2k8 SP1 box.
Stored proc1 called Stored proc2 which had an error and was unable to commit successfully and falling back on the caller and raised a doomed transaction.
The code wrapping the transaction is the one used from the Microsoft example on this page:
It check whether it is the first transaction or not and does accordingly (save transaction or begin a new one)
My point is while tracking that issue (the doomed transaction), I've come along something I'm unable to explain. After, yes even after the rollback (DBCC opentran was NOT showing any transaction after the rollback, so it was rollbacked at that point) I was still unable to write to the log file (transaction log). Our application custom log line code EXEC Schema.LogSPCode 'StoredProcName', @ErrorNumber, @ErrorMessage was raising the doomed transaction. I had to write a custom check to avoid writing in our log (and by the same time the transaction log)
Here's the code of Stored proc 1:
DECLARE @IsDoomedTransaction AS BIT = 0
DECLARE @TranCounter int
SET @TranCounter = @@TRANCOUNT
IF @TranCounter > 0 SAVE TRANSACTION InnerTrans
ELSE BEGIN TRANSACTION
BEGIN TRY
-- business code here
-- stored proc 2 is called and raise an error uncommitable
IF @TranCounter = 0 COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (@TranCounter = 0 AND XACT_STATE() = -1) SET @IsDoomedTransaction = 1
IF @TranCounter = 0 ROLLBACK TRANSACTION;
ELSE IF XACT_STATE() <> -1 ROLLBACK TRANSACTION InnerTrans
DECLARE @ErrorMessage nvarchar(250)
DECLARE @ErrorNumber int
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorNumber = ERROR_NUMBER()
-- at this point the rollback was performed. DBCC opentran was not showing any open transaction
-- still I'm unable to do any logged DML operations
IF @IsDoomedTransaction = 0
EXEC Tools.LogException 'StoredProcName', @ErrorNumber, @ErrorMessage
END CATCH
What I'm doing wrong or what did I missed?
Thks a lot!
August 17, 2009 at 2:50 pm
I found the issue and how to correct it. No need to think about it.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply