Consider the below case:
Some users in a company may read and update customers data in
database (from a front-end application).
To avoid duplicate update of same customer, if user A begins modifying data of a specific customer, no one can modify data of this customer until user A finishes and updates to database (user A holds a exclusive lock of this customer data).
If another user try to modify the same customer data, system will return immediately with errors indicating user A is already modifying data of this customer.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @result INT
EXEC @result = sp_getapplock
@Resource = 'UniqueCustomerID', @LockMode = 'Exclusive', @LockTimeout = 0
IF @result = 0
--Successful grant the lock, perform the data update action here
--The lock is holding by someone, return the SPID who holds the lock
To get the session id holding the application lock you can query
SELECT request_session_id FROM sys.dm_tran_locks WHERE resource_type = 'APPLICATION' AND request_mode = 'X' AND request_status = 'GRANT' AND resource_description LIKE '%:\[UniqueCustomerID\]:%' ESCAPE '\'
UniqueCustomerID is up to 32 characters (more will be truncated).
But this requires
VIEW SERVER STATE permission. So you might need to wrap this up in a stored procedure that uses code signing or
EXECUTE AS to temporarily elevate the permissions.
Rolling your own scheme as suggested by Mitch shouldn't be too tricky and avoids that problem.
You could have a table with a row per customer and a column for
user_name then instead of calling
sp_getapplock the beginning of each transaction attempts to update the
SET LOCK_TIMEOUT 0; and (if that fails) then you can read the user_name contained in that row at read uncommitted. I would also have each transaction set its
user_name column back to
NULL before committing. If the read of user_name returns
NULL you would then know to retry.
The table would need to be indexed on
CustomerId to locate the row without bumping into locks for other customers.