Deadlock in Merge Statement in SQL Server

DarshanShah-8037 1 Reputation point
2023-11-30T02:51:21.4733333+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,322 questions
{count} votes