Databases
StackOverflow2013
Postsdbo
POupdate and insert queries creating a deadlock
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POupdate and insert queries creating a deadlock
    primarykey
    data
    text
    <p>I will try to explain my problem as detailed as possible, and i would appreciate any help/suggestion. My problem is regarding a deadlock being caused by two queries (one insert and one update). I'm using MS-SQL server 2008</p> <p>I have two applications using the same database:</p> <ol> <li>Web app (on every request multiple records are inserted in the Impressions table by calling a stored procedure)</li> <li>Windows service (calculates all the Impressions done in one minute, every minute, for the previous minute and sets a flag on each of the Impressions calculated via a stored procedure as well)</li> </ol> <p>The web app inserts the impressions records without using a transaction, while the windows service application calculates the impressions while using a <code>IsolationLevel.ReadUncommitted</code> transaction. The stored procedure in the windows service app does something like this:</p> <p><strong>Windows Service stored procedure:</strong></p> <p>Loops trough all the impressions that have the <code>isCalculated</code> flag set to false and date &lt; @now , increments a counter and other data in another table connected to the impressions table, and sets the <code>isCalculated</code> flag to true on impressions that have date &lt; @now. Because this stored procedure is pretty big, no point in pasting it, here is a shortened code snippet of what the proc does:</p> <pre><code>DECLARE @nowTime datetime = convert(datetime, @now, 21) DECLARE dailyCursor CURSOR FOR SELECT Daily.dailyId, Daily.spentDaily, Daily.impressionsCountCache , SUM(Impressions.amountCharged) as sumCharged, COUNT(Impressions.impressionId) as countImpressions FROM Daily INNER JOIN Impressions on Impressions.dailyId = Daily.dailyId WHERE Impressions.isCharged=0 AND Impressions.showTime &lt; @nowTime AND Daily.isActive = 1 GROUP BY Daily.dailyId, Daily.spentDaily, Daily.impressionsCountCache OPEN dailyCursor DECLARE @dailyId int, @spentDaily decimal(18,6), @impressionsCountCache int, @sumCharged decimal(18,6), @countImpressions int FETCH NEXT FROM dailyCursor INTO @dailyId,@spentDaily, @impressionsCountCache, @sumCharged, @countImpressions WHILE @@FETCH_STATUS = 0 BEGIN UPDATE Daily SET spentDaily= @spentDaily + @sumCharged, impressionsCountCache = @impressionsCountCache + @countImpressions WHERE dailyId = @dailyId FETCH NEXT FROM dailyCursor INTO @dailyId,@spentDaily, @impressionsCountCache, @sumCharged, @countImpressions END CLOSE dailyCursor DEALLOCATE dailyCursor UPDATE Impressions SET isCharged=1 WHERE showTime &lt; @nowTime AND isCharged=0 </code></pre> <p><strong>Web App Stored Procedure:</strong></p> <p>This procedure is pretty simple it just inserts the record in the table. Here is a shortened code snippet:</p> <pre><code>INSERT INTO Impressions (dailyId, date, pageUrl,isCalculated) VALUES (@dailyId, @date, @pageUrl, 0) </code></pre> <p><strong>The Code</strong></p> <p>The code that calls these stored procedures is pretty simple it just creates the SQL commands passing the needed parameters and executes them</p> <pre><code>//i send the date like this string date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture); SqlCommand comm = sql.StoredProcedureCommand("storedProcName", parameters, values); </code></pre> <p>I'm experiencing deadlocks very often (the exceptions occur in the web app, not the windows service), and after using the SQL-Profiler, I found out that the deadlocks are probably happening because of these two queries (I don't have much experience in analyzing profiler data).</p> <p>The latest trace data collected from the SQL server profiler can be found on the bottom of this question</p> <p>In theory these two stored procedures should be able to work together because the first one inserts the records one by one with date=DateTime.Now, and the second one calculates the Impressions that have date &lt; DateTime.Now.</p> <p>Edit:</p> <p>Here is the code run in the windows service app:</p> <pre><code>SQL sql = new SQL(); DateTime endTime = DateTime.Now; //our custom DAL class that opens a connection sql.StartTransaction(IsolationLevel.ReadUncommitted); try { List&lt;string&gt; properties = new List&lt;string&gt;() { "now" }; List&lt;string&gt; values = new List&lt;string&gt;() { endTime.ToString("yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture) }; SqlCommand comm = sql.StoredProcedureCommannd("ChargeImpressions", properties, values); comm.Transaction = sql.Transaction; ok = sql.CheckExecute(comm); } catch (Exception up) { ok = false; throw up; } finally { if (ok) sql.CommitTransaction(); else sql.RollbackTransactions(); CloseConn(); } </code></pre> <p>EDIT:</p> <p>I added the indexes on both of the tables as suggested by Martin Smith like this:</p> <pre><code>CREATE NONCLUSTERED INDEX [IDX_Daily_DailyId] ON [dbo].[Daily] ( [daily] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO </code></pre> <p>and</p> <pre><code>CREATE NONCLUSTERED INDEX [IDX_Impressions_isCharged_showTime] ON [dbo].[Impressions] ( [isCharged] ASC, [showTime] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO </code></pre> <p>For now no exceptions, will report back later</p> <p>Edit:</p> <p>Unfortunately this did not solve the deadlock issue. I will start a deadlock trace in profiler to see if the deadlocks are the same as before.</p> <p>Edit: </p> <p>Pasted the new trace (to me it looks the same as the previous one), couldn't capture a screen of the execution plan (its too big) but <a href="http://pastebin.com/PjBfwuLc" rel="nofollow noreferrer">here is the xml from the execution plan</a>.And here is a screenshot of the execution plan of the insert query:</p> <p><img src="https://i.stack.imgur.com/wLxa0.jpg" alt="execution plan of the insert query"></p> <pre><code> &lt;deadlock victim="process14e29e748"&gt; &lt;process-list&gt; &lt;process id="process14e29e748" taskpriority="0" logused="952" waitresource="KEY: 6:72057594045071360 (f473d6a70892)" waittime="4549" ownerId="2507482845" transactionname="INSERT" lasttranstarted="2011-09-05T11:59:16.587" XDES="0x15bef83b0" lockMode="S" schedulerid="1" kpid="2116" status="suspended" spid="65" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-09-05T11:59:16.587" lastbatchcompleted="2011-09-05T11:59:16.587" clientapp=".Net SqlClient Data Provider" hostpid="2200" isolationlevel="snapshot (5)" xactid="2507482845" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"&gt; &lt;executionStack&gt; &lt;frame procname="dbo.InsertImpression" line="27" stmtstart="2002" stmtend="2560" sqlhandle="0x03000600550e30512609e200529f00000100000000000000"&gt; INSERT INTO Impressions (dailyId, languageId, showTime, pageUrl, amountCharged, age, ipAddress, userAgent, portalId, isCharged,isCalculated) VALUES (@dailyId, @languageId, @showTime, @pageUrl, @amountCharged, @age, @ip, @userAgent, @portalId, 0, 0) &lt;/frame&gt; &lt;/executionStack&gt; &lt;inputbuf&gt; Proc [Database Id = 6 Object Id = 1362103893] &lt;/inputbuf&gt; &lt;/process&gt; &lt;process id="process6c9dc8" taskpriority="0" logused="335684" waitresource="KEY: 6:72057594045464576 (5fcc21780b69)" waittime="4475" ownerId="2507482712" transactionname="transaction_name" lasttranstarted="2011-09-05T11:59:15.737" XDES="0x1772119b0" lockMode="U" schedulerid="2" kpid="3364" status="suspended" spid="88" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-09-05T11:59:15.737" lastbatchcompleted="2011-09-05T11:59:15.737" clientapp=".Net SqlClient Data Provider" hostpid="1436" isolationlevel="read uncommitted (1)" xactid="2507482712" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"&gt; &lt;executionStack&gt; &lt;frame procname="dbo.ChargeImpressions" line="60" stmtstart="4906" stmtend="5178" sqlhandle="0x03000600e3c5474f0609e200529f00000100000000000000"&gt; UPDATE Impressions SET isCharged=1 WHERE showTime &amp;amp;lt; @nowTime AND isCharged=0 &lt;/frame&gt; &lt;/executionStack&gt; &lt;inputbuf&gt; Proc [Database Id = 6 Object Id = 1330103779] &lt;/inputbuf&gt; &lt;/process&gt; &lt;/process-list&gt; &lt;resource-list&gt; &lt;keylock hobtid="72057594045071360" dbid="6" objectname="dbo.Daily" indexname="PK_Daily" id="lock14c6aab00" mode="X" associatedObjectId="72057594045071360"&gt; &lt;owner-list&gt; &lt;owner id="process6c9dc8" mode="X"/&gt; &lt;/owner-list&gt; &lt;waiter-list&gt; &lt;waiter id="process14e29e748" mode="S" requestType="wait"/&gt; &lt;/waiter-list&gt; &lt;/keylock&gt; &lt;keylock hobtid="72057594045464576" dbid="6" objectname="dbo.Impressions" indexname="IDX_Impressions_isCharged_showTime" id="lock14c901200" mode="X" associatedObjectId="72057594045464576"&gt; &lt;owner-list&gt; &lt;owner id="process14e29e748" mode="X"/&gt; &lt;/owner-list&gt; &lt;waiter-list&gt; &lt;waiter id="process6c9dc8" mode="U" requestType="wait"/&gt; &lt;/waiter-list&gt; &lt;/keylock&gt; &lt;/resource-list&gt; &lt;/deadlock&gt; </code></pre> <p>Edit: </p> <p>After suggestions from Jonathan Dickinson: </p> <ol> <li>I changed the stored procedure (removed the cursor), </li> <li>I changed the IDX_Impressions_isCharged_showTime to not allow PAGE_LOCKS and</li> <li>I added -1 second to the @now property in the windows service application to avoid borderline deadlock cases. </li> </ol> <p>Update:</p> <p>The query execution time was decreased after the last changes, but the number of exceptions hasn't.</p> <p>Hopefully last update:</p> <p>The changes proposed by Martin Smith are now live, the insert query now uses the non-clustered index and in theory this should fix the issue. For now no exceptions have been reported (Keeping my fingers crossed)</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAtzoya
    1. USAtzoya
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POupdate and insert queries creating a deadlock
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POupdate and insert queries creating a deadlock
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POupdate and insert queries creating a deadlock
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThis article covers Read Uncommitted and locking behaviour: http://beyondrelational.com/blogs/jacob/archive/2008/08/28/sql-server-transaction-isolation-level-read-committed.aspx
      singulars
      1. POupdate and insert queries creating a deadlock
      1. USAdam Houldsworth
    2. COThe problem seems to be arising because of the FK relationship to `dbo.Daily` what indexes do you have on both tables?
      singulars
      1. POupdate and insert queries creating a deadlock
      1. USMartin Smith
    3. COI have a couple of indexes in both of the tables. Daily has two indexes (primary clutered "dailyId" and nonclustered non-unique "date"). Impressions has 3 indexes (primary clustered "impressionId", nonclustered non-unique "impressionId,dailyId,isCalculated,isCharged", and nonclustered non-unique "isCharged,portalId")
      singulars
      1. POupdate and insert queries creating a deadlock
      1. USAtzoya
 

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