Utility Objects–Waitfor Delay Coordinator (SQL Server 2008+)

Finally… took longer than I had expected when I wrote this a while back, but I had to move my website and get DNS moved before I could post code…

When I write code, I do my best to test that code in as many ways as necessary. One of the last types of tests that is necessary is concurrency testing. Concurrency testing is one of the most difficult types of testing because it takes running multiple processes simultaneously and making sure that you get the correct answers multiple times. This is really difficult when you need to make > 1 statement that takes only a second or two simultaneously with another (or even 10 other) connections. What I am trying to replicate is the WAITFOR statement with a DELAY, but multiple connections use the same delay.

As I am writing my sequences presentation for SQL Rally, I wanted to test how values were allocated to each row based on different caching levels. I was looking at the clock, creating WAITFOR TIME statements, and copying the values into multiple windows as I have done so many times before. I was also thinking that I should start blogging again, and (like my sequence presentation) do something code oriented rather than design for a bit (I just finished my design book, so I am going to rest on that subject for a bit, well, other than the SQL Rally precon that I am subliminally trying to get you to sign up for before prices go up again).

So envisioned a kind of multi-user WAITFOR statement that would say: Start all processes in 20 seconds, then start multiple connections. (As I write this, I am envisioning a kind of semaphore version of this that you could hold until ready to go, but more on that some other day.) I initially used some 2012 features like FORMAT, but I wanted to make sure this was useful to a more wide audience. The usage of the system is really simple, to have your connection wait for 20 seconds, you execute:

EXEC Utility.WaitForSync$StartWait @WaitSeconds=20

To have multiple connections start at the same second, you execute the same statement on a different connection. The stored procedure calculates the time in 20 seconds, stores the value off and executes a WAITFOR TIME statement on each connection you have executed this statement on. After the time passes and the connection continues, you have to reset the connection or you will receive the following message (not an error, just a PRINT statement, since you may want the batch to complete):

WaitForSync: An error occurred. Message: Too late to start another WAITFOR session. Last session DELAY time was 2012-03-20 17:55:03. Use Utility.WaitForSync$reset to start new sessions

To reset the session, as the message says, just execute Utility.WaitForSync$reset. There are also procedures to view the time when the processes will continue.

If you want to not do the action if the WAITFOR TIME statements have already completed before you start an operation, the procedure will return a –100. So you might use something like the following TRY…CATCH Block to control execution:

Of course, the most interesting thing about downloading code is learning how to write your own code that uses the same patterns and techniques, so let me cover the highlights. The table is pretty simple:

It only allows a single row using the PK Constraint, a default, and a check constraint. A bit of a trick, but definitely workable trick to ensure a given cardinality in a table. The important two columns are one for the value that will be used in the WAITFOR statement, and the StartTime column is used to allow you to do your demos right around midnight (and yes, I put this in because when I was first using the procedure, it was taking a really long time to complete late one night.)

The idea is, the first user will put a row in here at a given time, then every caller after that (and before the StartTime) will use the same time. After the StartTime, the process has already started.

The simplest procedure is the reset procedure. It just deletes the row from the table (even if other users are still in progress, but this is just a utility for demos and testing).

The StartWait procedure is a bit more complicated that that.. It takes a parameter of @WaitSeconds that lets the first caller to set the time. Usually 10 seconds works great for a few connections, but it depends on just how much you need to coordinate. The code is commented, and is really pretty simple. The basics are that it checks to see if a row exists and grabs the value, if it doesn’t, then it creates a new row.

--if a value was not already stored, we are just starting IF @StartTime IS NULL BEGIN --set the startTime to the current time + the number of seconds in parame SET @StartTime = DATEADD(second,@waitSeconds,SYSDATETIME());

--then I use a good old style convert with a format to get the time for the delay SET @WaitforDelayValue = CONVERT(nchar(8),@starttime, 108);

--insert the value into the WaitForSyncing table. StartTime willbe used to make sure --the time is later, even if it crosses the day boundry INSERT INTO Utility.WaitForSync(WaitforDelayValue, StartTime) VALUES (@WaitforDelayValue,@StartTime); END --if the time has already passed, we raise an error to the client that you can't piggy back on the --existing session, reset ELSE IF @StartTime <= SYSDATETIME() BEGIN DECLARE @msg nvarchar(1000) SET @msg = N'Too late to start another WAITFOR session. Last session DELAY time was ' + CAST(@startTime AS NVARCHAR(20)) + '.' + N' Use Utility.WaitForSync$reset to start new sessions'; RAISERROR (@msg,16,1); END --finally, we take the delay value we created and use it in an execute statement --note that SSMS won't how the SELECT until after the batch resumes. DECLARE @queryText NVARCHAR(100) = 'WAITFOR TIME ' + QUOTENAME(@WaitforDelayValue,'''');

Finally, there is a viewing procedure that lets you see what the time that the procedure starts is. Note that it has a 1 return value if there is no session started, and a –100 if an expired session is out there or there is an error.