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.