none
deadlock issue

    Question

  • Hi All,

    Need some help in fixing the deadlock.

    Will an Intent exclusive (IX) lock can cause deadlock??
    We are seeing deadlocks where one spid is having IX lock and another spid is requesting for a Shared lock.
    Needs some advise here on how can I avoid/minimize the deadlock of current scenario.

    Below is the deadlock graph.

    deadlock1.xml

    ============

    <deadlock>
      <victim-list>
        <victimProcess id="process72c7134ca8" />
      </victim-list>
      <process-list>
        <process id="process72c7134ca8" taskpriority="0" logused="15242008" waitresource="OBJECT: 7:322100188:0 " waittime="4549" ownerId="16137045" transactionname="user_transaction" lasttranstarted="2018-06-08T07:58:02.500" XDES="0x72c3494458" lockMode="IX" schedulerid="1" kpid="52136" status="suspended" spid="79" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-06-08T07:58:15.943" lastbatchcompleted="2018-06-08T07:58:15.943" lastattention="2018-06-08T07:58:14.733" clientapp=".Net SqlClient Data Provider" hostname="RD0003FF9073D7" hostpid="4260" loginname="BDO-APT-LMM-SVC-LDT2" isolationlevel="read committed (2)" xactid="16137045" currentdb="7" currentdbname="BDO-DB-APT-LIB-LDT2-IND" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
          <executionStack>
            <frame procname="unknown" queryhash="0x921c0b78aa56bc6a" queryplanhash="0x7d3ad568ab0d3fd5" line="1" stmtend="1066" sqlhandle="0x02000000488d8e0d31e51a04301cdbf9481c3410ac0375dc0000000000000000000000000000000000000000">
    unknown    </frame>
          </executionStack>
          <inputbuf>
    insert bulk [Questionnaire].[FieldValues] ([ChangesetId] UniqueIdentifier, [Id] UniqueIdentifier, [CreatedAt] DateTimeOffset(0), [CreatedBy] Int, [DeletedAt] DateTimeOffset(0), [DeletedBy] Int, [FieldId] UniqueIdentifier, [IsDeleted] Bit, [LocaleTypeId] UniqueIdentifier, [ModifiedAt] DateTimeOffset(0), [ModifiedBy] Int, [PropertyDefinitionId] UniqueIdentifier, [Value] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [FlagColor] NVarChar(10) COLLATE SQL_Latin1_General_CP1_CI_AS, [IsTailored] Bit, [ValueChangedAt] DateTime2(0))   </inputbuf>
        </process>
        <process id="process72c4437c28" taskpriority="0" logused="63352560" waitresource="PAGE: 7:1:3313776 " waittime="3699" ownerId="16143070" transactionname="user_transaction" lasttranstarted="2018-06-08T07:58:10.190" XDES="0x70fd1c4040" lockMode="S" schedulerid="1" kpid="55288" status="suspended" spid="126" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-06-08T07:58:10.183" lastbatchcompleted="2018-06-08T07:58:10.160" lastattention="1900-01-01T00:00:00.160" clientapp=".Net SqlClient Data Provider" hostname="RD0003FF9073D7" hostpid="5132" loginname="BDO-APT-LMM-SVC-LDT2" isolationlevel="read committed (2)" xactid="16143070" currentdb="7" currentdbname="BDO-DB-APT-LIB-LDT2-IND" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
          <executionStack>
            <frame procname="e0ca04c6-94c4-498a-a3f3-6013a8d22925.Common.uspDeleteBaseContent" queryhash="0x4ac02b61b6324de1" queryplanhash="0xba96b7b3469aa21f" line="85" stmtstart="17482" stmtend="17680" sqlhandle="0x03000700dbcec17e0e54b500f8a8000001000000000000000000000000000000000000000000000000000000">
    DELETE AA FROM AMT.ItemVersions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetI    </frame>
            <frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" stmtstart="84" stmtend="202" sqlhandle="0x010007004d91862090bfee657100000000000000000000000000000000000000000000000000000000000000">
    unknown    </frame>
            <frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
    unknown    </frame>
          </executionStack>
          <inputbuf>
    (@EmployeeId int,@BaseId uniqueidentifier)EXECUTE [Common].[uspDeleteBaseContent] @EmployeeId, @BaseId   </inputbuf>
        </process>
      </process-list>
      <resource-list>
        <objectlock lockPartition="0" objid="322100188" subresource="FULL" dbid="7" objectname="e0ca04c6-94c4-498a-a3f3-6013a8d22925.Questionnaire.FieldValues" id="lock720f6f4b80" mode="X" associatedObjectId="322100188">
          <owner-list>
            <owner id="process72c4437c28" mode="X" />
          </owner-list>
          <waiter-list>
            <waiter id="process72c7134ca8" mode="IX" requestType="wait" />
          </waiter-list>
        </objectlock>
        <pagelock fileid="1" pageid="3313776" dbid="7" subresource="FULL" objectname="e0ca04c6-94c4-498a-a3f3-6013a8d22925.Questionnaire.Questionnaires" id="lock709542a100" mode="IX" associatedObjectId="72057594060537856">
          <owner-list>
            <owner id="process72c7134ca8" mode="IX" />
          </owner-list>
          <waiter-list>
            <waiter id="process72c4437c28" mode="S" requestType="convert" />
          </waiter-list>
        </pagelock>
      </resource-list>
    </deadlock>

    Thanks,

    Sam

    Tuesday, June 12, 2018 5:15 AM

All replies

  • Will an Intent exclusive (IX) lock can cause deadlock??
    ...
    Needs some advise here on how can I avoid/minimize the deadlock of current scenario.

    Hello Sam,

    Intent XLock means the process tries to get a lock on an object, but can't because an other process already holds a lock on it and deadlock are mainlyan issue caused by application design, e.g. 2 app logics trying to do the same update, but in reverse order: Process 1 update table a+b within a transaction, process 2 update table b+a.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, June 12, 2018 6:35 AM
    Moderator
  • This caught my attention:

        <process id="process72c7134ca8" transactionname="user_transaction" lasttranstarted="2018-06-08T07:58:02.500" lastattention="2018-06-08T07:58:14.733" lastbatchcompleted="2018-06-08T07:58:15.943" lastbatchstarted="2018-06-08T07:58:15.943"

    I have rearranged the timestamps to be in order. The process running SqlBulkCopy has started a transaction, sent an attention signal to SQL Server to interrupt operation and then continued without rolling back the transaction. Attention signals most commonly appear when the client API times out. It could be that there is something special with SqlBulkCopy, but this looks to me something that should be investigated.

    As for the deadlock, it seems that Questionaire.FieldValues and Questionaire.Questionaires have a FK constraints referring to AMT.ItemVersions. It could be that the FK columns are not indexed.

    But maybe it is that simple that these processes should not run simultaneously. The bulk-load operation should maybe take out a table lock to serliase the operations.

    Tuesday, June 12, 2018 7:18 AM
  • Thanks Erland, Yes. there are some FKeys.

    ALTER TABLE [Questionnaire].[Questionnaires]  WITH NOCHECK ADD  CONSTRAINT [FK_Questionnaires_ItemVersions_ChangesetId_ItemId] FOREIGN KEY([ChangesetId], [ItemId])
    REFERENCES [AMT].[ItemVersions] ([ChangesetId], [ItemId])
    GO

    ALTER TABLE [Questionnaire].[FieldValues]  WITH NOCHECK ADD  CONSTRAINT [FK_FieldValues_Fields_ChangesetId_FieldId] FOREIGN KEY([ChangesetId], [FieldId])
    REFERENCES [Questionnaire].[Fields] ([ChangesetId], [Id])
    GO

    Also , I am try to build a story around this deadlock scenario.

    Looks like they are trying to delete from Questionnaire.FieldVaues at the same time as you are trying to bulk insert into it. I am saying this because it is evident from the delete stored procedure.  But don't know why the deadlock graph is showing a different stmt at the time of deadlock. But still as per my gut feeling goes, there will be a exclusive lock(X) has already been held by spid 126 I believe. Pl correct me if I am wrong.

    Here is stored proc code.

    CREATE PROCEDURE [Common].[uspDeleteBaseContent] @EmployeeId INT, @BaseId UniqueIdentifier
    AS
    BEGIN
        DROP TABLE IF EXISTS #ChangesetsToBeDeleted

        BEGIN TRANSACTION;
        BEGIN TRY

        CREATE TABLE #ChangesetsToBeDeleted ( ChangesetID UNIQUEIDENTIFIER NOT NULL  )

        -- Identify the Changesets that should be deleted from all the tables
        INSERT INTO #ChangesetsToBeDeleted (ChangesetID  )
        SELECT CC.ID  FROM  Common.Changesets CC WHERE CC.Status = 'A' AND BaseId=@BaseId

       
        DELETE AA FROM Questionnaire.FieldValues                                   AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
        DELETE AA FROM Questionnaire.Fields                                           AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
        DELETE AA FROM Questionnaire.Questionnaires                                   AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId

        DELETE AA FROM AMT.ItemVersions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId

        UPDATE Common.Changesets SET Status = 'D', ModifiedAt = GETUTCDATE(), ModifiedBy = @EmployeeId WHERE Id IN ( SELECT ChangesetId FROM #ChangesetsToBeDeleted )  

        END TRY
        BEGIN CATCH
            SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
            IF @@TRANCOUNT > 0  
                ROLLBACK TRANSACTION;
            THROW;
        END CATCH

        IF @@TRANCOUNT > 0  
        BEGIN
            COMMIT TRANSACTION;  

            --drop all temp tables
            DROP  TABLE #ChangesetsToBeDeleted
        END

        SET NOCOUNT OFF
    END;
    GO

    I'm thinking to try below options. Pl guide me if am doing correct or wrong.

    Option 1: disable lock escalation on Fieldvalues table

    ALTER TABLE Questionnaire.FieldValues SET (LOCK_ESCALATION = DISABLE);

    Option 2: Instead of bulk insert using Entity Framework code first approach, ask them try loading in small batches from .net i.e. range inserts which takes only KEY lock and not a Table level lock.

    Option 3: create indexes on foreign key columns

    Option 4: set serializable isolation level inside stored procedure.

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    option 5: don't run both the processes at same time. (But I dont think they ll agree, because this app will be used across users around the world which uses a SQL Azure PaaS db and cannot restrict them not run simultaneously.. or may be not sure, the development team should instrument the code in such a way, if one is running/in-progress other has to wait.

                                            
    Tuesday, June 12, 2018 10:49 AM
  • So process 7c28 acquired a page lock on Questionnaire.FieldValues when running DELETE on that table. Because ChangesetId is a GUID, it is not unlikely that a new INSERT wants to insert a row on the same page, it the clustered index is on the guid, but that is blocked.

    At this point, the process 7c28 wants a shared lock on a page in Questionnaire.Questionnaires to check for FK references, but the bulk-load process has already been there and left its mark.

    It is important when analysing deadlock graphs that locks may come from previous statements in the same transaction.

    Did you review the indexing on the FK columns? (Although, it could be that you are deleting that may locks that you get page locks.)

    Tuesday, June 12, 2018 9:25 PM
  • Yeah Erland, supporting indexes are in place. Explained the team try to change the table accessing in the same order.
    Thursday, June 14, 2018 5:44 PM