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:

  1. Triggers on the detail tables.  
  2. 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.  
  3. 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]].  
  4. 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)   -- Only successful return codes
BEGIN
  PRINT 
@result
  
RAISERROR 'Lock failed to acquire for Proj Aggregate table population.'16)
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 OneUser TwoUser Three

Procedure is called

  1. Mutex is granted 
  2. Since no data is in the cache or it is stale we begin to populate the cache.  
  
 

Procedure is called

  1. Mutex is not granted.  User is blocked. 
 
  

Procedure is called

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

  1. Data cache is not stale, all ProjectIDs are available
  2. Data is returned immediately to the client
  3. Mutex is released.  
 
  

Mutex is granted.

 

  1. Data cache is not stale, all ProjectIDs are available
  2. Data is returned immediately to the client
  3. Mutex is released.