Deadlock in Merge Statement in SQL Server
DarshanShah-8037
1
Reputation point
Hello,
I have two SPs in which Insert Update and Delete is handled through MERGE Statement. I see indexes are properly defined. Same table is referred by these two SPs. These two SPs are frequently called. And both have MERGE statement. I tried with UPDLOCK on target table however still same issue. Added NOLOCK hint while reading data from few tables in join. Still same issue. Deadlock occurred on InvoiceDetailManualGlAccount table with Page Lock. image_2023_11_30T02_50_33_118Z.png Any hint to resolve deadlock ?
Below is the code.
MERGE INTO InvoiceDetailManualGlAccount T
USING (
SELECT P.InvoiceDetailId AS InvoiceDetailId
,CustomerApGlAccountId AS CustomerApGlAccountId
,CorrectedGlCodePercent AS [Percent]
,(ID.LineAmount * CorrectedGlCodePercent)/100 AS GlAccountAmount
FROM @correctionSumODCAGForIDMGA P
INNER JOIN InvoiceDetail ID WITH (NOLOCK)
ON ID.InvoiceDetailId = P.InvoiceDetailId
) AS S
ON T.InvoiceDetailId = S.InvoiceDetailId AND T.CustomerApGlAccountId = S.CustomerApGlAccountId
WHEN MATCHED THEN
UPDATE SET
InvoiceDetailId = S.InvoiceDetailId
, CustomerAPGlAccountId = S.CustomerApGlAccountId
, [Percent] = S.[Percent]
, Amount = S.GlAccountAmount
, InvoiceManualGlAccountSourceId = 3
, ModifiedDateTime = GETDATE()
, ModifiedUserId = @userId
WHEN NOT MATCHED BY TARGET THEN
INSERT (
InvoiceDetailId
,CustomerApGlAccountId
,[Percent]
,Amount
,InvoiceManualGlAccountSourceId
,CreatedDateTime
,CreatedUserId
)
VALUES
(S.InvoiceDetailId
,S.CustomerApGlAccountId
,S.[Percent]
,S.GlAccountAmount
,3
,GETDATE()
,@userId
)
WHEN NOT MATCHED BY SOURCE AND T.InvoiceDetailId IN (SELECT ID FROM @invoiceDetailsAllowedToDelete)
THEN
DELETE
;
SQL Server | Other
14,494 questions
Sign in to answer