SQL Server Mutexes and Data Caches

Mutexes in SQL Server can be very useful. First, you may be wondering what a mutex is. Mutex is the shortened form of "mutual exclusion" and is an algorithm used in programming to prevent the concurrent use of a shared resource. Generally a SQL developer is trying to make a database more "concurrent", not less, meaning we are looking for ways to prevent, say, a table (or data) from serializing. But in many cases a critical object in the database can only be accessed by one process or thread at a time (single-threaded). The classic example of where this is needed in a database is a queue table, which I've written about [[Queue Processing Using SQL Server|previously]]. There are lots of types of mutexes (semaphores, message passing, simple locking, etc).

But you may have other needs for mutexes. An example is a aggregate or summary table. In this case an aggregate table stores aggregates of numeric data derived from lots of detail tables. Somehow we need to keep the aggregate table up-to-date as changes are trickling in to the detail tables. Methods to do this include:

Triggers on the detail tables.
If your CRUD to the detail tables is via stored procedures you can inject the equivalent of the trigger code directly into the stored proc.
If you can withstand some latency with your data, is to use an asynchronous update mechanism such as a SQL Agent job or Service Broker. I wrote a summary of how I used Service Broker with asynchronous triggers [[Service Broker|here]].
And of course there is DTS/SSIS.

Each of these mechanisms has its Pros and Cons.

Another solution is to use a true mutex in SQL Server by using sp_getapplock and sp_releaselock. A great use of these mutex devices is to create data caches in SQL Server. Let's assume you need an aggregate table, but maintaining that data from the detail tables is very expensive relative to the frequency that the data is being read. But let's say when the aggregate data is read (such as when a report is run) that many reads will occur (because many reports that use the data run simultaneously.

And that is the exact scenario where I have used this. Some background...we have a series of Reporting Services reports that need to aggregate data a particular way. We display these reports "dashboard-style" meaning there are quite a few charts that display on the page, which is customizable, and each chart is a separate report. SSRS runs these reports in parallel, not serially. Further, although these are separate reports they each tend to utilize the same aggregate data. So we want to maintain a data cache on the db server where each report can share the data. Since SSRS does not guarantee which report will run in what order, and because the dashboard can be customized to include/exclude reports, we need a generic way to determine who builds the cache and to ensure the cache doesn't get poisoned from either another report or another user running the dashboard.

IF @result NOT IN ( 0, 1 )   -- Only successful return codes
BEGIN
  PRINT @result
  RAISERROR ( 'Lock failed to acquire for Proj Aggregate table population.', 16, 1 )
END
ELSE
BEGIN  --mutex acquired
   --cache ageing...we allow 1 min
   DECLARE @CacheAge DATETIME = DATEADD(mi,-1,GETDATE())
   --take this opportunity to purge the DUMMY table of anything older than 1 min
   DELETE FROM dbo.ProjectCache WHERE CacheInsertDateTime < @CacheAge

   IF EXISTS
       (
           SELECT
               input.ProjectID
               ,pam.ProjectID
           FROM #ProjectIDList input
           LEFT JOIN dbo.ProjectCache pam
               ON input.ProjectID = pam.ProjectID         --natural key
               AND pam.CacheInsertDateTime > @CacheAge        --cache has to be within age
           WHERE pam.ProjectID IS NULL                        --if we are missing cache rows for the ProjectID, fail it.
       )
   BEGIN
       --cache is stale or Project requested does not live in cache
       BEGIN
           INSERT INTO dbo.ProjectCache
           SELECT input.ProjectID
               ...
           FROM ...
       END
   END

   --get the data
   SELECT * FROM dbo.ProjectCache
EXEC @result = sp_releaseapplock @Resource = 'Proj Aggregate table population'
END  --mutex acquired
COMMIT
END

Here we check to see that we obtained a lock. In no case should this ever fail, it will simply wait to obtain the mutex, which is an exclusive lock on the data cache table.

Now that I have my mutex I want to see if the data cache is stale and if so purge it.

Since each report has a set of params we need to make sure our cache has those params (ProjectID).

Here we rebuild the cache.

And finally we return the data from the cache as requested, release the app lock, and commit the transaction.

Assume now that a split second after the first user enters the procedure a second user makes the same request. And then a third user makes the same request a second after that. Here is the order of events.

User One	User Two	User Three
Procedure is called Mutex is granted Since no data is in the cache or it is stale we begin to populate the cache.
	Procedure is called Mutex is not granted. User is blocked.
		Procedure is called Mutex is not granted. User is blocked.
Waiting on Procedure to build data cache...
Waiting on Procedure to build data cache...
Waiting on Procedure to build data cache...
Procedure finally completes. Data cache is rebuilt. Data is returned to the client and mutex is released.
	Mutex is granted. Data cache is not stale, all ProjectIDs are available Data is returned immediately to the client Mutex is released.
		Mutex is granted. Data cache is not stale, all ProjectIDs are available Data is returned immediately to the client Mutex is released.

Dave Wentzel 2011-07-14 CONTENT
sql server data architecture