Forcing a request timeout in SQL Server

Question

We had a problem with a block of code that responds poorly to slow databases (it keeps the bed at the request timeout). We created the patch and are in the process of launching it through regression.

We cannot get a timeout. I opened a transaction from SQL Mgmt Studio and updated each row to lock them, but this does not result in an INSERT timeout (this is what I need).

Is it easy to get table-level locking via T-SQL? Or do I need to be a master? Or can I easily force a timeout without blocking? Any input is appreciated.

Answer 1

run this and then try pasting ...

select * from yourTable with (holdlock,tablockx) 

here you can block it for 5 minutes:

 BEGIN TRANSACTION SELECT * FROM yourTable WITH (TABLOCKX, HOLDLOCK) WHERE 0 = 1 WAITFOR DELAY '00:05' ROLLBACK TRANSACTION 

Answer 2

You can simply specify your sql code to wait a minute before returning:

 WaitFor Delay '00:01:00' 

Answer 3

On the tip side: if the connection is configured, reduce the wait time for the connection string to 1 second - this will simplify the work. Fill the table with data varieties and ask 3 other processes to spin a cycle that updates pieces of this table with a transaction around the cycle. Do not modify the actual procedure invoked by the application (enter waitfor). This invalidates the integration test.

But actually, this is an example of research in favor of unit testing and dependency injection. Some things are just hard to integrate the test. Unit test + dependency injection .

• Real: Code that holds → Database timeout (hard to reproduce).
• Refactoring: Code that holds → Repository (data access only) → Database
• Unit test: Code that holds> Mock repository for throw -> null
• Now you have an unsuccessful test for code that holds and can fix it.

This is an injection of dependencies. Dev can add a dependency to the database by replacing something that mimics the behavior of the dependency. It’s good to do all the database tests. Anyway, with Unit test in place, you know that the fix does what it should, but you still need integration testing. In this case, he can better focus on regression, which means that testing does not violate anything else, and the function still works.

You have already created your patch, so I think my answer is too late.

Answer 4

Check out this blog post. Basically, SQL Server does not have query timeouts. Clients can use the SQL timeout, but the engine itself does not work.

http://blogs.msdn.com/khen1234/archive/2005/10/20/483015.aspx