Release another user's lock obtained with sp_getapplock on SQL Server

Question

We have a system that uses sp_getapplock to create an exclusive mutex any time someone opens an order in the GUI. This is used to prevent multiple people from making changes to an order simultaneously.

Sometimes people will open an order and go home, leaving it open. This effectively blocks anyone from being able to make changes to the order. I then get emails, calls and end up doing a kill <spid> in enterprise manager. Obviously I've gotten sick of this and want to make a quick self-service webform.

The main problem I've run into is that kill requires sysadmin privileges, which I do not want to give to the user that the our website runs as. I have tried sp_releaseapplock but this doesn't let you release another user's lock (even when calling it as a sysadmin).

So, finally my question; does anyone know of an alternative method to release a lock that was obtained by another user using sp_getapplock?

Answer 1

The documentation is pretty clear on this point:

Locks placed on a resource are associated with either the current transaction or the current session. Locks associated with the current transaction are released when the transaction commits or rolls back. Locks associated with the session are released when the session is logged out. When the server shuts down for any reason, all locks are released.

Applocks are analogous to Critical Sections - the primary reason for their existence is so that other threads can't simply override them, unless they have privileges to kill the process that's holding the lock.

Have you considered using some form of optimistic concurrency instead? Applocks are intended to be held for less than a second at a time - i.e. the duration of a typical transaction. It's normally not a good idea to hold onto one for several minutes (or hours) for precisely this reason.

If you must use applocks this way, and can't use optimistic concurrency, then I believe your only recourse is to kill the spid that owns the lock.

If you don't want to give sysadmin privileges to the user, you could create a Stored Procedure to kill the process WITH EXECUTE AS <admin_user>... keeping in mind, of course, that this opens up a pretty wide security hole, so be very careful who you grant execute permissions to.