November 4, 2011 Leave a comment
How often does it happen: you write an application that uses a SQL Database as source, and when you want to fetch data you get a time-out. Damn! But when you need to get a time-out (for example, if you want to test an exception), you don’t get one. In SQL Server you can create your own time-outs if you want.
One of the simplest ways to generate a time-out, is to lock an object, and running a query against the object in another session. You can lock an object with a simple query:
SELECT * FROM Company WITH(TABLOCKX, HOLDLOCK)
The query hint TABLOCKX puts an Exclusive lock on the table, and the HOLDLOCK hint creates a more exclusive Shared Lock, but this will be hold until the end of your transaction.
This will be obsolete if your query will retrieve 10 records for example, because this will not hold a lock long enough to cause a time-out. You can hold a lock longer by using the WAITFOR hint:
BEGIN TRANSACTION SELECT * FROM Company WITH(TABLOCKX, HOLDLOCK) WHERE 0 = 1 WAITFOR DELAY '00:00:05' ROLLBACK TRANSACTION
This query uses the same hints as the first query, with the addition with the WAITFOR DELAY. This will instruct SQL Server to wait for a specified time or time interval, before executing the ROLLBACK TRANSATION.
The WAITFOR can be used in two ways:
WAITFOR DELAY ‘00:00:05’
This will instruct SQL to wait for five seconds.
WAITFOR TIME ‘12:00:05’
This will instruct SQL to wait untill 5 seconds past 12:00, to execute the ROLLBACK.
With these statements it’s possible to test use Locks and test time-outs without killing a SQL Server. There is only one more thing I need to tell you:
USE THE FORCE WISELY! ONLY FOR GOOD, AND NOT FOR EVIL!!! :)