Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Mutexes in SQL

By Toby Harman,

Introduction

Controlling the flow of processes on multiple servers is something that we all face as virtualised and blade servers take over the data centre. Server A runs one part of a job and puts a file into a shared folder to be picked up by servers B, C and D. So how do we tell these other servers that their file is ready for processing? And when we add a new server, do we have to modify the solution? There are many scenarios where one process must finish before the other starts. The usual solution for this is to implement a table somewhere that holds a "stop-or-go" flag, but this solution can have issues when processes terminate badly and don't clean up after themselves.

We faced a similar requirement recently, and a feature of SQL Server that I hadn't used before was suggested. The sp_getapplock (and the counterpart sp_releaseapplock) can be used to lock a "resource" in the database, allowing other processes to check for that resource and wait for it to release for a specified time. Programmers who have a working knowledge of threading will recognise the concept as a mutex, and SQL has a nice implementation that can be used to great effect.

Setup the environment

As our solution required processes from different servers which access different schemas within the SQL instance, we created a stored procedure to wrap around the sp_getapplock. This handles the fact that the "resource" is created inside the current schema and allows us to provide a centralised store for these mutexes.

CREATE PROCEDURE [spGetAppLock]
@p_Resource NVARCHAR(255)
, @p_LockMode NVARCHAR(32)
, @p_LockOwner NVARCHAR(32)
, @p_LockTimeout INT
, @p_DbPrincipal NVARCHAR(32)
AS
DECLARE
@l_Result INT
EXEC @l_Result = sp_getapplock
@Resource = @p_Resource
, @LockMode = @p_LockMode
, @LockOwner = @p_LockOwner
, @LockTimeout = @p_LockTimeout
, @DbPrincipal = @p_DbPrincipal;

RETURN @l_Result
GO

Anything that we create, we must also be able to destroy, so there is also the "release" version of the procedure:

CREATE PROCEDURE [spRelAppLock]
@p_Resource NVARCHAR(255)
, @p_LockOwner NVARCHAR(32)
, @p_DbPrincipal NVARCHAR(32)
AS
DECLARE
@l_Result INT
EXEC @l_Result = sp_releaseapplock
@Resource = @p_Resource
, @LockOwner = @p_LockOwner
, @DbPrincipal = @p_DbPrincipal;
RETURN @l_Result;
GO

Controlling the Flow

Now we can use this to control two or more processes on the same resource by executing our stored procedure in the correct schema:

EXEC [Maintenance].[dbo].[spGetAppLock]
@p_Resource = 'MyResource' -- Mutex Name
, @p_LockMode = 'Shared' -- Lock Mode. Shared / Exclusive
, @p_LockOwner = 'Session' -- Lock parent (Transaction or Session)
, @p_LockTimeout = 600000 -- milliseconds
, @p_DbPrincipal = 'dbo'

The lock mode for this should be reasonably self-explanatory. Shared locks can co-exist with Shared locks, but "Exclusive" means that only one process can access this resource. The Lock Owner is a little less obvious. An owner of "Transaction" mode means that there must be a transaction active, and the mutex will be released either when the transaction terminates or it is explicitly released. An owner of "Session" means that the mutex will be held until it is released or the entire session terminates.

Using a mutex in Transaction mode doesn't seem particularly useful, as we already have record locks that release at the end of a transaction, so we chose to use "Session" mode.

The final part of this is the timeout. This allows us to set an arbitrary limit on how long we are prepared to wait for that resource to become free, and control the behaviour of our application if the lock doesn't become free. Using this we can choose to send alert messages, terminate gracefully or any other option that is allowed.

The only thing left to do is to release our locks:

EXEC @l_Result = [Maintenance].[dbo].[spRelAppLock]
@p_Resource = 'MyResource'
, @p_LockOwner = 'Session'
, @p_DbPrincipal = 'dbo'

Conclusion

Using this technique allows us to resolve the conflict between complex processes running on different servers, or an external process that needs to wait for a stored procedure to complete. The database sits at the centre of all the applications that consume its data, so it makes a good place to implement these mutexes, and Microsoft has been kind enough to provide us with this feature. Its not recommended for all solutions, but when you absolutely, positively, must control the processes and the order they happen in, it can provide the co-ordination we sometimes lack.

Total article views: 8595 | Views in the last 30 days: 4
 
Related Articles
FORUM

Cluster missing SQL Server Resources

Cluster missing SQL Server Resources

FORUM

SQL Server 2005 Resources Management

SQL Server 2005 Resources Management

FORUM

Server becomes very slow [RESOURCE_SEMAPHORE] waittype.

Server becomes very slow [RESOURCE_SEMAPHORE] waittype.

ARTICLE

SQL Server DTS Resources

A list of DTS resources, including bugs, issues, books, and some good programming resources.

ARTICLE

Resource Governor

This article is about Resource Governor. A new feature introduced in SQL Server 2008. Special focus ...

Tags
mutex    
software development    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones