title: How to check who is holding a lock that I would like to grant?
body: <p>Consider the below case:</p>
  
  <blockquote>
    <p>Some users in a company may read and update customers data in
    database (from a front-end application).</p>
    
    <p>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).</p>
    
    <p>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.</p>
  </blockquote>
  
  <p>For such requirement, I would like to use <code>sp_getapplock</code> to grant an exclusive lock with name as the <em>unique customer ID</em> 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.</p>
  
  <p>Below is my expected SQL:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>How can I retrieve the SPID which is holding the lock? I know that <code>sys.sysprocesses</code> and <code>sp_who</code> can get the <em>block by</em> 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:-)</p>