September 5, 2012 at 7:00 pm
I am getting a deadlock when I try to delete a FK constraint.
When i run the sproc below with the DELETE FROM Storefront.Size WHERE Storefront.Size.ID=@SizeID;
commented out I do not get the deadlock. But when it is uncommented I get the dead lock.
Is there a way to delete a record from a FK constraint table after a delete as in... Delete record from this table when it is ok to delete or resources become available?
ALTER proc [Storefront].[proc_DeleteSidelite]
@SideliteID INT
AS
BEGIN
DECLARE @SizeID INT;
SELECT @SizeID= sl.SizeID FROM Storefront.Sidelite sl with(nolock)
DELETE FROM Storefront.Size WHERE Storefront.Size.ID=@SizeID;
DELETE FROM Storefront.Sidelite WHERE ID = @SideliteID;
END;
I have added my schema as an attachment to this post.
This is as simple as I need to delete a row from another table that is related to the record that has been previously deleted.
When a sidelite is deleted I need to delete the size of the sidelite without causing deadlocks.
Dam again!
September 5, 2012 at 9:35 pm
You could consider defining the foreign key with the ON DELETE CASCADE option. Then all you need to do is delete the parent record from Sidelite and all the child records will also be deleted. This should resolve the deadlock issue.
Cheers
Leo
Nothing in SQL Server is ever so complicated that with a little work it can't be made more complicated.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
September 5, 2012 at 10:14 pm
Did you see the attachment i posted?
Is it possible to cascade on delete from the sidelite table to the size table since the FK is in the sidelite table?
Dam again!
September 5, 2012 at 10:57 pm
For these two tables it is ok to have dirty data. Is there any way that I can set a feature on these tables to where there is never a lock of any kind of all? For my select statements I have with(nolock), but my issues are coming in when I try to delete a record that is being updated at the same time.
What can i do to for the delete to act as the principle? and disregard any other operations that may be taking place on that record? As in, if an update is taking place on the record in question is there a way to cancel everything taking place onto that record and let the delete take over?
Dam again!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply