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] &lt; [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;