T-SQL：手动锁定表几分钟 [英] T-SQL: Lock a table manually for some minutes

问题描述

我知道这将是奇怪的，但我想在我的MVC应用程序中触发一个错误，这个错误将基于一个LINQ查询，其中我想从表中获得一个记录。虽然此记录将使用T-SQL命令（例如无限循环总是更新此记录）阻塞（数据库/表/行）级别，然后我的LINQ查询将执行查询以读取该记录。

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?

推荐答案

如果我在第一次尝试的时候，理解你的问题正确，它听起来像你想强制表锁的目的测试。要使用SQL Server执行此操作，您可以创建和运行存储过程，从而在感兴趣的表上引起排它表锁定。当该锁定处于活动状态时，您可以运行要测试的程序。

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.

以下存储过程应该锁定您的表2分钟。您可以根据需要更改延迟参数。

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

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

这样做的一般方法在下面的stackoverflow帖子中描述。 MSDN链接提供了有关WAITFOR语句的一些附加信息。

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

原始stackoverflow帖子

补充MSDN帖子

希望它有帮助。

这篇关于T-SQL：手动锁定表几分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！