This post has already been read 120 times!
There is a stored procedure that must be run by only one user at a time. How do to I prevent multiple users from running the stored procedure at the same time?
SQL Server provides an application manageable lock mechanism through the sp_getapplock / sp_releaseapplock pair of system stored procedures. They provide a way for application code to use SQL's underlying locking mechanism, without having to lock database rows. The lock can be tied to a transaction or session ensuring lock release when the transaction COMMITs or ROLLSBACK or when the session exits and the connection is closed.
Using sp_getapplock to lock a resource
To obtain a lock, call sp_getapplock as follows:
Using sp_releasapplock to release a resource
The call to sp_releaseapplock requires the @Resource and @LockOwner parameters and looks like this:
However, if @LockOwner='Transaction' then sp_releaseapplock must be executed inside the transaction. In addition, if the lock isn't held by the transaction SQL Server doesn't just return a return code, an explicit error is thrown and the code must account for that possibility.
For that reason when using @LockOwner='Transaction' I avoid calling sp_releaseapplock but instead rely on the transaction COMMIT to release the lock.
Stored Procedure Example Using sp_getapplock
The lock is held for the duration of the transaction and will be released either by the COMMIT at the end of the TRY block or by the ROLLBACK in the CATCH block. You might notice the use of RAIERROR... WITH NOWAIT instead of PRINT. RAISERROR with a error code of zero isn't really an error and adding the WITH NOWAIT forces the message and any preceding messages to be displayed immediately. I described this feature in detail in the this tip Using the NOWAIT option with the SQL Server RAISERROR statement.