May 4, 2021 at 9:57 am
Are you sure that these are deadlocks, and not just locks? As you are inserting, I would not expect deadlocks & suggest that more investigation is needed.
May 4, 2021 at 11:12 am
The errors I am getting are all like this:
Transaction (Process ID 81) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
May 4, 2021 at 12:28 pm
The key to deadlocks is usually, but not always, that you have multiple different queries, not the same query, that are accessing the various tables in different orders. Meaning, one batch inserts into TABLEA and then selects from TABLEB. The other batch selects from TABLEB, then inserts into TABLEA. Each one has to wait on the other to complete, but neither can complete until the other is finished. A deadly embrace, a deadlock.
Deadlocks are also performance related. Even with the example above, if everything is completed really quickly, you'll never notice that the code is out of order.
So, to fix deadlocks, first, get the code so that everything goes in the same order all the time. Next, performance tune that code and your structures. Between the two, you should eliminate most deadlocks. Yes, snapshot isolation helps because the shared locks needed for reads are not taken out in the same way, reducing the chances of deadlocks radically. However, snapshot isolation can add considerable load to your tempdb, so be cautious here.
Search the articles here on SSC. Tons and tons of information on deadlocks, how to get the deadlock graphs (extended events, specifically system_health, will be your friend, don't go old school and use traceflags), how to read the graphs and how to fix the issues.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 4, 2021 at 1:27 pm
Great tip about system_health. I didn't realise so much more information was recorded.
I have extracted the xml_deadlock_report events - and the results are unexpected. And probably much easier to fix.
Thanks for the help.
May 4, 2021 at 3:09 pm
Happy to help!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply