On Delete cascade & deadlock prevention

Lets say we have tables Parent & Child in which Child has a FK on Parent PK with ON DELETE CASCADE.
Now imagine two threads A & B
Thread A inserts a record to child and waits to insert a record to Parent
Thread B try to remove  a record from Parent and attempts to remove all relevant records from Child first due to the FK constraint
In the case that thread B acquires an Exclusive lock on Child , we got a classical deadlock.

Please advice how to resolve , is there any possibility to control the lock mode on the DELETE CASCADE operation.
LVL 19
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

As far as I know there's no way to control that lock. So, you should use the cascading delete with care. I would not recommend it for tables with large volume of data.
There are hints that you can include in the insert / delete query, but I don't think that will help in preventing the deadlock that you're talking about. See query hints here:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
elimesikaAuthor Commented:
elimesikaAuthor Commented:
Well, after investigation I have found a better way that prevent deadlocks, implemented it and find it as a great solution. I am adding that for other people seeking of how to resolve that problem.

pre-acquire the lock in the delete in the correct order manually.

Example is with manual deletes rather than cascade, but should work for cascade’s as well.

You can't just change it to delete the header first and the detail
after, as that would cause a foreign key violation. But you can make
sure that you do get a lock on the row to be deleted at the start of the
procedure. Something like this:

-- Get (and keep) a shared lock with "right to upgrade to exclusive"
SELECT @DummyVariable = @SomeColumn
WHERE (your where clause);

-- Now delete details
WHERE (your where clause);

-- Finally, delete header row just locked
WHERE (your where clause); -- Same as in first query!!

COMMIT TRANSACTION; -- Or rollback in case of error, of course.


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.