May 15, 2013 at 7:53 am
Hi All,
My question is could this statement cause an uncommitable transaction and subsequent leave transaction in an open state where the locks it had acquired would persist. I ask the question because I've taken over administration responsibility for a database and a stored procedure that calls this statement is occasionally causing blocking on the database but in activity monitor the state is blank and it sits there inactive until killed. My first instinct from looking at the statement is that the update has failed and the transaction has entered an uncommitable state but what I don't understand is why wouldn't the implicit transaction be rolled back on hitting the catch unless for some reason the catch has been bypassed.
BEGIN Try
UPDATE Table1
SET
Col1 = @State,
Col2 = Getdate(),
Col3 = @Error
WHERE Col4 = @MessageID
SELECT 'SUCCESS' as Response
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() as Response
END CATCH
I have now set XACT_ABORT on and wrapped the update in a transaction and check the XACT_STATE in the catch and rollback if appropriate.
I hope this makes sense, any help would be much appreciated so I can better understand what is going on.
Thanks
May 15, 2013 at 8:41 am
There should be no need to do all that. More than likely the process is being blocked by some other process, have you checked for blocking? (sp_who2)
Having said that though, I would get rid of your explicit transactions and rollback stuff code it this way:
declare @resultMessage
BEGIN Try
UPDATE Table1
SET
Col1 = @State,
Col2 = Getdate(),
Col3 = @Error
WHERE Col4 = @MessageID
set @resultMessage = 'SUCCESS'
END TRY
BEGIN CATCH
set @resultMessage = ERROR_MESSAGE()
END CATCH
select @resultMessage as Result
The probability of survival is inversely proportional to the angle of arrival.
May 15, 2013 at 12:51 pm
When it is causing the blocking it is the head block I've also tried running the profiler while it's acting as the head block and its not doing anything. I realise that the majority of the time the catch should cause an implicit statement to rollback. When this happens the system is very busy so I'm thinking it may be caused by an application side timeout from what I've read this might cause it. Am I correct in thinking this?
May 15, 2013 at 1:04 pm
It would only rollback if there was an error in the SQL statement or the update process was terminated.
Doesn't appear to be the case here.
The probability of survival is inversely proportional to the angle of arrival.
May 15, 2013 at 1:22 pm
What my theory is there is something going wrong in the try block that is causing a bypass of the catch which was my reason for adding the xact_abort on. Do you think I'm barking up the wrong tree?
May 15, 2013 at 1:42 pm
Yes.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply