In one of our applications, a deadlock was caused by the following two ad-hoc queries, issued by the entity-framework.
Query 1:
DELETE [dbo].[T_Fin_RequestGeneralLedger]
WHERE ([RequestId] = @0)
Query 2:
INSERT [dbo].[T_Fin_RequestGeneralLedgerEntityLink]
([RequestId], [FinancialCompanyCd], [FiscalYear], [LedgerCode], LastUpdatedOn], [LastUpdatedBy])
VALUES (@0, @1, @2, @3, @4, @5)
The relations between the relevant tables are as follows.
The FK from T_Fin_RequestGeneralLedgerEntityLink to T_Fin_RequestGeneralLedger has "ON DELETE CASCADE" specified.
There are no triggers.
The deadlock is on the primary key indexes PK_T_Fin_RequestGeneralLedgerEntityLink of T_Fin_RequestGeneralLedgerEntityLink and PK_T_FinCompFYLedger on T_FinCompFYLedger.
I do understand that the insert needs locks on both indexes.
I do understand that the delete on T_Fin_RequestGeneralLedger needs locks on PK_T_Fin_RequestGeneralLedgerEntityLink.
I fail to see why the delete needs a lock on PK_T_FinCompFYLedger, however. Can anyone explain why this lock is needed?
Kind regards,
Chris Sijtsma