T-SQL: Lock a table manually for some minutes [duplicate]

Question

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?

Answer 1

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

Hope it helps.