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.
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.