How to tackle database deadlocks - mitikov/KeepSitecoreSimple GitHub Wiki
Where is deadlock coming from
You have a bag full of apples and going to count only untouched red ones.
You do not want anybody to take or bite apples from the box while counting as that will mess up your calculations.
Everybody is welcomed to eat apples once you finish counting.
Your son has started picking red apples for his friends at the same time you started counting.
He already took a few apples out of the bag and will take more out.
- On the one hand, you cannot continue counting apples till your son takes whatever he wants
- On the other hand, your son cannot take more as he knows he'll mess up your calculations
Why? A few operations executed at the exact same time
Deadlock happens when a few operations touch same data at the exact same time.
Chances of deadlocks are proportional to the query execution speed and workload.
Taking into general database ideology/design to do fast operations via primary keys, chances of deadlocks are low.
What are the operations?
SQL Server Profiler is capable of collecting deadlock graphs that will clearly state:
- Which operations provoked deadlock
- What resource got blocked?
- Which query was killed?
Solutions
A) Most obvious one - let your son take whatever he wants, and start counting all over
Default database engine logic is to kill your 'SELECT' statement and indicate the reason - 'Deadlock'.
Retrying 'SELECT' in a while will be successful - nothing wrong with this approach.
B) Count faster than your son moves - you'll finish counting before he even starts
Teach your son to move faster than Flash runs - he'll take whatever he wants before you start counting
In other words - speed up each operation execution by bringing in more resources and reducing the count of other operations to execute. You'll count faster without distractions (f.e. phone calls).
The good place to start is to reduce the number of queries system produces by increasing cache sizes.
C) Reduce the necessity to count apples in the basket and reduce the frequency of your son taking apples.
Fewer times those are happening, the less chance they'll be executed together.
D) Put a lock on a basket so that only one guy at a time can reach it
That is a no-way solution that will hurt everybody.
E) Re-design application to ensure the same set of data does not get concurrently accessed-modified
That is not always possible to say the truth.
Practical steps/solutions
In retry pattern you should trust. Sitecore ships with DataProviders.Retryer that is designed to retry the operation periodically a few times. It tackles both network issues, database engine failures, as well as deadlocks on 'Select' statements.
Understand both the workload your database gets, and where is it coming from. SQL Server Profiler will help to find the workload, and your application source code - reasons.
Summary
The day will come when you receive a deadlock - just a matter of time.
The most important part will be to face the problem prepared.