Rollback during Timeout question 1

[cmcgann]

Hi,

I am experiencing a problem where an SQL transaction does not committ when the updates in the procedure times out.

BEGIN TRANSACTION

UPDATE WHATEVER
SELECT WHATEVER
UPDATE WHATEVER

COMMIT TRANSACTION

The knock on effect of this is that locks are held open for table WHATEVER until the transaction is comitted or the application is restarted. Note: I have tried to put in error trapping but this does not work.. If anyone has had any experience with an issue like this i would love to hear.

I dont want to modify the timeout value either, basically i just want to know why SQL Server does not automatically rollback this transaction


Thanks

Conor

 

[SQLSister]

YOu need a rollback transaction statement for it to rollback.

YOu also should look at why you update is timing out. If you have a very large recordset- you might want to consider doing the update in batches. If you you used a cursor, you might want to consoider replacing it with a set-based statment.

Questions about posting. See faq183-874

 

[vongrunt]

Application...Are we talking about server-side (a la stored proc) or client-side code (a la ADO .BeginTrans) here?

 

[cmcgann]

Hi there,

Thanks for the swift response. First off i have tried using rollback in error handling but the problem here is that it never gets executed by sql server. The rollback code goes something like this

-- IF @@Error <> 0
-- BEGIN
-- ROLLBACK TRANSACTION
-- END

This is a Server side stored procedure.

 

[vongrunt]

OK then... have you tried SET XACT_ABORT ON?

 

[cmcgann]

Hi vongrunt,

I hadn't. Tried it or heard of it. I had a quick look in help and this seems to be what i am looking for. I'll let you know how i get on.

Thanks again.

 

[cmcgann]

I tried this and it works. Thanks all for your help..

 

[cmcgann]

Does anyone know how this can be set at the server side? Instead of explicitly setting it in each procedure, have it set as default?

Thanks