SQL Server Deadlock – Resolving Deadlocks Between Non-Related Tables

deadlocksql serversql server 2014

I am facing with a deadlock between two tables Table1 and Table2.

The deadlock occurs when an sp (sp2) delete from table2 and other sp (sp1) try to update table1.

enter image description here

In table2 the column by which it is deleted is the primary key of table1, however due to a bad design the Foreign Key was never added, that is, that table2 has no references to table1, however when deleting from the table2 generates an exclusive lock of table1. So how the engine knows the reference without FK?

CREATE TABLE [dbo].[Table2] (
    [IdTable1]   INT          NOT NULL,
    [IdTable3] INT          NOT NULL,
    [Sent]         BIT          CONSTRAINT [DF_Table2_Sent] DEFAULT ((0)) NOT NULL,
    [Order]        VARCHAR (50) CONSTRAINT [DF_Table2_NewID] DEFAULT (newid()) NOT NULL,
    [ToExclude]    BIT          CONSTRAINT [DF_Table2_ToExclude] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED ([IdTable1] ASC, [IdTable3] ASC) WITH (FILLFACTOR = 100) ON [FG2]
);


GO
CREATE NONCLUSTERED INDEX [IX_Table2_IdTable1_Sent]
    ON [dbo].[Table2]([IdTable1] ASC, [Sent] ASC, [Order] ASC)
    INCLUDE([IdTable3]) WITH (FILLFACTOR = 100)
    ON [FG2];

Here is the deadlock detail. https://justpaste.it/700ik

Also the tables are on different filegroups (different files)

Why is this?

Also, if I had the FK, why should need check the PK in table1 if I am just deleting from table2?

EDIT
Thanks Josh for the answer.

Now I have SP2 as

CREATE PROCEDURE SP2 @value
AS
IF @value = 1
    UPDATE dbo.Table1.....
else
BEGIN
    EXEC SP3 --This SP update Table1
    DELETE FROM Table2...
END

Now to avoid keep the locking on Table 1 could I add a transaction as follow to release the lock on table 1 on SP3 completition?

CREATE PROCEDURE SP2 @value
AS
IF @value = 1
    UPDATE dbo.Table1.....
else
BEGIN
    BEGIN TRAN T1
       EXEC SP3 --This SP update Table1
    COMMIT TRAN T1
    DELETE FROM Table2...
END

Thanks.

Best Answer

Looking at a subset of the deadlock information:

enter image description here

This shows that session 342 is currently executing line 47 of the stored procedure dbo.SP2, which is a DELETE statement on dbo.Table2.

Somewhere prior to that in the procedure, this session has already acquired an eXclusive (key) lock on dbo.Table1. Based on the non-zero "log used" value, it seems like this procedure does some writes to dbo.Table1, thus acquiring the X lock and creating the potential for deadlocks.

The takeaway here is that the deadlock XML is only showing you the currently executing statement (Erik Darling has some great demos of this and other confusing things about deadlock graphs on his blog). It appears that there are multiple statements being executed in a transaction in this stored procedure.

Another possibility is that there is a trigger on one of more of these tables that's causing more locks to be taken than what is obvious from the T-SQL statements themselves.

Also, if I had the FK, why should need check the PK in table1 if I am just deleting from table2?

You're right, even if the foreign key existed on Table2, locks on Table1 would not be required to delete rows from Table2. Locks would be required if the FK was defined "backwards" and cascading deletes were enabled.

Related Question