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.