0

We have two processes which are operating with delete/insert/update on the same table, but in different areas. These areas are clearly separated by a dimension field. Here is a simplified example:

  • Process 1: DELETE FROM facts WHERE Area = 'Actual' AND [...]
  • Process 2: INSERT INTO facts (Area, [...]) SELECT 'Plan' as Area, [...] FROM [...]

Now it happens that we get deadlocks between these two processes. However, they are operating on different records! Is it possible to prevent such deadlocks? e.g. is it possible to make a partial lock, or to give a "hint" to the processes so that they know that they are acting on different areas?

Dale K
  • 25,246
  • 15
  • 42
  • 71
askolotl
  • 964
  • 1
  • 13
  • 27
  • 2
    Step 1 to debugging deadlocks... inspect the deadlock graph, it will tell you what resources are conflicting. It maybe that you are missing the necessary indexes to allow SQL Server to take the best locks. Often the best way to avoid deadlocks is to ensure all access to multiple tables within a process are in the same order. And if you need help interpreting the deadlock graph you might find more specialist help at https://dba.stackexchange.com/ – Dale K Dec 02 '20 at 10:33
  • 3
    Query/index tuning is important to avoid deadlocks. This will help ensure only rows needed are touched. For example, if the delete query is performing a scan due to lack of useful indexes or non-sargable expressions, deadlocks are likely. – Dan Guzman Dec 02 '20 at 11:00
  • @DanGuzman Good point, I will check the execution plans closely to see if there are table scans or missing indexes! – askolotl Dec 02 '20 at 11:33
  • @DaleK Oh yes, thanks, I will check the deadlocks in the profiler, totally forgot this! – askolotl Dec 02 '20 at 11:34
  • 2
    @askolotl, you probably already have the deadlock graphs captured by the system_health XE trace. See [this answer](https://stackoverflow.com/questions/65072228/deadlock-graph-from-extended-events-not-showing/65073177#65073177) for an example query to retrieve the event. – Dan Guzman Dec 02 '20 at 11:36
  • @DanGuzman Thanks a lot for guiding me! I will look at this later; for now, I let the profiler run and wait for "deadlock" events. – askolotl Dec 02 '20 at 12:14

0 Answers0