Databases
StackOverflow2013
Postsdbo
POConflict between UPDATE and SELECT
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POConflict between UPDATE and SELECT
    primarykey
    data
    text
    <p>I have a table <code>DB.DATA_FEED</code> that I update using a T/SQL Procedure. Every minute, the procedure below is executed 100 times for different data.</p> <pre><code>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 &gt; 0 COMMIT WORK END TRY BEGIN CATCH --error code END CATCH END END </code></pre> <p>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)</p> <pre><code>DECLARE @MYDATE AS DATE; SET @MYDATE='?' SELECT * FROM DB.DATA_FEED WHERE MARKET_DATE&gt;=@MYDATE AND MARKET_DATE&lt;DATEADD(D,1,@MYDATE) AND CURR1 = ? AND CURR2 = ? AND PERIOD = ? ORDER BY PERIOD </code></pre> <p>I have sometimes, albeit rarely, got a database lock. </p> <p>Using the the script from <a href="http://sqlserverplanet.com/troubleshooting/blocking-processes-lead-blocker" rel="nofollow">http://sqlserverplanet.com/troubleshooting/blocking-processes-lead-blocker</a> 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. </p> <p>Is there something wrong with my SQL code? What can I do to prevent such locks happening in the future?</p> <p>Thanks</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USgordon613
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POConflict between UPDATE and SELECT
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POConflict between UPDATE and SELECT
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POConflict between UPDATE and SELECT
      1. USDavid Söderlund
      1. VTFavorite
    1. COHave you considered running your SELECTs using read committed snapshot?
      singulars
      1. POConflict between UPDATE and SELECT
      1. USAaron Bertrand
    2. COI didn't know about it... Are you however suggesting that I do 'ALTER DATABASE DB SET READ_COMMITTED_SNAPSHOT ON' or on a query-by-query level.
      singulars
      1. POConflict between UPDATE and SELECT
      1. USgordon613
    3. COWell it has to be enabled at the database level, and then explicitly set for specific queries (the database level setting just makes it possible). You should of course test this on your overall workload, I wasn't suggesting just turning it on. If dirty reads are acceptable (which by the sounds of how often the selects are being run, it is quite possible) you could also consider read uncommitted.
      singulars
      1. POConflict between UPDATE and SELECT
      1. USAaron Bertrand
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload