Takeaway: For most use cases, using sp_releaseapplock is unnecessary. Especially when using @LockOwner = 'Transaction (which is the default).
The procedure sp_getapplock is a system stored procedure that can be helpful when developing SQL for concurrency. It takes a lock on an imaginary resource and it can be used to avoid race conditions.
But I don’t use sp_getapplock a lot. I almost always depend on SQL Server’s normal locking of resources (like tables, indexes, rows etc…). But I might consider it for complicated situations (like managing sort order in a hierarchy using a table with many different indexes).
In that case, I might use it something like this:
BEGIN TRAN exec sp_getapplock @Resource = @LockResourceName, @LockMode = 'Exclusive', @LockOwner = 'Transaction'; /* read stuff (e.g. "is time slot available?") */ /* change stuff (e.g. "make reservation") */ exec sp_releaseapplock @Resource = @LockResourceName, @LockOwner = 'Transaction'; COMMIT |
But there’s a problem with this pattern, especially when using RCSI. After sp_releaseapplock is called, but before the COMMIT completes, another process running the same code can read the previous state. In the example above, both processes will think a time slot is available and will try to make the same reservation.
What I really want is to release the applock after the commit. But because I specified the lock owner is 'Transaction'. That gets done automatically when the transaction ends! So really what I want is this:
BEGIN TRAN exec sp_getapplock @Resource = @LockResourceName, @LockMode = 'Exclusive', @LockOwner = 'Transaction'; /* read stuff (e.g. "is time slot available?") */ /* change stuff (e.g. "make reservation") */ COMMIT -- all locks are freed after this commit |
