none
Transaction (Process ID 155) was deadlocked on lock resources with another process and has been chosen as the deadlock victim RRS feed

  • Question

  •  Hello ,

      I have Implement the following Stored Procedure . Then I call it in C#  In multiple Threads . Some Times I got this Exception :

       "Transaction (Process ID 155) was deadlocked on lock resources with another process and has been chosen as the deadlock

          victim.   Rerun the transaction.

     The Stored Procedure body is :

    	UPDATE top(@RecordsNumber)  CA SET CA.Transaction_BOOKING_INSTANT_Code= @BOOKING_INSTANT_Code ,CA.[Transaction_BOOKING_TIME]= getdate() , CA.LAST_MODIFIED_TIME = GetDate()
    	From
    		dbo.TBL_BP_CUSTOMERS_APPLICATIONS CA  WITH (READPAST) --ON CA.CUSTOMER_APPLICATION_ID = tbca.CUSTOMER_APPLICATION_ID
    			INNER JOIN TBL_BP_APPLICATIONS A WITH (READPAST) ON CA.APPLICATION_ID = A.APPLICATION_ID
    			INNER JOIN TBL_BP_CUSTOMERS C WITH (READPAST) ON CA.CUSTOMER_ID = C.CUSTOMER_ID
    			INNER JOIN TBL_BP_CUSTOMERS_SERVICES CS WITH (READPAST) ON (CA.CUSTOMER_APPLICATION_ID = CS.CUSTOMER_APPLICATION_ID AND CA.APPLICATION_ID = @APPLICATION_ID)
    			INNER JOIN TBL_BP_SERVICE_TRANSACTION ST WITH (READPAST) ON (CS.CUSTOMER_APPLICATION_SERVICE_ID = ST.CUSTOMER_APPLICATION_SERVICE_ID AND 
    
    				((A.APPLICATION_BILLING_TYPE = 0 AND -- Application Based
    				(ST.SERVICE_TRANSACTION_DATE >= ISNULL(A.APPLICATION_LAST_BILLING_DATE,DateAdd(D,1,GETDATE())) AND ST.SERVICE_TRANSACTION_DATE <= 
    				DATEADD(D, 0, DATEDIFF(D, 0,DATEADD(MONTH, A.BILLING_PERIOD, ISNULL(A.APPLICATION_LAST_BILLING_DATE, DateAdd(D,1,GETDATE()))))))) OR 
    				
    				(A.APPLICATION_BILLING_TYPE = 1 AND -- Customer Based
    				(ST.SERVICE_TRANSACTION_DATE >= ISNULL(CA.LAST_BILLING_DATE,DateAdd(D,1,GETDATE())) AND ST.SERVICE_TRANSACTION_DATE <= 
    					DATEADD(D, 0, DATEDIFF(D, 0,DATEADD(MONTH, CA.BILLING_PERIOD, ISNULL(CA.LAST_BILLING_DATE, DateAdd(D,1,GETDATE())))))))))
    		WHERE 
    		     (CA.APPLICATION_ID = @APPLICATION_ID OR @APPLICATION_ID = -1) and ((CA.IS_PROCESSED_FOR_TRANSACTION = 0  AND A.APPLICATION_BILLING_TYPE = 0) OR A.APPLICATION_BILLING_TYPE = 1 ) AND
    		     CA.Transaction_BOOKING_INSTANT_Code Is null And
    		    A.APPLICATION_ACTIVITY = 0 AND A.RECOERD_ACTIVITY = 0 AND 
    			C.CUSTOMER_ACTIVITY = 1 AND C.RECOERD_ACTIVITY = 0 AND 
    			CA.APPLICATION_STATUS_ID = 1 AND CA.RECORD_ACTIVITY = 0 AND ((CA.IS_PROCESSED_FOR_TRANSACTION = 0  AND A.APPLICATION_BILLING_TYPE = 0) OR A.APPLICATION_BILLING_TYPE = 1 ) AND
    			(@INVOICING_DATE IS NULL OR ( DATEADD(D, 0, DATEDIFF(D, 0,DATEADD(MONTH, CA.BILLING_PERIOD, ISNULL(CA.LAST_BILLING_DATE, DateAdd(D,1,GETDATE())))))<= DATEADD(D, 0, DATEDIFF(D, 0, @INVOICING_DATE)) ))

    How Can I solve this Issue ?

     Thanks

     

    ASk

    Thursday, December 21, 2017 11:24 AM

All replies

  • You can try to avoid deadlocks by specifying explicit locking hints

    Like what is explained here

    http://improve.dk/solving-deadlocks-through-locking-hints/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, December 21, 2017 11:28 AM
    Moderator
  • I doubt that using locking hints is going to help. Rather they will make things worse. You would need to rewrite the query so that it is more efficient and has index seeks and no scans in the plan. This may require that you have multiple paths for different search conditions.

    You can implement retry logic in your code, but it takes a couple of seconds to detect a deadlock, so I am not sure that with the current query that parallel execution of this query will scale. You may be better off using an application lock to serialise access, if you are not able to rewrite the query.

    Thursday, December 21, 2017 10:31 PM
    Moderator