0

I have a node.js script that is attempting to geocode records in a table. The script selects 100 records at a time that do not have lat/log data, geocodes them using a third party api and writes the results to the lat/long column for the corresponding record. This script has been causing deadlock errors in the database like the one below:

Transaction (Process ID 69) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I setup SQL Server Profiler to run a trace and track deadlock data and was able to get one of the deadlock graphs but I can't quite figure out why deadlock is occuring. The two statements are both updates against different records in the same table. I checked the Isolation Level and it is Read Committed. While I haven't dealt with a lot database deadlocks, I didn't think these types of transactions would cause a deadlock. I've included the XML below in case it is helpful:

<deadlock-list>
 <deadlock victim="process25211037848">
  <process-list>
   <process id="process25211037848" taskpriority="0" logused="0" waitresource="PAGE: 8:1:34878 " waittime="2422" ownerId="3315762" transactionname="UPDATE" lasttranstarted="2021-05-23T23:46:32.920" XDES="0x24d9e44d900" lockMode="U" schedulerid="4" kpid="7440" status="suspended" spid="65" sbid="0" ecid="4" priority="0" trancount="0" lastbatchstarted="2021-05-23T23:46:32.923" lastbatchcompleted="2021-05-23T23:46:32.380" lastattention="1900-01-01T00:00:00.380" clientapp="Node.js" hostname="PNS157" hostpid="6536" isolationlevel="read committed (2)" xactid="3315762" currentdb="8" currentdbname="DW_Staging" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="100" stmtend="250" sqlhandle="0x02000000259d222f04b5eda0f2e0ab46a16fa9ba5cc2d8310000000000000000000000000000000000000000">
unknown     </frame>
     <frame procname="adhoc" line="1" stmtend="236" sqlhandle="0x02000000a6f84a131709260f562f587021ed884c3ed1e86a0000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
UPDATE Members SET MbrLat = 28.03195, MbrLong = -81.09178 WHERE MemberUID = &apos;ID456&apos;    </inputbuf>
   </process>
   <process id="process24eae1b64e8" taskpriority="0" logused="0" waitresource="PAGE: 8:1:36694 " waittime="2422" ownerId="3315758" transactionname="UPDATE" lasttranstarted="2021-05-23T23:46:32.920" XDES="0x25206e8b900" lockMode="U" schedulerid="3" kpid="7784" status="suspended" spid="79" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2021-05-23T23:46:32.920" lastbatchcompleted="2021-05-23T23:46:32.480" lastattention="1900-01-01T00:00:00.480" clientapp="Node.js" hostname="PNS157" hostpid="6536" isolationlevel="read committed (2)" xactid="3315758" currentdb="8" currentdbname="DW_Staging" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="100" stmtend="250" sqlhandle="0x02000000d48bf12084e55043f1f9c7969865359e9737b71a0000000000000000000000000000000000000000">
unknown     </frame>
     <frame procname="adhoc" line="1" stmtend="244" sqlhandle="0x02000000424e371e647b248bf01785555348ca7d05f180650000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
UPDATE Members SET MbrLat = 25.9510963, MbrLong = -81.1176539 WHERE MemberUID = &apos;ID123&apos;    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <pagelock fileid="1" pageid="34878" dbid="8" subresource="FULL" objectname="DW_Staging.dbo.Members" id="lock24ed7f99880" mode="U" associatedObjectId="72057594044678144">
    <owner-list>
     <owner id="process24eae1b64e8" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="process25211037848" mode="U" requestType="wait"/>
    </waiter-list>
   </pagelock>
   <pagelock fileid="1" pageid="36694" dbid="8" subresource="FULL" objectname="DW_Staging.dbo.Members" id="lock24e383d5f00" mode="U" associatedObjectId="72057594044678144">
    <owner-list>
     <owner id="process25211037848" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="process24eae1b64e8" mode="U" requestType="wait"/>
    </waiter-list>
   </pagelock>
  </resource-list>
 </deadlock>
</deadlock-list>
    

Can anyone see why this deadlock would be occurring? Any suggestions for how I might get around these deadlock errors or what I should do to debug the issue?

UPDATES - in response to the questions below:

  • I don't have an index on MemberUID
  • The execution plan can be found here.
7
  • 1
    Have you got an index on MemberUID if so what is its definition ? – Stephen Morris - Mo64 May 24 at 7:30
  • 1
    Why is it taking page locks? Is the "allow row locks" option disabled on the object containing those pages? What does the execution plan look like? – Martin Smith May 24 at 8:15
  • Just for reading – Denis Rubashkin May 24 at 9:13
  • Please share the query plan via brentozar.com/pastetheplan. If these are executing inside a transaction, please edit and add the full batch – Charlieface May 24 at 13:33
  • @StephenMorris-Mo64 - I don't have an index on MemberUID. I went ahead and updated my question to reflect this – Abe Miessler May 24 at 21:15
3

Because you have no suitable index on the table, you get a full table scan.

The table scan is going parallel, which means that access is happening out-of-order. You could solve this by using OPTION(MAXDOP 1), but that is just a band-aid.

Your best bet is to create an index to serve this query, the idea being that the query only needs to do a point lookup on the exact rows it needs.

Without knowing other queries you have, I can only suggest based on this one

CREATE NONCLUSTERED INDEX IX_Members_MemberUID ON Members (MemberUID) INCLUDE (MbrLat, MbrLong);

Given that the column is called MemberUID it is probably the primary key, in which case you should almost certainly make this a CLUSTERED index

CREATE UNIQUE CLUSTERED INDEX CX_Members_MemberUID ON Members (MemberUID);

Or better

--  DROP CONSTRAINT the old primary key then ...

ALTER TABLE Members
    ADD CONSTRAINT PRIMARY KEY CLUSTERED (MemberUID);

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.