Reducing Deadlocks
Locks, blocks, and even deadlocks are common occurrences in a SQL Server database. Using a more optimistic isolation level, such as Read Committed Snapshot, can significantly reduce blocking for reader sessions. However, writers still block other writers.
You don't want excessive deadlocks because they contradict the principle of capturing and storing data. If you’re experiencing excessive deadlocks and know the problem scripts, ensure you're accessing tables in the same order across your scripts. For example, if you have two scripts, let’s call them A and B. In script A, you update Table1 and then update Table2. However, in script B, you update Table2 first and then Table1; this is a classic recipe for deadlock.
In the screenshot below, we can resolve the deadlock by allowing the forks table to update first in both scripts.
