none
SQL Server - Dead lock RRS feed

  • Question

  •  Dears ,

    • I have write create an SP that Update from Table :
    • I used This Stored IN my C# Code within  "Transaction Scope".        
    ALTER PROCEDURE [dbo].[PR_BP_USR_UPDATE_CUSTOMER_APPLICATION] 
          (
                @Customer_Application_ID int,
                @CustomerID int ,
                @ApplicationID int,
                @BillingPeriod smallint,
                @Last_Billing_Period datetime ,
                @ACCOUNT_NUMBER nvarchar(20),
                @App_Customer_ID nvarchar(100),
                @App_Customer_Desc nvarchar(100),
                @ApplicationStatusID smallint,
                @SadadBatchBalance float,
                @Is_QC smallint,
                @QC_NIN NVARCHAR(15),
                @QC_ID INT,
                @RecordActivity smallint,
                @QC_PERCENTAGE float,
                @EMAIL nvarchar(50),
                @MOBILE_NUMBER nvarchar(20),
                @BRM_ACCOUNT_NUMBER nvarchar(20),
                @BRM_MIGRATED_ON datetime,
                @MIGRATION_STATUS smallint,
                @IS_PROCESSED_FOR_TRANSACTION BIT,
                @TRANSACTION_PROCESS_DATE DATETIME,
                
                @RESULT_CODE smallint OUTPUT,
                @RESULT_MESSAGE VARCHAR(250) OUTPUT
          )
          
    AS
    BEGIN
          -- SET NOCOUNT ON added to prevent extra result sets from
          -- interfering with SELECT statements.
          SET NOCOUNT ON;
    
        -- Insert statements for procedure here
          UPDATE [dbo].[TBL_BP_CUSTOMERS_APPLICATIONS] SET
          [CUSTOMER_ID] = @CustomerID,
          [APPLICATION_ID]  = @ApplicationID,
          [BILLING_PERIOD] = @BillingPeriod ,
          [LAST_BILLING_DATE] = @Last_Billing_Period ,
          ACCOUNT_NUMBER = @ACCOUNT_NUMBER ,
    	  [APP_CUSTOMER_ID] = @App_Customer_ID ,
          [APP_CUSTOMER_DESC] = @App_Customer_Desc ,
          [APPLICATION_STATUS_ID] = @ApplicationStatusID ,
          [IS_QC]= @Is_QC,
          [QC_NIN]= @QC_NIN ,
          [QC_ID]= @QC_ID ,
          [QC_PERCENTAGE] = @QC_PERCENTAGE,
          SADAD_BATCH_BALANCE=@SadadBatchBalance,
          EMAIL = @EMAIL,
          MOBILE_NUMBER = @MOBILE_NUMBER ,
          LAST_MODIFIED_TIME=GETDATE(),      
          BRM_ACCOUNT_NUMBER=@BRM_ACCOUNT_NUMBER,     
          BRM_MIGRATED_ON=@BRM_MIGRATED_ON,     
          MIGRATION_STATUS=@MIGRATION_STATUS,
          IS_PROCESSED_FOR_TRANSACTION = @IS_PROCESSED_FOR_TRANSACTION,
          TRANSACTION_PROCESS_DATE = CONVERT(DATE, @TRANSACTION_PROCESS_DATE)
          
          WHERE [CUSTOMER_APPLICATION_ID] = @Customer_Application_ID AND [RECORD_ACTIVITY] =0

    •      I write another SP . I do not use any transaction Scope :
                    
    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)) ))
    • I got the deadlock Exception :

               

    •  Also Here is the XML

    Deadlock graph 493444 1 sa 0X01 27 PN3-ELM-VI-SQ01 2017-12-26 00:19:03.920 <deadlock-list> <deadlock victim="process7b57048"> <process-list> <process id="process7b57048" taskpriority="0" logused="0" waitresource="PAGE: 5:1:7402953" waittime="1981" ownerId="1046685721" transactionname="UPDATE" lasttranstarted="2017-12-26T00:19:00.380" XDES="0x30035b950" lockMode="U" schedulerid="23" kpid="11100" status="suspended" spid="155" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-12-26T00:19:00.380" lastbatchcompleted="2017-12-26T00:19:00.380" clientapp=".Net SqlClient Data Provider" hostname="PN3-BIL-WV-IS02" hostpid="5240" loginname="BPAPP" isolationlevel="read committed (2)" xactid="1046685721" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionStack> <frame procname="BillingDB.dbo.PR_BP_USR_GET_CUSTOMERS_APPS_WITH_SERVICE_TRANSACTION_Book" line="27" stmtstart="1442" stmtend="6366" sqlhandle="0x03000500e9867c474192c0004ea800000100000000000000"> 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 &gt;= ISNULL(A.APPLICATION_LAST_BILLING_DATE,DateAdd(D,1,GETDATE())) AND ST.SERVICE_TRANSACTION_DATE &lt;= DATEADD(D, 0, DATEDIFF( </frame> </executionStack> <inputbuf> Proc [Database Id = 5 Object Id = 1199343337] </inputbuf> </process> <process id="process18b708" taskpriority="0" logused="1684" waitresource="PAGE: 5:1:7402917" waittime="883" ownerId="1046685048" transactionname="user_transaction" lasttranstarted="2017-12-26T00:18:58.490" XDES="0x5e57933c0" lockMode="IU" schedulerid="1" kpid="8300" status="suspended" spid="165" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-12-26T00:19:03.033" lastbatchcompleted="2017-12-26T00:19:03.030" clientapp=".Net SqlClient Data Provider" hostname="PN1-BIL-WV-IS01" hostpid="6304" loginname="BPAPP" isolationlevel="read committed (2)" xactid="1046685048" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionStack> <frame procname="BillingDB.dbo.PR_BP_USR_UPDATE_CUSTOMER_APPLICATION" line="38" stmtstart="2540" stmtend="4704" sqlhandle="0x03000500c6f4131ba8d0d00013a400000100000000000000"> UPDATE [dbo].[TBL_BP_CUSTOMERS_APPLICATIONS] SET [CUSTOMER_ID] = @CustomerID, [APPLICATION_ID] = @ApplicationID, [BILLING_PERIOD] = @BillingPeriod , [LAST_BILLING_DATE] = @Last_Billing_Period , ACCOUNT_NUMBER = @ACCOUNT_NUMBER , [APP_CUSTOMER_ID] = @App_Customer_ID , [APP_CUSTOMER_DESC] = @App_Customer_Desc , [APPLICATION_STATUS_ID] = @ApplicationStatusID , [IS_QC]= @Is_QC, [QC_NIN]= @QC_NIN , [QC_ID]= @QC_ID , [QC_PERCENTAGE] = @QC_PERCENTAGE, SADAD_BATCH_BALANCE=@SadadBatchBalance, EMAIL = @EMAIL, MOBILE_NUMBER = @MOBILE_NUMBER , LAST_MODIFIED_TIME=GETDATE(), BRM_ACCOUNT_NUMBER=@BRM_ACCOUNT_NUMBER, BRM_MIGRATED_ON=@BRM_MIGRATED_ON, MIGRATION_STATUS=@MIGRATION_STATUS, IS_PROCESSED_FOR_TRANSACTION = @IS_PROCESSED_FOR_TRANSACTION, TRANSACTION_PROCESS_DATE = CONVERT(DATE, @TRANSACTION_PROCESS_DATE) WHERE [CUSTOMER_APPLICATION_ID] = @Custo </frame> </executionStack> <inputbuf> Proc [Database Id = 5 Object Id = 454292678] </inputbuf> </process> </process-list> <resource-list> <pagelock fileid="1" pageid="7402953" dbid="5" objectname="BillingDB.dbo.TBL_BP_CUSTOMERS_APPLICATIONS" id="lock1a0506f80" mode="IX" associatedObjectId="72057594101366784"> <owner-list> <owner id="process18b708" mode="IX"/> </owner-list> <waiter-list> <waiter id="process7b57048" mode="U" requestType="wait"/> </waiter-list> </pagelock> <pagelock fileid="1" pageid="7402917" dbid="5" objectname="BillingDB.dbo.TBL_BP_CUSTOMERS_APPLICATIONS" id="lock24be16c80" mode="U" associatedObjectId="72057594101366784"> <owner-list> <owner id="process7b57048" mode="U"/> </owner-list> <waiter-list> <waiter id="process18b708" mode="IU" requestType="wait"/> </waiter-list> </pagelock> </resource-list> </deadlock> </deadlock-list>

      How Can I solve this Issue .

          

    ASk

    Tuesday, December 26, 2017 9:00 AM

Answers

  • I note that the process running PR_BP_USR_UPDATE_CUSTOMER_APPLICATION started its transaction 00:18:58.490, but but the last batch was started 00:19:03.033. This means that this transaction had executed for 4½ seconds before the deadlocks. I would assume that during this period it performed more than one update. But what updates, I don't know. Was that updates to other tables? Or was that that more updates to the same table? Judging from the deadlock chain, there was. That begs the question why there are repeated calls to the same procedure and not a single call to a procedure that accepts a table-valued parameter as input.
    The other process updates a number of rows in the same table, how many I don't know since I don't know the value of @RecordsNumber. I see that it uses READPAST, so it might not be blocked by updates from the first transaction. (I say "might", because I don't know how READPAST works with UPDATE statements.) In any case, it takes out page locks, and it has locked one page, on which the first process now wants to update a row. At the same time, it's waiting to access a page on which the first process has already made an update. (So I guess READPAST does not work as you might have thought.)

    It is possible that adding OPTION (RECOMPILE) at the end to the UPDATE TOP query could help, as that could permit for a more efficient query plan that does not require page locks. It could also help to replace PR_BP_USR_UPDATE_CUSTOMER_APPLICATION with a procedure that accepts a table-valued parameter.

    None of these actions will entirely remove the risk for the deadlock. However, if the transactions are shortened in time, the window where the deadlock can occur can be considerably reduced.

    Tuesday, December 26, 2017 9:55 AM
    Moderator