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.
Col1 = @State,
Col2 = Getdate(),
Col3 = @Error
WHERE Col4 = @MessageID
SELECT 'SUCCESS' as Response
SELECT ERROR_MESSAGE() as Response
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.