CODEWITHSUNDEEP
t-sqlsql-server-2012
gordon613
gordon613

Reputation: 2952

Conflict between UPDATE and SELECT

I have a table DB.DATA_FEED that I update using a T/SQL Procedure. Every minute, the procedure below is executed 100 times for different data.

ALTER PROCEDURE [DB].[UPDATE_DATA_FEED]  
   @P_MARKET_DATE varchar(max),
   @P_CURR1 int,
   @P_CURR2 int,
   @P_PERIOD float(53),
   @P_MID float(53)
AS 

   BEGIN

      BEGIN TRY

         UPDATE DB.DATA_FEED
            SET 
               MID = @P_MID, 
               MARKET_DATE = convert(datetime,@P_MARKET_DATE, 103)
            WHERE 
               cast(MARKET_DATE as date) = 
               cast(convert(datetime,@P_MARKET_DATE, 103) as date) AND 
            CURR1 = @P_CURR1 AND 
            CURR2 = @P_CURR2 AND 
            PERIOD = @P_PERIOD

         IF @@TRANCOUNT > 0
            COMMIT WORK 

      END TRY

      BEGIN CATCH

         --error code

      END CATCH

   END

 END

When Users use the application, then they also read from this table, as per the SQL below. Potentially this select can run thousands of times in one minute. (Questions marks are replaced by parser with appropriate date/numbers)

DECLARE @MYDATE AS DATE;
SET @MYDATE='?'
SELECT *
FROM DB.DATA_FEED
WHERE MARKET_DATE>=@MYDATE AND MARKET_DATE<DATEADD(D,1,@MYDATE)
AND CURR1 = ?
AND CURR2 = ?
AND PERIOD = ?

ORDER BY PERIOD

I have sometimes, albeit rarely, got a database lock.

Using the the script from http://sqlserverplanet.com/troubleshooting/blocking-processes-lead-blocker I saw it was SPID=58. I then did DECLARE @SPID INT; SET @SPID = 58; DBCC INPUTBUFFER(@SPID) to find the SQL script which turned out to be my select statement.

Is there something wrong with my SQL code? What can I do to prevent such locks happening in the future?

Thanks

Upvotes: 2

Views: 212

Answers (1)

Pimenta
Pimenta

Reputation: 1109

Readers have priority over writers so when someone is writing the readers have to wait for the writing to finish. There are two Table Hints you ca try one is NOLOCK that reads uncommited lines (dirty reads) and the other is READPAST (only reads information that has been commited on the last commit). In both cases the readers never block the table, there for do not deadlock a writer.

Writers can block other writers but, if I understood correctly, only one write per execution so the readers will intercalate writes, diminuishing the deadlocks.

Hope it helps.

Upvotes: 0

Related Questions