title: Does sp_getapplock cause SQL Server performance problems?
body: <p>I have a stored procedure which cannot be executed concurrently. Multiple processes call this stored procedure, but it is of vital importance that the processes access the stored procedure sequentially.</p>
  
  <p>The stored procedure basically scans a table for a primary key that meets various conditions, marks the record as in-use by the calling process, and then passes the primary key back to the calling process.</p>
  
  <p>Anywhere from one to a dozen instances of the calling process could exist, depending upon how much work exists.</p>
  
  <p>I decided to prevent concurrency by using <code>sp_GetAppLock</code> inside the stored procedure. I grab an exclusive transaction lock, with <code>@Resource</code> set to a string that is only used inside this stored procedure. The only thing that is ever blocked by this lock is the execution of this stored procedure.</p>
  
  <p>The call inside the stored procedure looks like this:</p>
  
  <pre><code>     sp_getapplock @Resource='My Unique String Here' 
           ,@LockMode='Exclusive'  -- Type of lock
           ,@LockOwner='Transaction' -- Transaction or Session
           ,@LockTimeout = 5000
  </code></pre>
  
  <p>It works swimmingly. If a dozen instances of my process are running, only one of them executes the stored procedure at any one point in time, while the other 11 obediently queue up and wait their turn.</p>
  
  <p>The only problem is our DBA. He is a very good DBA who constantly monitors the database for blocking and receives an alert when it exceeds a certain threshold.  My use of <code>sp_getapplock</code> triggers a lot of alerts. My DBA claims that the blocking in-and-of-itself is a performance problem.</p>
  
  <p>Is his claim accurate? My feeling is that this is "good" blocking, in that the only thing being blocked is execution of the stored procedure, and I <strong>want</strong> that to be blocked. But my DBA says that forcing SQL Server to enforce this blocking is a significant drain on resources.</p>
  
  <p>Can I tell him to "put down the crack pipe," as we used to say? Or should I re-write my application to avoid the need for <code>sp_getapplock</code>?  </p>
  
  <p>The article I read which sold me on <code>sp_getapplock</code> is here: <a href="https://www.mssqltips.com/sqlservertip/3202/prevent-multiple-users-from-running-the-same-sql-server-stored-procedure-at-the-same-time" rel="nofollow noreferrer">sp_getapplock</a> </p>