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:

BEGIN TRY
DECLARE @RETVAL int
EXEC @RETVAL = Utility.WaitForSync$StartWait @WaitSeconds=10
IF @RETVAL = -100
     RAISERROR (‘Time Elapsed’,16,1)

–Other code

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH

Which would return something like:

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

Time Elapsed

You can download the code from my website’s downloadable code page. 

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:

CREATE TABLE Utility.WaitForSync
(
    WaitforDelayValue nchar(8) NOT NULL,
    StartTime          datetime2(0) NOT NULL, –used to make sure you can do a waitfor that crosses a day boundry
    OnlyOneRow          tinyint PRIMARY KEY DEFAULT (1) CHECK (OnlyOneRow = 1) 
);

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).

CREATE PROCEDURE Utility.WaitForSync$Reset
AS
———————————————————————————————-
— Louis Davidson        drsql.org
—
— Simple enough, just delete the row from the table
———————————————————————————————-
    DELETE FROM Utility.WaitForSync
GO

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. 

ALTER PROCEDURE Utility.WaitForSync$StartWait
(
    @WaitSeconds    int –minimum amount of time to wait. The WAITFOR statement
                            –always starts as minute changes..
)
AS
————————————————————————————————
—- Louis Davidson        drsql.org
—-
—- Either starts a new wait for session or uses the existing one
————————————————————————————————
SET NOCOUNT ON
BEGIN TRY
    DECLARE @StartTime datetime2(0),
            @WaitforDelayValue NCHAR(8),
            @ProcStartTime datetime2(0) = SYSDATETIME();
    –Get the row from the table where we hold the sync value.
    SELECT  @StartTime = StartTime,
            @WaitforDelayValue = WaitforDelayValue
    FROM   Utility.WaitForSync;

    –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,””);
   
    EXECUTE (@queryText);
    PRINT ‘WaitForSync: Starting at: ‘ + CAST(@startTime AS NVARCHAR(20)) +
           ‘ Waited for: ‘ + CAST(DATEDIFF(SECOND,@procStartTime, SYSDATETIME()) AS VARCHAR(10)) + ‘ seconds.’
  END TRY
 
  BEGIN CATCH
    –benign output that won’t stop anything. Simply keep going
    PRINT ‘WaitForSync: An error occurred. Message: ‘ + CHAR(13) + CHAR(10)
            + ERROR_MESSAGE()
    RETURN -100 –The caller can use the return value to decide if they want to stop what they are doing
  END CATCH
GO

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.