58

I know this will be strange, but I want to trigger an error in my MVC application and this error will be based on a LINQ Query, where I would like to get one record from a table. While this record will be blocked (database/table/row) level using T-SQL commands (for example an infinite loop always updating this one record) then my LINQ Query will execute a query to read that record. When LINQ tries then the result should be a time out after 20-30 seconds, anybody has tried this before?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Richárd Baldauf
  • 1,068
  • 2
  • 10
  • 24

1 Answers1

144

If I understand your question correctly, it sounds like you would like to force a table lock for purposes of testing. To do that with SQL Server, you can create and run a stored procedure that will cause an exclusive table lock on the table of interest. While that lock is active, you can run the program you want to test.

The following stored procedure should lock your table for 2 minutes. You can change the delay parameter to suit your needs.

BEGIN TRAN  
SELECT TOP (1) 1 FROM TABLE WITH (TABLOCKX)
WAITFOR DELAY '00:02:00' 
ROLLBACK TRAN   
GO

A general approach for doing this was described in the stackoverflow post below. The MSDN link provides some additional information on the WAITFOR statement.

Original stackoverflow post

Supplementary MSDN post

starball
  • 20,030
  • 7
  • 43
  • 238
Marty
  • 1,896
  • 1
  • 12
  • 11
  • I this answer is old. But why do we have use ROLLBACK TRAN, doesnt SqlServer will remove lock on its own when there is no more statement to execute? – Abhinav Singh Jun 20 '23 at 18:18