Deadlock in child table while executing delete and insert statements-CodePudding

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)]

Schedule.Schedules (Child table)

[Schedule.Schedules (Child table)]

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.

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

[Deadlock Graph]

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?

CodePudding user response：

There's not enough information to be sure, but I'll tell you where I'd start.

Eliminate the IF EXISTS test. Just go straight to the DELETE. If the value isn't there, the DELETE will be quick anyway. You're also not in a transaction, which leaves you open to the table changing between SELECT and DELETE.
Re-write the proprietary DELETE...JOIN as an ANSI DELETE using a WHERE EXISTS subquery. The proprietary version has problems whose details elude me right now. Better to write it in a standard way, and not invite problems.

You say "child" and "parent" tables. Does Schedules have a defined foreign key to Assignments? It should.

I'm not sure those changes will eliminate the problem, but IMO they'll make it less likely. You're increasing the atomicity by reducing the number of statements, and by eliminating branches you force each invocation of the procedure to execute the exact same logical sequence.

CodePudding user response：

It looks like your deadlocking is caused by a big table scan on Schedules. The scan happens in three different places in your procedure. What should happen instead is a simple Nested Loops/Index Seek on ParentId.

The reason you have a scan is because the join condition on ParentId is between a nvarchar(50) column and a bigint. I suggest you fix this by making ParentId a bigint.

ALTER TABLE schedule.Schedules
  ALTER COLUMN ParentId bigint NULL;

You may need to drop and re-create indexes or constraints when you do this.

As a side point, although it appears that you have an index on schedule.Assignments (OldResourceRequestId), it is not unique. This is causing an Assert on the various subqueries to ensure only one row is returned, and may also be affecting query statistics/estimates.

I suggest you change it (if possible) to a unique index. If there are duplicates then you need to rethink these joins anyway, as you would get duplicate results or fail the Assert.

CREATE NONCLUSTERED INDEX [IX_Assignments_OldResourceRequestId] ON schedule.Assignments
(
    OldResourceRequestId ASC
)
WITH (DROP_EXISTING = ON, ONLINE = ON) ON PRIMARY;

You should also take note of your IF statements. They are not indented, and it is not clear that what is actually happening is that only the first statement afterwards is conditional, due to the lack of BEGIN END. As mentioned in the other answer, the IF may not be necessary anyway.