Database Table lock

Question

I'm using SQL 2000 for my application. My application is using N tables. My application has a wrapper for SQL server called Database server. It is running as a 24/7 windows service.

If I have checked the integrity check option in the SQL maintenance plan, when this task is running one time after that one of my tables has been locked and it has been never unlocked. So my history of the database transaction has been lost.

Please provide your suggestion how to solve this problem.

Answer 1

What if you have a client-side command timeout? And the locks are your own locks as a result of the DBCC?

Your code will timeout waiting for the DBCC to finish, but any locks it's already issued are not rolled back.

A command timeout tells SQL Server to simply stop processing. To release locks you need to either ROLLACK on the connection or close the connection.

Options:

• Use SET XACT_ABORT in the SQL: Do I really need to use “SET XACT_ABORT ON”? (SO)
• On client error, try and rollback yourself (Literally IF @@TRANCOUNT > 0 ROLLBACK TRAN)