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 = 'ID456' </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 = 'ID123' </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