How to overcome deadlocks

Question

I am receiving deadlock errors when trying to run a sproc with a delete statement in it. What is happening is that I've got a FK constraint table row that is being updated at the same time that the table row I'm deleting that it is related to. The data that is being updated in the constraint table is no longer important and retrieval for that data being updated will no longer be access by anyone for any reason, it just so happens that this update and delete can all happen at once. So, I need to the delete to be the principle operation.

What do I need to do to stop a deadlock like this?

DELETE FROM Storefront.Sidelite WHERE ID = @SideliteID;

Below is a screen shot of a Sidelite table and the Size constraint table.

Tables in question

Ok, there are no reads taking place here. The only type things taking place is many updates to the Size table while the Sidelite table is trying to delete a record that it's size is being updated and this is causing a deadlock.

I need to stop all operations to the Size table while a delete takes place in the sidelite table and then, I'll delete the related size record in a trigger.

What indexes do you have? What is the `UPDATE` statement? What RDBMS and version? What is the definition of the FK constraint? Does it cascade? — Martin Smith, Sep 05 '12 at 19:45

score 0 · Answer 1 · edited May 23 '17 at 12:13

0

on the select statement where it is getting the initial value you can utilize with(readuncommitted) or with(nolock) statement. This will however give you dirty reads. Please utilize the following link for more information: Why use a READ UNCOMMITTED isolation level?

edited May 23 '17 at 12:13

Community

1
1

answered Sep 05 '12 at 19:47

Jason Squires

27
1
9

score 0 · Answer 2 · answered Sep 06 '12 at 17:40

1.) SET ALLOW_SNAPSHOT_ISOLATION ON

2.) SET TRANSACTION ISOLATION LEVEL SNAPSHOT

ALTER proc [Storefront].[proc_DeleteSidelite]
@SideliteID INT
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

  DECLARE @SizeID INT; 
        BEGIN TRAN
          SELECT @SizeID= sl.SizeID FROM Storefront.Sidelite sl 
                                    with(nolock) WHERE sl.ID = @SideliteID
          DELETE FROM Storefront.Sidelite WHERE ID = @SideliteID;
          DELETE FROM Storefront.Size  WHERE ID=@SizeID;
        COMMIT TRAN
END;

How to overcome deadlocks

2 Answers2