SQL Deadlocks - Same Stored Procedure Called Concurrently

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:

• User or application opens a specific order

• A stored procedure is called to retreive the data (lets call it dbo.Get_Data)

• dbo.Get_Data checks event logs to see if there have been any changes since the last time it looked for the data

• If it finds a new event, it runs the expensive query to update the data

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

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;

StackExchange Database Administrators Q#255585, answer score: 9