CODEWITHSUNDEEP
sql-serverstored-procedureslocking
Tom Regan
Tom Regan

Reputation: 3851

Does sp_getapplock cause SQL Server performance problems?

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.

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.

Anywhere from one to a dozen instances of the calling process could exist, depending upon how much work exists.

I decided to prevent concurrency by using sp_GetAppLock inside the stored procedure. I grab an exclusive transaction lock, with @Resource 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.

The call inside the stored procedure looks like this:

     sp_getapplock @Resource='My Unique String Here' 
         ,@LockMode='Exclusive'  -- Type of lock
         ,@LockOwner='Transaction' -- Transaction or Session
         ,@LockTimeout = 5000

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.

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 sp_getapplock triggers a lot of alerts. My DBA claims that the blocking in-and-of-itself is a performance problem.

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 want that to be blocked. But my DBA says that forcing SQL Server to enforce this blocking is a significant drain on resources.

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 sp_getapplock?

The article I read which sold me on sp_getapplock is here: sp_getapplock

Upvotes: 4

Views: 5098

Answers (2)

Jonathan Roberts
Jonathan Roberts

Reputation: 476

"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."
Here is a different way to do it inside the SP:

BEGIN TRANSACTION
 SELECT x.PKCol
   FROM dbo.[myTable] x WITH (FASTFIRSTROW XLOCK ROWLOCK READPAST)
  WHERE x.col1 = @col1...

 IF @@ROWCOUNT > 0 BEGIN
    UPDATE dbo.[myTable]
       SET ...
     WHERE x.col1 = @col1
 END

COMMIT TRANSACTION

XLOCK Specifies that exclusive locks are to be taken and held until the transaction completes. If specified with ROWLOCK, PAGLOCK, or TABLOCK, the exclusive locks apply to the appropriate level of granularity.

Upvotes: 3

M.Ali
M.Ali

Reputation: 69564

Unfortunately, I think your DBA has a point, blocking does drain resources and this type of blocking is putting extra load on the server.

Let me explain how:

  1. Proc gets called, SQL Server assigns worker thread from the Thread pool to it and it starts executing.

  2. Call 2,3,4,... comes in, again SQL Server assigns worker threads to these calls, the Threads starts executing but because of the exclusive locks you have obtained, all the threads get suspended and sitting in the "Waiting List" for resources to become available.

  3. Worker Threads which are very limited in numbers on any SQL Server are being held because of your process.

  4. Now SQL Server is accumulating waits because of something a developer decided to do.

As a DBA we want you to come to SQL Server get what you need and leave it as soon as possible. If you are intentionally staying there and holding on to resources and putting SQL Server under pressure, it will piss off the DBA.

I think you need to reconsider your application design and come up with an alternative solution.

Maybe a "Process Table" in the SQL Server, update it with some value when a process start and for each call check the process table first before you fire the next call for that proc. So the wait stuff happens in the application layer and only when the resources are available then go to DB.

Upvotes: 7

Related Questions