I have below two queries on Production:
Query # 1: Script that deletes older historical data from core table "Table1" based on "Date" column
There are ~20 tables that point to "Table1" by foreign key with cascade rule, so when you delete from "Table1", data is deleted from ~20 other tables, too
Script deletes data in batches 50 rows at a time with 1 second interval between batches
Script does NOT cause Exclusive locks to escalate to table-level lock, when batch is only 50 rows; it only places row/page level (X) locks
Query # 2: Production Application that pulls data from "Table1", "Table2", "Table3" and some other tables
This is heavy read query, that places Shared locks on tables. It does not do any insert/update/delete
Problem:
Sometimes, deadlock occurs when Query # 2
(Prod app) reads data, and I simultaneously run Query # 1
(script deleting older data) in SSMS. Query # 2
is always a victim, as I understand it is because it is NOT doing any changes to transaction log, while Query # 1
does
Trying to resolve this, I've added set deadlock_priority -10
to Query # 1
, hoping that it will make Query # 1
victim in any possible deadlocks
First it seemed it worked, Query # 1
became victim in deadlock situations, throwing error 1205 when deleting some of the batches.
BUT - recently I discovered that sometimes Query # 2
still becomes a victim
Question:
How is this possible that Query # 2
sometimes becomes a victim even after I've set deadlock_priority -10
for Query # 1
?
Is there a way to fix it and make sure Query # 1
will be the victim in 100 % cases ?
Update: added graph below