Forcing a query timeout in SQL Server

sql-server testing timeout locking
BnWasteland picture BnWasteland · Apr 28, 2009 · Viewed 49.9k times · Source

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

KM. picture KM. · Apr 28, 2009

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

Related questions

How to Set up PHP Test Server in Dreamweaver?

I am trying to setup a PHP server so that I could use the "Live" feature in Dreamweaver, in addition to being able to preview in my browser without having to upload the .php file via an FTP application every …

Read More
Should UAT data be a mirror of Production? And if so, how?

We have been kicking about an idea where UAT can be tested with near live data (say maximum a week old). I strongly believe that development and QA environments should be in control of their own data, but UAT (the …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More