What would cause this SQL Server deadlock?

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , , , ,

Leave a Reply