locked
DeadLock on Same Wait resource RRS feed

  • Question

  •      

    <deadlock>
      <victim-list>
        <victimProcess id="process10258508" />
      </victim-list>
      <process-list>
        <process id="process10258508" taskpriority="0" logused="912" waitresource="KEY: 11:72057596004728832 (8fa6d1cb67a4)" waittime="661" ownerId="83453462660" transactionname="getTimeId" lasttranstarted="2014-07-04T13:42:14.847" XDES="0x290ebd7970" lockMode="U" schedulerid="9" kpid="172108" status="suspended" spid="200" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-07-04T13:42:14.843" lastbatchcompleted="2014-07-04T13:42:14.843" clientapp="MARS (Market Risk Systems) Infrastructure 21.0" hostname="LDNPSM020010937" hostpid="19564" loginname="sa" isolationlevel="read committed (2)" xactid="83453462660" currentdb="11" lockTimeout="4294967295" clientoption1="536873056" clientoption2="128056">
          <executionStack>
            <frame procname="" line="581" stmtstart="58694" stmtend="60460" sqlhandle="0x03000b00a9490b6b69dd5a012ba300000100000000000000" />
            <frame procname="" line="1" stmtstart="84" sqlhandle="0x01000b0055091e2c603a1cea2c0000000000000000000000" />
            <frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000" />
          </executionStack>
          <inputbuf> (@P1 tinyint,@P2 int,@P3 tinyint,@P4 text)EXEC MarsRepPutTransferControl @transferTypeId = @P1, @calcRequestRefNo = @P2, @pLVectorInd = @P3, @emailAddress = @P4   </inputbuf>
        </process>
        <process id="process14736088" taskpriority="0" logused="1680" waitresource="KEY: 11:72057596004728832 (64e449f8c5fb)" waittime="370" ownerId="83453465697" transactionname="getTimeId" lasttranstarted="2014-07-04T13:42:15.020" XDES="0x12953793c0" lockMode="U" schedulerid="50" kpid="174052" status="suspended" spid="247" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-07-04T13:42:15.020" lastbatchcompleted="2014-07-04T13:42:15.017" clientapp="MARS (Market Risk Systems) Infrastructure 21.0" hostname="LDNPSM020010938" hostpid="18404" loginname="sa" isolationlevel="read committed (2)" xactid="83453465697" currentdb="11" lockTimeout="4294967295" clientoption1="536873056" clientoption2="128056">
          <executionStack>
            <frame procname="" line="581" stmtstart="58694" stmtend="60460" sqlhandle="0x03000b00a9490b6b69dd5a012ba300000100000000000000" />
            <frame procname="" line="1" stmtstart="84" sqlhandle="0x01000b0055091e2c603a1cea2c0000000000000000000000" />
            <frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000" />
          </executionStack>
          <inputbuf> (@P1 tinyint,@P2 int,@P3 tinyint,@P4 text)EXEC MarsRepPutTransferControl @transferTypeId = @P1, @calcRequestRefNo = @P2, @pLVectorInd = @P3, @emailAddress = @P4   </inputbuf>
        </process>
      </process-list>
      <resource-list>
        <keylock hobtid="72057596004728832" dbid="11" objectname="" indexname="" id="lock1fe816ea80" mode="X" associatedObjectId="72057596004728832">
          <owner-list>
            <owner id="process14736088" mode="X" />
          </owner-list>
          <waiter-list>
            <waiter id="process10258508" mode="U" requestType="wait" />
          </waiter-list>
        </keylock>
        <keylock hobtid="72057596004728832" dbid="11" objectname="" indexname="" id="lock317332aa00" mode="X" associatedObjectId="72057596004728832">
          <owner-list>
            <owner id="process10258508" mode="X" />
          </owner-list>
          <waiter-list>
            <waiter id="process14736088" mode="U" requestType="wait" />
          </waiter-list>
        </keylock>
      </resource-list>
    </deadlock>

    Hi Experts ,

    I am not able to understand this dead lock, my stored proc inserts and updates record in underlying table. while updating records cluster key is passed if no cluster is provided process going to create record in underlying table.

    please can someone ellaborate what is happening exactly and what would the solution for this.


    Shivraj Patil.

    Monday, July 7, 2014 10:25 AM

Answers

  • Check the properties of the clustered index seek operator.  I expect that SQL Server optimized the expression "<> @transferid" to "< Scalar Operator and > Scalar Operator" for use in the seek predicate.  Although the plan might look ok at first glance, my guess is that the number of rows in the actual execution plan will be very high because it must touch every row in the table except for the excluded @transferid.  Scanning a large number of rows is a recipe for deadlocks.

    Assuming the existing clustered primary key index is useful for other queries, consider creating a non-clustered index on TimeId, TransferTypeId, RiskResultsStatus, and TransferId so that only those rows needed during the update are touched. 

     

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Shivraj Patil Wednesday, July 16, 2014 5:01 AM
    Tuesday, July 15, 2014 12:35 PM

All replies

  • Shivraj,

    Without knowing anything about the table structure , indexes etc all I can say is,

    There are two spids 200 and 247 , both executed from this application "MARS (Market Risk Systems) Infrastructure 21.0"

    Both this spids are executing the stored procedure EXEC MarsRepPutTransferControl simultaneously . Both the executions are trying to access same index on the object 72057596004728832 in the database id 11.

    Spid 200 is the deadlock victim. Two

    If the same process is rerun probably the deadlock will not happen again. This basically boils down to the way the application/database is designed . The better way is to handle this deadlock error in the application and then re-run again.

    Without knowing more about the whole tables and indexes , what it is trying to do it is impossible to give any direct solutions.

    http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx

    Read this blog to understand about deadlocks, deadlock graphs and what options you have.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com


    Monday, July 7, 2014 10:58 AM
  • Thanks Ashwin for explanation

    Table has clustered index on identity  column "TranferId " , when the process called with no TransferId then process does insert record with new TransferId and this transfer id further being used to update the rest of logic in proc.


    Shivraj Patil.

    Monday, July 7, 2014 12:09 PM
  • index_name	index_description	index_keys
    NN_CalcRequest	nonclustered located on sharpe_ref1_Group1	CalcRequestRefNo
    NN1_TypeStatusRelease	nonclustered located on sharpe_ref1_Group1	TransferTypeId, Status, ReleasedOn
    NN2_TypeRiskResultsStatus	nonclustered located on sharpe_ref1_Group1	TransferTypeId, RiskResultsStatus
    NN3_TMSEmailSentInd	nonclustered located on sharpe_ref1_Group1	TMSEmailSentInd
    PK_MARSREPTRANSFERCONTROL	clustered, unique, primary key located on sharpe_ref1_Group1	TransferId


    Shivraj Patil.

    Monday, July 7, 2014 12:11 PM
  • Are we doing huge insert and update in the same proc?
    If yes then can we do this with two different procs and see the difference if business allows this logic.

    Also hopefully appropriate locks\locks hints are used to get this done. Like sometime no lock can do a great job for us.


    Santosh Singh

    Monday, July 7, 2014 12:43 PM
  • Hi Shivraj,

    Can you post the full stored procedure as well as table structure please?


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Monday, July 7, 2014 12:50 PM
  • Hi Ashwin,

    This section is causing the issue when there 2 process called this code at the same time.

    IF (@transferId IS NULL)    
        BEGIN    
                    IF (@status IS NULL) SET @status = @STATUS_WAITING    
        
                    SET @msg = 'Unable to create new entry in MarsRepTransferControlDeadLock'    
        
                     -- Insert the new row    
                    INSERT INTO sharpe_ref1.dbo.MarsRepTransferControlDeadLock    
                    (    
                        TransferTypeId,    
                        ReadyForTransferTime,    
                        TransferStartTime,    
                        TransferEndTime,    
                        RiskResReadyForTransferTime,    
                        RiskResultsTransferStartTime,    
                        RiskResultsTransferEndTime,    
                        CalcRequestRefNo,    
                        RMPLRequestRefNo,    
                        ValuationDate,    
                        CalcRequestDate,    
                        ReleaseNo,    
                        ReleasedOn,    
                        LoadReference,    
                        TimeId,    
                        FirstLoadInd,    
                        PLVectorInd,    
                        Status,    
                        RiskResultsStatus,    
                        RiskEnginePLVectorStatus,    
                        RestartInd,    
                        EmailAddress,    
                        TMSEmailSentInd    
                    )    
                    VALUES    
                    (    
                       @transferTypeId,    
                       @readyForTransferTime,    
                       @transferStartTime,    
                       @transferEndTime,    
                       @riskResReadyForTransferTime,    
                       @riskResultsTransferStartTime,    
                       @riskResultsTransferEndTime,    
                       @calcRequestRefNo,    
                       @RMPLRequestRefNo,    
                       @valuationDate,    
                       @calcRequestDate,    
                       @releaseNo,    
                       @releasedOn,    
                       @loadReference,    
                       CONVERT(smallint, @timeId),    
                       @firstLoadInd,    
                       @pLVectorInd,    
                       @status,    
                       @riskResultsStatus,    
                       @riskEnginePLVectorStatus,    
                       @restartInd,    
                       @emailAddress,    
                       @TMS_MAIL_NOT_SENT    
                    )    
        
                    SELECT @rowcount = @@ROWCOUNT, @transferId = SCOPE_IDENTITY()    
        
                    IF (@rowcount != 1) RAISERROR (@msg, 16, 1)    
                      
                    IF @transferTypeId = @XFER_VARCALC    
                    BEGIN    
                       SET @msg = 'Error encountered when updating RiskResultsStatus with @STATUS_CALCFAIL'    
        
                        UPDATE tc    
                            SET tc.RiskResultsStatus = 4    
                        FROM sharpe_ref1.dbo.MarsRepTransferControlDeadLock AS tc WITH (ROWLOCK)    
                            JOIN sharpe_var1..CalcRequestTable AS cr WITH (NOLOCK)    
                                ON cr.CalcRequestRefNo = tc.CalcRequestRefNo    
                            JOIN sharpe_var1..VaRType  AS vt WITH (NOLOCK)    
                                ON vt.Id = cr.VaRTypeId    
                                    AND cr.CalcMethod NOT IN ('TS Static', 'Pos Static')    
                        WHERE tc.TransferId <> @transferId    
                        AND tc.TransferTypeId = 1    
                        AND tc.TimeId = CONVERT(smallint, @timeId)    
                        AND tc.RiskResultsStatus IS NULL    
                        AND ((cr.VaRTypeId IS NULL AND @VaRTypeId IS NULL) OR (cr.VaRTypeId = @VaRTypeId))    
                    END    
       END    

    UPdate query causing deadlock, I need your suggestion what I can change in proc to resolve this issue.

    update query complete in 1 seconds only but its causing issue when 2 process calling the same code.


    Shivraj Patil.

    Friday, July 11, 2014 6:15 AM
  • Can you make sure that both the calls for the Stored procedure (based on whatever parameters you are passing) uses the same access path i.e same index. 


    Thanks and regards, Rishabh K

    Friday, July 11, 2014 9:55 AM
  • what do you mean by same access path ? they are using the same index.


    Shivraj Patil.

    Friday, July 11, 2014 12:25 PM
  • what do you mean by same access path ? they are using the same index.

    By access path, I believe Rishabh meant the same index. 

    Can you post the execution plan?  With the indexes you posted, I would expect this query would require a clustered index scan of dbo.MarsRepTransferControl.  That table is named dbo.MarsRepTransferControlDeadLock in your query but I assume it is the same as the dbo.MarsRepTransferControl table in your index definitions.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, July 11, 2014 12:36 PM
  • thanks , 

    yes its the same index , this query going for clustered index seek on marsreptransfercontroldeadlock table.



    Shivraj Patil.

    Saturday, July 12, 2014 7:43 AM
  • yes its the same index , this query going for clustered index seek on marsreptransfercontroldeadlock table.

    Can you provide the actual execution plan?  If I understand correctly, the first column in the clustered index is TransferId.  I wouldn't expect a clustered index seek would be possible due to the inequality predicate in the WHERE clause.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, July 12, 2014 6:34 PM
  • Plesee below given execution plan for update query  , i also wonder why optimzer going for clustered index seek.

    Shivraj Patil.

    Tuesday, July 15, 2014 5:36 AM
  • Check the properties of the clustered index seek operator.  I expect that SQL Server optimized the expression "<> @transferid" to "< Scalar Operator and > Scalar Operator" for use in the seek predicate.  Although the plan might look ok at first glance, my guess is that the number of rows in the actual execution plan will be very high because it must touch every row in the table except for the excluded @transferid.  Scanning a large number of rows is a recipe for deadlocks.

    Assuming the existing clustered primary key index is useful for other queries, consider creating a non-clustered index on TimeId, TransferTypeId, RiskResultsStatus, and TransferId so that only those rows needed during the update are touched. 

     

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Shivraj Patil Wednesday, July 16, 2014 5:01 AM
    Tuesday, July 15, 2014 12:35 PM
  • Thanks Dan,


    Shivraj Patil.

    Wednesday, July 16, 2014 5:02 AM