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.
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).
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.
Try using the WAITFOR command in T-SQL. That'll pause the execution of the query and you should see a timeout. Try using the WAITFOR command in T-SQL. That'll pause the execution of the query and you should see a timeout.
Using SQL Server Management StudioIn Object Explorer, right-click a server and select Properties. Click the Connections node. Under Remote server connections, in the Remote query timeout box, type or select a value from 0 through 2,147,483,647 to set the maximum number seconds for SQL Server to wait before timing out.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With