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

consider below case:

some users in company may read , update customers data in database (from front-end application).

to avoid duplicate update of same customer, if user begins modifying data of specific customer, no 1 can modify data of customer until user finishes , updates database (user holds exclusive lock of customer data).

if user try modify same customer data, system return errors indicating user modifying data of customer.

for such requirement, use sp_getapplock grant exclusive lock name unique customer id when user starts editing customer data. when user try edit, system return lock not granted because user holding it.

below 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 lock, perform data update action here else     --the lock holding someone, return spid holds lock commit transaction 

how can retrieve spid holding lock? know sys.sysprocesses , sp_who can block by spid, in case sql statement required return without blocking, , holding lock tries grant. possible , how so? appreciate suggestions , advise:-)

to session id holding application lock can query sys.dm_tran_locks

select request_session_id   sys.dm_tran_locks  resource_type = 'application'        , request_mode = 'x'        , request_status = 'grant'        , resource_description '%:\[uniquecustomerid\]:%' escape '\'  

where uniquecustomerid 32 characters (more truncated).

but requires view server state permission. might need wrap in stored procedure uses code signing or execute as temporarily elevate permissions.

rolling own scheme suggested mitch shouldn't tricky , avoids problem.

you have table row per customer , column user_name instead of calling sp_getapplock beginning of each transaction attempts update user_name @ set lock_timeout 0; , (if fails) can read user_name contained in row @ read uncommitted. have each transaction set user_name column null before committing. if read of user_name returns null know retry.

the table need indexed on customerid locate row without bumping locks other customers.