title: How can I set a lock inside a stored procedure?
body: <p>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.</p>
  
  <p>Once the stored procedure has run, I want to store the latest result in a <code>LatestResult</code> table, against a time, and have all calls to the procedure return that result for the next ten minutes.</p>
  
  <p>That much is relatively simple, but we've found that, because the procedure checks the <code>LatestResult</code> table and updates it, that large userbases are getting a number of deadlocks, when two users call the procedure at the same time.</p>
  
  <p>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 <code>LatestResult</code> table, and unlocks the second user, who then picks up the result from the <code>LatestResult</code> table.</p>
  
  <p>Is there any way to accomplish this kind of locking in SQL Server?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>This is basically how the code looks without its error checking calls:</p>
  
  <pre><code>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()) &gt;= 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
  </code></pre>
  
  <p>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.</p>
  
  <p>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...</p>