Solving Deadlocks with Application Locks

Deadlocks are an enduring feature of SQL Server. They’ve been a source of pain for many over the years and there are various ways to diagnose, mitigate or resolve them. Here I want to demonstrate approach I haven’t seen discussed – using an application lock to segregate processes.

An example where I’ve used this effectively was for a queue table where work would be placed and a number of processes would nibble away at it throughout the day. Separate to this was a maintenance routine which ran each evening to manage partitions on the table. The maintenance job would kick in and would deadlock all those processes.

This was solved with an Application Lock.

What is an application lock?

Great question, I wasn’t familiar with it beforehand either.

Simply put, we’re programmatically declaring a lock as part of our query. In the same way that SQL Server would take out locks on objects, we’re doing this manually against a fictional object.

In the same way as our transactions against database objects, we can acquire and release these locks as well as specifying the locking mode, for example shared or exclusive.

You can find usage of this feature within some system stored procedures. I’m not sure if that’s an endorsement or some technical debt on Microsoft’s part to be honest. Let’s move on.

Using an application lock

There are a two key procedures for an application lock. We’ll look at the fundamentals for what we need here and provide links for further reading. The first procedure we have is to acquire the application lock:

DECLARE @Result INT;

EXEC @Result = sp_getapplock
    @Resource = N'TestLock',
    @LockMode = 'Shared',
    @LockOwner = 'Session';

The sp_getapplock procedure is how we acquire the lock. We can specify a @Resource name which is what multiple locks can reference to challenge access. The @LockMode is like we’d see with regular locking with options including Shared and Exclusive which we’ll use here. Finally the @LockOwner specifies if we’re using a Session or Transaction (default) for the lock. The return value from the procedure will be negative if it fails, otherwise 0 or 1 indicate success.

Once we’ve got a lock and we’ve done our work we want to be able to release it:

EXEC sp_releaseapplock
	@Resource = N'TestLock',
	@LockOwner = 'Session';

We use the sp_releaseapplock procedure to release the lock. We’ve got a couple of the the parameters from when we acquired the lock. The @Resource name of the lock to release and we also have the @LockOwner depending if we took the lock against the Session or Transaction.

That’s all there is to it. With these two tools we’ll orchestrate our processes to get out of each other’s way.

In addition to these we also have two helper functions. Firstly there’s APPLOCK_TEST which tests if a lock could be acquired for a specific resource and lock mode. There is also APPLOCK_MODE which returns the lock mode held for the specified resource name.

Implementing the locks

We won’t be building a deadlock here, we’ll be looking at the pattern to avoid them using the application locks.

To demonstrate this we’ll be recreating the earlier scenario with two procedures – one will be a smaller worker to nibble away, and our maintenance procedure to come and bulldoze them out of the way. Our smaller process will use Shared locks and our maintenance process will take an Exclusive lock.

The smaller worker will take a shared lock and wait a few seconds before releasing it. We’ll run multiple of these using SQLQueryStress tool so we can see them sharing a lock:

CREATE OR ALTER PROC dbo.Nibbler
AS
BEGIN

	/* Take a shared lock for multipe processes */
	EXEC sp_getapplock
		@Resource = N'TestLock',
		@LockMode = 'Shared',
		@LockOwner = 'Session';

	/* Do (a little) work (wait for up to 5 seconds) */
	DECLARE @WaitUntil DATETIME = DATEADD(SECOND, FLOOR(RAND(CHECKSUM(NEWID())) * 5) , GETDATE());
	WAITFOR TIME @WaitUntil;

	/* Release our lock */
	EXEC sp_releaseapplock
		@Resource = N'TestLock',
		@LockOwner = 'Session';

END
GO

Our larger maintenance process will require exclusive access to do its work. Once it has this it will be able to run a longer process without being interrupted:

CREATE OR ALTER PROC dbo.Maintenance
AS
BEGIN

	/* Take an exclusive lock for the duration of work */
	EXEC sp_getapplock
		@Resource = N'TestLock',
		@LockMode = 'Exclusive',
		@LockOwner = 'Session';

	/* Do (a lot of) work */
	WAITFOR DELAY '00:00:30';

	/* Release our lock */
	EXEC sp_releaseapplock
		@Resource = N'TestLock',
		@LockOwner = 'Session';

END
GO

With those in place SQL Query Stress is set up with 8 threads to call our Nibbler proc and we’ll manually call the Maintenance proc to see the impact. We’ll review them in action using the sp_WhoIsActive procedure.

When the workers start running we can see our smaller processes happily sharing the locks and running in parallel:

Once we call our Maintenance process we’ll see it wait for existing work to be finished up so it can acquire the exclusive lock. The smaller threads will finish their work and restart only to be backed up behind our exclusive lock:

Finally once all the smaller threads wrap up our larger workload can acquire the exclusive lock and proceed to do its work uninterrupted:

Once it is complete the exclusive lock is released and the smaller workers can get back to their work again:

That’s all there is to it, a couple of extra lines of code to bookend our procedures and we have an effective method for segregating the processes.

Wrap up

In this post we’ve looked at how we can utilise application locks to have greater control of cross process scheduling. This pattern can be used to avoid deadlocks by ensuring we don’t have conflicting processes running concurrently.

Admittedly this is straightforward locking at play. The difference here is that we’re controlling it manually to effectively schedule our processes.

Until relatively recently I wasn’t aware of the ability to define our own locks like this. Is this something you’ve come across before? Have you had any particularly good or bad experience with it? I’d be interested to hear other views.