Mango Wong Mango Wong - 1 year ago 44
SQL Question

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

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
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:


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

How can I retrieve the SPID which is holding the lock? I know that
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 Source

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.