3

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?

6

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

  • The lock could be coming from the update. – Anthony Genovese Dec 27 '19 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.