On Delete cascade & deadlock prevention
HI
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.
Thanks
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.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
BEGIN TRANSACTION;
-- Get (and keep) a shared lock with "right to upgrade to exclusive"
SELECT @DummyVariable = @SomeColumn
FROM header WITH UPDLOCK
WHERE (your where clause);
-- Now delete details
DELETE FROM detail
WHERE (your where clause);
-- Finally, delete header row just locked
DELETE FROM header
WHERE (your where clause); -- Same as in first query!!
COMMIT TRANSACTION; -- Or rollback in case of error, of course.
“
http://www.phwinfo.com/forum/comp-db-ms-sqlserver/343050-deadlock-avoidance-foreign-keys-dictating-table-access-order.html
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:
BEGIN TRANSACTION;
-- Get (and keep) a shared lock with "right to upgrade to exclusive"
SELECT @DummyVariable = @SomeColumn
FROM header WITH UPDLOCK
WHERE (your where clause);
-- Now delete details
DELETE FROM detail
WHERE (your where clause);
-- Finally, delete header row just locked
DELETE FROM header
WHERE (your where clause); -- Same as in first query!!
COMMIT TRANSACTION; -- Or rollback in case of error, of course.
“
http://www.phwinfo.com/forum/comp-db-ms-sqlserver/343050-deadlock-avoidance-foreign-keys-dictating-table-access-order.html
ASKER