Using databases to synchronize and co-ordinate processes on a web farm

I was thinking what title was more appropriate for this blog post, the one actually used above, or would “Sql Server as lock manager” would have been more appropriate.

Today only, I had a situation where I needed to ensure that multiple instances of one of our ASP.NET applications deployed on a web farm co-ordinated with each other in executing certain actions and did not get into a race situation with each instance trying to do the same thing as others.

The basic problem was something like this:
Many of the actions in our application I am referencing, are triggered by asynchronous events. For example, the application needs to monitor a shared folder and do some processing as files are added or modified in that folder.

Similarly, all the instances of the application also need to monitor a back-end database, and perform processing when a particular set of fields for a table changed in a specified manner. We are using Sql Server’s Query Notification feature in this case to bring the change notification from the database to the processing logic in the application. There were a couple more such scenarios.

Now the issue was that multiple instances of the application on different servers of the farm were listening to these events, and as soon as the asynchronous event fires, all listeners got notified and started their processing independently of each other. This was a serious concern for us, as the processing also involved financial transactions being entered into a database, and this meant a single transaction had the probability of getting into the database multiple times.

We needed a way to synchronize and serialize the servers so that only one of them got into the processing logic once the event fired.

And after some pondering over, it struck me as why we cannot use a database as a synchronization mechanism for co-ordination across servers. I mean DBMSes already ensure consistency of data for parallel updates with locking mechanisms guaranteeing serializability for any degree of parallelism.

So, why not use the same DBMS feature to acquire an exclusive lock on a designated database resource before getting into the processing loop. As each server would be requesting the lock for a known resource on the same database, and the DBMS would concede only the first request while putting others to wait till the first one is finished, this provided an attractive option for ensuring serializability on the farm.

My initial instinct was to request lock on a table row with known primary key, the row created specifically for such synchronization. Fortunately I requested help at MSDN forums to see what the Sql gurus there had to say about such an implementation. And I was really glad I sought help there, with the veteran Sql Server MVP Erland Sommarskog bringing to my notice 2 really useful Sql Server stored procedures: sp_getapplock and sp_releaseapplock.

Essentially you pass-in a known unique string to sp_getapplock, and the procedure only returns if the lock on that string has not been granted in a non-compatible mode to another request, with the same string, for the same database and database principal (see the parameters on MSDN for the procedures for more details).

If the lock has been granted already, you are put to wait until the lock is released or you are timed out. There was nothing more I could have asked for devising a solution to my problem.

So, I immediately wrote 2 Stored Procedures for acquiring and releasing the locks, as below:

CREATE PROCEDURE AcquireLock
AS
BEGIN
	DECLARE @result int;
	
	EXEC @result = sp_getapplock 
			@Resource = 'MyCustomString', 
			@LockMode = 'Exclusive',
			@LockOwner = 'Session',
			@LockTimeout = 100000; -- In milliseconds, currently equals 100 seconds
	
	IF @result < 0
		RAISERROR (N'Could not obtain exclusive lock.', 
						15, 
						1);
END
GO


CREATE PROCEDURE ReleaseLock
AS
BEGIN
	DECLARE @result int;
	
	EXEC @result = sp_releaseapplock 
			@Resource = 'MyCustomString', 
			@LockOwner = 'Session';
	
	IF @result < 0
		RAISERROR (N'Could not release the lock successfully.', 
						15, 
						1);
END
GO

And from there on, it was simply modifying the application processing logic to acquire the lock first, then perform the processing and finally, release the lock (notice finally, it’s important you deterministically release the lock or future requests for the lock would always hang or timeout).

In the end, it was an easy and intuitive solution for a problem that could really have manifested into an issue to manage properly and efficiently.