2022-08-18

Deadlock in child table while executing delete and insert statements

I have these two below tables on which I am performing delete followed by an insert but intermittently deadlocks are being encountered.

Schedule.Assignments (Parent table)

[Schedule.Assignments (Parent table)]

1

Schedule.Schedules (Child table)

[Schedule.Schedules (Child table)]

2

Intermittently two types of deadlocks are occurring on schedule.Schedules table (child) although the operation is being performed on schedule.Assignments table (parent). Both are having the same deadlock graph as shown below.

  1. Deadlock between an Insert and Delete statements on schedule.Assignments table.

  2. Deadlock between same Delete statement on schedule.Assignments table.

[Deadlock Graph]

3

Deadlock Graph1 : https://pastebin.com/raw/ZpQUrjBV
Deadlock Graph2 : https://pastebin.com/raw/DhnuyZ7a

StoredProc containing the insert and delete statements: https://pastebin.com/raw/6DNh2RxH

Query Execution Plan: PasteThePlan

[Edit]

Assignments Schema: Assignments Schema

Assignments Indexes: Assignments Indexes

Schedules Schema: Schedules Schema

Schedules Indexes: Schedules Indexes

What I am not able to understand as to why deadlock object is showing as child table whereas the process involved in the deadlock shows insert/delete on parent table.

Please share your thoughts as how to solve these deadlocks?



No comments:

Post a Comment