Introduction
In multi-user database environments, ensuring the integrity and consistency of data is essential. One common challenge is preventing multiple instances of the same stored procedure from executing at the same time, which can lead to data conflicts, race conditions, and unpredictable results. To address this, SQL Server provides a powerful tool called sp_getapplock
.
sp_getapplock
allows you to define and control your own logical resources, independent of physical database objects like tables and rows. By doing so, you can coordinate access to key sections of code, ensuring that only one process at a time can run a particular portion of logic—even if that logic spans multiple data operations. This post explains what sp_getapplock
is, what you are actually locking when you use it, how to implement it to prevent concurrent executions of a stored procedure, and the difference between transaction-level and session-level locks.
Understanding What sp_getapplock Locks
When you use sp_getapplock
, you are not locking database rows, tables, or other traditional database objects. Instead, you are locking a conceptual resource defined by a resource name you choose. This resource name is simply a string—such as 'ProcessOrderLock'
—and it represents a logical lock token that SQL Server can manage on your behalf.
Think of it as creating a named “mutex” or “semaphore” within the database environment. If two sessions attempt to acquire the same named lock with conflicting modes (like both wanting exclusive access), only one session can hold it at a time. You are effectively controlling concurrency at the application level, not the physical data level.
For example, if you have a stored procedure called ProcessOrder
that must never run concurrently, you can associate it with a logical resource named 'ProcessOrderLock'
. When one session calls ProcessOrder
, it requests the 'ProcessOrderLock'
. As long as that lock is held, no other session can run ProcessOrder
and also acquire that lock, thus preventing concurrent execution.
Transaction-Level vs. Session-Level Locks
When calling sp_getapplock
, you can specify the lock owner. The two most common approaches are:
- Transaction-Level Lock (
@LockOwner = 'Transaction'
):
The lock is owned by the current transaction. If there is no active transaction, you must start one before callingsp_getapplock
. The lock is automatically released when the transaction is committed or rolled back. This is useful if you want the lock’s lifetime to exactly match the transaction’s lifetime, ensuring that the locked code and the data changes occur as a single atomic unit. - Session-Level Lock (
@LockOwner = 'Session'
):
The lock is owned by the session and does not require an active transaction. You must explicitly release the lock usingsp_releaseapplock
. Session-level locks are simpler if you just need concurrency control without tying it directly to a transactional scope.
Which one to choose?
Use a transaction-level lock if the lock should be tightly coupled with a transaction, so that when the transaction finishes (commit or rollback), the lock is automatically released.
Use a session-level lock if you prefer to manage the lock’s lifetime yourself and do not necessarily want to start a transaction.
Example: Preventing Concurrent Executions Using a Transaction-Level Lock
Let’s consider a stored procedure named ProcessOrder
that processes customer orders. This operation might update various tables, send notifications, or run through complex business logic that should never be performed by two sessions at the same time.
In this example, we’ll use a transaction-level lock. That means we must start a transaction before acquiring the lock:
CREATE PROCEDURE ProcessOrder
@OrderID INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @LockResult INT;
-- Start a transaction to hold the lock in
BEGIN TRAN;
-- Attempt to acquire an exclusive application-level lock on the logical resource "ProcessOrderLock"
EXEC @LockResult = sp_getapplock
@Resource = 'ProcessOrderLock',
@LockMode = 'Exclusive',
@LockOwner = 'Transaction', -- Tied to this transaction
@LockTimeout = 10000; -- Wait up to 10 seconds
IF @LockResult < 0
BEGIN
RAISERROR('Could not acquire application-level lock (ProcessOrderLock).', 16, 1);
ROLLBACK;
RETURN;
END
BEGIN TRY
-- Code that should never run concurrently goes here:
UPDATE Orders
SET Status = 'Processing'
WHERE OrderID = @OrderID;
WAITFOR DELAY '00:00:05'; -- Simulate some processing time
UPDATE Orders
SET Status = 'Completed'
WHERE OrderID = @OrderID;
-- Commit the transaction. This automatically releases the lock.
COMMIT;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
RAISERROR(@ErrorMessage, 16, 1);
-- Rollback the transaction which also releases the lock
ROLLBACK;
END CATCH
END
How It Works with Transaction-Level Lock
- Lock Acquisition: Before running the main logic, the procedure starts a transaction. It then attempts to acquire the lock named
'ProcessOrderLock'
. Because@LockOwner = 'Transaction'
, the lock is associated with the current transaction. - Exclusive Mode: Setting
@LockMode = 'Exclusive'
means only one session can hold this lock at any given time. If another session tries to runProcessOrder
while the lock is held, it will wait up to 10 seconds. If it still can’t get the lock, it returns an error. - Protected Code Section: Once the lock is acquired, the procedure executes the code that must not be run concurrently. During this time, no other session can hold
'ProcessOrderLock'
and therefore cannot run the same code. - Lock Release on Transaction End: If the code in the
TRY
block completes successfully, the lock is automatically released when the transaction is committed. If an error occurs, theCATCH
block fires, raises the error, and rolls back the transaction, which also releases the lock. In either case, the lock’s lifetime matches the transaction’s lifetime.
Using a Session-Level Lock Instead (An Alternative Approach)
If you don’t want to tie the lock to a transaction, you can specify @LockOwner = 'Session'
. In that case, you don’t need to start a transaction first. However, you must explicitly release the lock using sp_releaseapplock
:
EXEC @LockResult = sp_getapplock
@Resource = 'ProcessOrderLock',
@LockMode = 'Exclusive',
@LockOwner = 'Session',
@LockTimeout = 10000;
IF @LockResult < 0
BEGIN
RAISERROR('Could not acquire application-level lock (ProcessOrderLock).', 16, 1);
RETURN;
END
-- Protected code here
EXEC sp_releaseapplock
@Resource = 'ProcessOrderLock',
@LockOwner = 'Session';
With a session-level lock, the lock must be released explicitly, and you are free to start or not start a transaction at any point within the protected code.
Benefits of Using sp_getapplock
- Logical Concurrency Control: You define the logical unit of work that must not overlap, rather than relying solely on data-level locking.
- Flexible Resource Naming: Since the locked resource is identified by a name you choose, it can represent any operation or workflow, not just a table or row.
- Better Data Integrity: By ensuring only one execution runs at a time, you prevent inconsistent or partial updates that might occur under concurrent execution.
- Avoids Complex Workarounds: Instead of implementing external coordination mechanisms, you can rely on SQL Server’s built-in lock manager for these logical resources.
Best Practices
- Meaningful Resource Names: Use descriptive names (e.g.,
'ProcessOrderLock'
) to clearly indicate what resource or operation is being serialized. - Appropriate Lock Modes and Timeouts: Set the lock mode (Exclusive, Shared, Update) and timeouts that fit your concurrency control needs without causing unnecessary waits or failures.
- Consider Lock Ownership Options: Decide between transaction-level or session-level locks based on whether you want the lock to track a transaction or remain independent.
- Monitor and Test: Monitor lock usage to detect contention or deadlocks. Test thoroughly under various load conditions.
Potential Considerations
- Deadlocks: Although less common if planned well, poorly implemented application-level locks can still contribute to deadlocks, just like data locks.
- Overhead: Acquiring and holding locks has a cost, so use
sp_getapplock
only where it’s truly needed. - Complexity: Adding logical locks can increase complexity. Document the reasoning behind each lock and ensure your team understands its purpose.
Conclusion
sp_getapplock
empowers you to define and control concurrency at the application level within SQL Server. Whether you tie the lock’s lifetime to a transaction or manage it at the session level, you can tailor concurrency control to fit your application’s needs.
By choosing the appropriate lock owner and properly acquiring and releasing the lock, you ensure that only one session at a time can execute the protected logic, improving data integrity, consistency, and reliability in your SQL Server environment.
Great post! One of my favorite use cases for using applocks is for background processes. Most of the time, user facing procs are written to handle concurrency just fine.
However, background jobs like things that cleanup tables, import data, etc. Those I want to ensure they don’t accidentally get kicked off twice…for example, maybe it gets kicked off by a SQL Agent job, but then someone manually runs the proc themselves, I want it to wait or throw an error.
So I’ll occasionally use applocks for those background service / maintenance job type things, and it works great.
Another cool thing you can do is dynamically set the lock name for more granular control. For example, you can combine a static string with the username so that the code can be run concurrently for all uses, but an individual user cannot run concurrent calls.
(FYI, found your blog post from Brent Ozar’s weekly email, nice job!)
Thank you for the kind words! I particularly like your idea of dynamically setting the lock name. I can definitely see use cases for doing this.
I first have to say that this is a very well written article. Nice work, Stephen!
On a different note…
About 5 or 6 years ago, we implemented sp_GetApplock for a couple of things. Perhaps we did something wrong or perhaps it was simply used on the wrong things but our server became mostly paralyzed by it. 30 out of 32 core were stuck using it and the PerfMon chart showed the related huge number of blocks going on. We had another tool to show what’s running similar to Adam Machanic’s “sp_WhoIsActive” and it showed the same thing with sp_GetApplock being what was running.
I’m not saying “don’t use it” but I will say “Look eye”. Nothing is a panacea and sometimes a great idea or tool used by one group will absolutely kill another group.
Thank you Jeff! It definitely isn’t a one size fits all solution, but its a nice tool to have in the toolbox. That’s why we always test our changes prior to deploying to production. 🙂