SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Implicit Update in TRY..CATCH can it become uncommitable


Implicit Update in TRY..CATCH can it become uncommitable

Author
Message
reachshaft
reachshaft
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 225
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
sturner
sturner
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5300 Visits: 3259
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.
reachshaft
reachshaft
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 225
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?
sturner
sturner
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5300 Visits: 3259
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.
reachshaft
reachshaft
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 225
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?
sturner
sturner
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5300 Visits: 3259
Yes.

The probability of survival is inversely proportional to the angle of arrival.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search