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