SQL Deadlocks - Same Stored Procedure Called Concurrently

Question

I am working with a relatively complex system, where static tables are updated from multiple data sources as they are needed, because dynamically loading the data takes 4-5 seconds and we prefer to display results to the user quickly.

The process:

1. User or application opens a specific order
2. A stored procedure is called to retreive the data (lets call it dbo.Get_Data)
3. dbo.Get_Data checks event logs to see if there have been any changes since the last time it looked for the data
4. If it finds a new event, it runs the expensive query to update the data
5. Data is returned

IF (
    SELECT [LastStaticUpdateEvent] < [LastSaveEvent] 
    FROM [dbo].[Events] 
    WHERE [OrderNumber] = @OrderNumber
)
BEGIN
    -- Update Static table
    UPDATE [Static]
    SET [Static].[A] = [App].[A]
        ,[Static].[B] = [App].[B]
    FROM [dbo].[AppData] AS [App] -- View with many joins (4-5 secs)
    INNER JOIN [dbo].[StaticResuts] AS [Static]
        ON [Static].[OrderNumber] = [App].[OrderNumber]
    WHERE [App].[OrderNumber] = @OrderNumber

    -- Update Event Log
    UPDATE [dbo].[Events]
    SET [LastStaticUpdateVent] = SYSDATETIME()
    WHERE [OrderNumber] = @OrderNumber
END

SELECT * FROM [dbo].[StaticResults]

The problem is that this data can be requested concurrently. If User A and User B both call dbo.Get_Data before the first UPDATE is complete, a deadlock occurs.

Is there a good method or pattern to have the 2nd call wait until the 1st is finished before continuing with its lookups?

Answer 1

Is there a good method or pattern to have the 2nd call wait until the 1st is finished before continuing with its lookups?

The easiest way to have a block of code that can be run by only one session at a time is to use an Application Lock. You can use SQL Server's locking engine, but instead of locking a specific row, page, or table, you create a lock with a custom name. eg

begin transaction;

--begin exclusive section
exec sp_getapplock @Resource = 'Get_Data Exclusive Lock', @LockMode = 'Exclusive';  

--do stuff in only one session

--end exclusive section
exec sp_releaseapplock @Resource = 'Get_Data Exclusive Lock';  


commit transaction;

Answer 2

The simple fix could be WITH (NOLOCK):

SELECT * FROM [dbo].[StaticResults] WITH (NOLOCK)

Which means that you are not blocked by the concurrent transactions in process and process will read the table data even though the lock is there on the table. The consequence may be users will not see the updated data in that attempt. As long as it is not a financial transaction or business-critical transactions it will serve the purpose.