Sql-server – sp_GetAppLock slow performance unusual behaviour

lockingsql server

We had a critical issue yesterday where by a 1 second stored procedure started to take minutes to execute. I don't know the business logic as of yet, however want to understand the impact of using sp_GetAppLock.

So scenario is:

Stored procedure called usp_abc, no parameters

Code within stored procedure looks like this:

BEGIN
BEGIN TRANSACTION

sp_getapplock @Resource = usp_abc, @LockMode = 'Exclusive'

with cte_1
( SELECT TOP 1 ID FROM tbl1 WHERE Status = 'OK')

UPDATE tbl1
SET Status = 'Complete'
WHERE ID = (SELECT ID FROM cte_1)

COMMIT TRAN

END

This stored procedure is called from the same application but on a load balancer from two different application servers. The stored procedure is constantly executed, yesterday when we had the issue more so (multiple times per second) because of backlog.

What I was seeing yesterday when querying sp_who2 was the execution of the stored procedure was being blocked by earlier executions. There were around 5 SPIDs doing the same thing. I traced back through the first SPID to take a look at resource wait. It fluctuated between SOS_SCHEDULER_YIELD and APPLICATION:5.0 "usp_abc" – basically itself.

It looked like it had got itself in a nasty loop, however the executions sometimes did complete. When running the update on its own without the lock it ran sub second as expected.

Once the load balancer was switched off and requests were coming from a single source the issue went away at the query took sub second.

This isn't our code, however we want to make recommendations on how to stop this occurring again. From what I can tell it looks like the developer put this in place for concurrency reasons and possibly to avoid deadlocks.

However judging by the code the native sql read committed should suffice in this situation because it should lock the data being updated meaning it is not possible to update the same record twice. I don't think deadlocking will be an issue with this query.

What are your thoughts?

Best Answer

Try adding SET_XACT_ABORT ON to the stored procedure to ensure the transaction is rolled back following termination due to an attention request. This is a best practice for procs with explicit transactions.

A scenario that could cause the symptoms you describe is that the proc execution timed out during execution due to blocking on the table. When a client timeout occurs, the client API cancels the proc by sending an attention event to stop execution. This cancels the executing proc but does not roll back the transaction if SET_XACT_ABORT OFF. The transaction will remain open until the connection is closed and reused (in the case of a connection pool), the connection is physically closed, or an explicit rollback or commit is issued on the same connection.

This is especially nasty with persistent (non-pooled) connections that don't properly clean up after an exception because all subsequent work performed on the connection is unwittingly done under the context of the open transaction.

Related Question