Forcing a query timeout in SQL Server

Question

We have had an issue with a block of code that responds poorly in the face of slow databases (It craps the bed on a query timeout). We have created a patch, and are in the process of running it through regression.

We can't get a timeout. I've opened a transaction from SQL Mgmt Studio and updated every row to lock them, but that doesn't cause INSERTs to timeout (which is what I need).

Can I get a table-level lock easily via T-SQL? Or do I have to fiddle around in master? Or can I easily force the timeout without locking? Any input is appreciated.

Answer 1

run this and then try your insert...

select * from yourTable with (holdlock,tablockx)

here, you can lock it for 5 minutes:

BEGIN TRANSACTION

SELECT * FROM yourTable WITH (TABLOCKX, HOLDLOCK)

WHERE 0 = 1

WAITFOR DELAY '00:05'

ROLLBACK TRANSACTION

Answer 2

You can just tell your sql code to wait for a minute before returning:

WaitFor Delay '00:01:00'

Answer 3

Check out this blog post. Basically SQL Server doesn't have query timeouts. Clients may enforce a SQL timeout but the engine itself does not.

http://blogs.msdn.com/khen1234/archive/2005/10/20/483015.aspx

Answer 4

On the tip side: If connection is configurable, reduce connection string timeout to 1 second - that will make it easier. Fill the table with oodles of data and have 3 other processes spin in a loop updating chunks of that table with a transaction around the loop. Do not alter actual procedure called by the app (injecting waitfor). That invalidates an integration test.

But really, this is a case study in favor unit testing and dependency injection. Some things are just hard to integration test. Unit test + dependency injection.

• Real: Code that craps -> Database timeout (hard to reproduce).
• Refactor: Code that craps -> Repository (does data access only) ->Database
• Unit test: Code that craps > Mock repository to throw -> null
• Now you have a failing test for code that craps and can fix it.

This is "dependency" injection. The dev can inject the dependency to the database, substituting something that simulates the behavior of a dependency. Good to do for all database tests. Anyway, with the unit test in place, you know the fix does sort of what it should, but you still need an integration testing. In this case, it may better focus on regression - which means testing it didn't break anything else and the feature still works.

You've already created your patch, so I guess my answer is too late.