How to check who is holding a lock that I would like to grant?

Question

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.

For such requirement, I would like to use sp_getapplock to grant an exclusive lock with name as the unique customer ID when user A starts editing the customer data. Then when another user try to edit, system will return that the lock could not be granted because user A is holding it.

Below is my expected SQL:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION

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
ELSE
    --The lock is holding by someone, return the SPID who holds the lock
COMMIT TRANSACTION

How can I retrieve the SPID which is holding the lock? I know that sys.sysprocesses and sp_who can get the block by SPID, but in my case that the sql statement is required to return immediately without blocking, and get who is holding the lock it tries to grant. Is it possible and how to do so? I appreciate any suggestions and advise:-)

Answer 1

To get the session id holding the application lock you can query sys.dm_tran_locks

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 '\' 

Where 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 user_name at 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.