Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Implicit Update in TRY..CATCH can it become uncommitable Expand / Collapse
Author
Message
Posted Wednesday, May 15, 2013 7:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 7:17 AM
Points: 6, Visits: 117
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
Post #1453106
Posted Wednesday, May 15, 2013 8:41 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, December 11, 2014 12:22 PM
Points: 1,446, Visits: 3,232
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.
Post #1453137
Posted Wednesday, May 15, 2013 12:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 7:17 AM
Points: 6, Visits: 117
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?
Post #1453254
Posted Wednesday, May 15, 2013 1:04 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, December 11, 2014 12:22 PM
Points: 1,446, Visits: 3,232
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.
Post #1453256
Posted Wednesday, May 15, 2013 1:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 7:17 AM
Points: 6, Visits: 117
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?
Post #1453257
Posted Wednesday, May 15, 2013 1:42 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, December 11, 2014 12:22 PM
Points: 1,446, Visits: 3,232
Yes.



The probability of survival is inversely proportional to the angle of arrival.
Post #1453259
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse