强制 SQLServer 中的查询超时

我们曾经遇到过一个代码块的问题，它在面对缓慢的数据库时响应很差(它在查询超时时出了问题)。我们已经创建了一个补丁，并且正在通过回归运行它。

我们不能暂停。我已经从 SQLMgmtStudio 打开了一个事务，并更新了每一行以锁定它们，但是这不会导致 INSERT 超时(这正是我所需要的)。

我可以通过 T-SQL 轻松获得表级锁吗？还是要我去调戏主人？或者我可以轻松地在不锁定的情况下强制超时吗？任何意见都欢迎。

57040 次浏览
小开
最佳答案

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
小开

You can just tell your sql code to wait for a minute before returning:

WaitFor Delay '00:01:00'
小开

Check out the October 20, 2005 blog post "There's no such thing as a query timeout..." by Ken Henderson. Basically, SQL Server doesn't have query timeouts. Clients may enforce a SQL timeout but the engine itself does not.

小开

On the flip side: If connection is configurable, reduce connection string timeout to 1 second - that will make it easier. Fill the table with oodles of data and have 3 other processes spin in a loop updating chunks of that table with a transaction around the loop. Do not alter actual procedure called by the app (injecting waitfor). That invalidates an integration test.

But really, this is a case study in favor unit testing and dependency injection. Some things are just hard to integration test. Unit test + dependency injection.

  • Real: Code that craps -> Database timeout (hard to reproduce).
  • Refactor: Code that craps -> Repository (does data access only) ->Database
  • Unit test: Code that craps > Mock repository to throw -> null
  • Now you have a failing test for code that craps and can fix it.

This is "dependency" injection. The dev can inject the dependency to the database, substituting something that simulates the behavior of a dependency. Good to do for all database tests. Anyway, with the unit test in place, you know the fix does sort of what it should, but you still need an integration testing. In this case, it may better focus on regression - which means testing it didn't break anything else and the feature still works.

You've already created your patch, so I guess my answer is too late.


提交答案