在 SQL Server 中强制查询超时 [英] 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.

我们无法获得超时.我已经从 SQL Mgmt Studio 打开了一个事务并更新了每一行以锁定它们，但这不会导致 INSERT 超时(这是我需要的).

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).

我可以通过 T-SQL 轻松获得表级锁吗?还是我必须在主人身上摆弄?或者我可以在不锁定的情况下轻松强制超时吗?任何输入表示赞赏.

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.

推荐答案

运行此程序，然后尝试插入...

run this and then try your insert...

select * from yourTable with (holdlock,tablockx)

在这里，您可以将其锁定 5 分钟:

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

这篇关于在 SQL Server 中强制查询超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！