Sample Header Ad - 728x90

What would cause this SQL Server deadlock?

0 votes
1 answer
657 views
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: unknown unknown UPDATE Members SET MbrLat = 28.03195, MbrLong = -81.09178 WHERE MemberUID = 'ID456' unknown unknown UPDATE Members SET MbrLat = 25.9510963, MbrLong = -81.1176539 WHERE MemberUID = 'ID123' 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 .
Asked by Abe Miessler (489 rep)
May 24, 2021, 04:16 AM
Last activity: May 24, 2021, 09:30 PM