How can I set a lock inside a stored procedure?

Question

I've got a long-running stored procedure on a SQL server database. I don't want it to run more often than once every ten minutes.

Once the stored procedure has run, I want to store the latest result in a LatestResult table, against a time, and have all calls to the procedure return that result for the next ten minutes.

That much is relatively simple, but we've found that, because the procedure checks the LatestResult table and updates it, that large userbases are getting a number of deadlocks, when two users call the procedure at the same time.

In a client-side/threading situation, I would solve this by using a lock, having the first user lock the function, the second user encounters the lock, waiting for the result, the first user finishes their procedure call, updates the LatestResult table, and unlocks the second user, who then picks up the result from the LatestResult table.

Is there any way to accomplish this kind of locking in SQL Server?

EDIT:

This is basically how the code looks without its error checking calls:

DECLARE @LastChecked AS DATETIME
DECLARE @LastResult AS NUMERIC(18,2)
SELECT TOP 1 @LastChecked = LastRunTime, @LastResult = LastResult FROM LastResult

DECLARE @ReturnValue AS NUMERIC(18,2)

IF DATEDIFF(n, @LastChecked, GetDate()) >= 10 OR NOT @LastResult = 0
BEGIN 
    SELECT @ReturnValue = ABS(ISNULL(SUM(ISNULL(Amount,0)),0)) FROM Transactions WHERE ISNULL(DeletedFlag,0) = 0 GROUP BY GroupID ORDER BY ABS(ISNULL(SUM(ISNULL(Amount,0)),0))
        UPDATE LastResult SET LastRunTime = GETDATE(), LastResult = @ReturnValue
        SELECT @ReturnValue
    END
ELSE
BEGIN
    SELECT @LastResult
END

I'm not really sure what's going on with the grouping, but I've found a test system where execution time is coming in around 4 seconds.

I think there's some work scheduled to archive some of these records and boil them down to running totals, which will probably help things given that there's several million rows in that four second table...

Solution

This is a valid opportunity to use an Application Lock (see sp_getapplock and sp_releaseapplock) as it is a lock taken out on a concept that you define, not on any particular rows in any given table. The idea is that you create a transaction, then create this arbitrary lock that has an indetifier, and other processes will wait to enter that piece of code until the lock is released. This works just like lock() at the app layer. The @Resource parameter is the label of the arbitrary "concept". In more complex situations, you can even concatenate a CustomerID or something in there for more granular locking control.

DECLARE @LastChecked DATETIME,
        @LastResult NUMERIC(18,2);
DECLARE @ReturnValue NUMERIC(18,2);

BEGIN TRANSACTION;
EXEC sp_getapplock @Resource = 'check_timing', @LockMode = 'Exclusive';

SELECT TOP 1 -- not sure if this helps the optimizer on a 1 row table, but seems ok
       @LastChecked = LastRunTime,
       @LastResult = LastResult
FROM LastResult;

IF (DATEDIFF(MINUTE, @LastChecked, GETDATE()) >= 10 OR @LastResult <> 0)
BEGIN 
   SELECT @ReturnValue = ABS(ISNULL(SUM(ISNULL(Amount, 0)), 0))
   FROM   Transactions
   WHERE  DeletedFlag = 0
   OR     DeletedFlag IS NULL;

   UPDATE LastResult
   SET    LastRunTime = GETDATE(),
          LastResult = @ReturnValue;
END;
ELSE
BEGIN
   SET @ReturnValue = @LastResult; -- This is always 0 here
END;

SELECT @ReturnValue AS [ReturnValue];

EXEC sp_releaseapplock @Resource = 'check_timing';
COMMIT TRANSACTION;

You need to manage errors / ROLLBACK yourself (as stated in the linked MSDN documentation) so put in the usual TRY / CATCH. But, this does allow you to manage the situation.

If there are any concerns regarding contention on this process, there shouldn't be much as the lookup done right after locking the resource is a SELECT from a single-row table and then an IF statement that (ideally) just returns the last known value if the 10-minute timer hasn't elapsed. Hence, most calls should process rather quickly.

Please note: sp_getapplock / sp_releaseapplock should be used sparingly; Application Locks can definitely be very handy (such as in cases like this one) but they should only be used when absolutely necessary.