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