Search code examples
sql-servertestingtimeoutlocking

Forcing a query timeout in SQL Server

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.

Solution

  • 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