none
Why is my query blocking RRS feed

  • Question

  • Hello All,

    I have been struggling with this for so long now :

    Every time I execute this query it goes into blocked state for hours. It completes the first insert but blocks after.     SET XACT_ABORT OFF; there are around 90K records its trying to insert.

    Maybe I am making any silly mistake here ? any pointers please.

     I have already tried NoLock it doesn't help.               

    BEGIN TRAN
            INSERT INTO [dbo].[FactWorkItemHistory]
            (        [WorkItemHistoryBK]
                    ,[WorkItemSK]
                    ,[TeamProjectSK]
    ,dateSK
    ,[RecordCount]
                    ,[RevisionCount]
                    ,[StateChangeCount]
                    ,[LastUpdatedDateTime]
                    ,[RemainingWork]
                    ,[CompletedWork] 
                ,[OriginalEstimate]
    ,[Scheduling_Effort]
            )
            SELECT   ws.[WorkItemBK] + '|1' AS [WorkItemHistoryBK]
                    ,[dbo].[DimWorkItem].[WorkItemSK]
                    ,[dbo].[DimTeamProject].[ProjectNodeSK]
    ,ws.[System.ChangedDate] AS [DateSK]
                     ,1  AS [RecordCount]
                    ,1  AS [RevisionCount]
                    ,ws.[StateChangeCount]
                    ,GETUTCDATE()
                    ,ws.[Microsoft.VSTS.Scheduling.RemainingWork],
    ws.[Microsoft.VSTS.Scheduling.CompletedWork]
    ,ws.[Microsoft.VSTS.Scheduling.OriginalEstimate]
    ,ws.Effort
            FROM        #workingSet AS ws
            LEFT JOIN   [dbo].[FactWorkItemHistory] WITH(HOLDLOCK, UPDLOCK)
                ON      [dbo].[FactWorkItemHistory].[WorkItemHistoryBK] = ws.[WorkItemBK] + '|1'
            LEFT JOIN   [dbo].[DimWorkItem]
        ON      [dbo].[DimWorkItem].[WorkItemBK] = ws.[WorkItemBK]
            LEFT JOIN   [dbo].[DimTeamProject]
                ON  [dbo].[DimTeamProject].[ProjectNodeSK] = ws.[TeamProjectGUID]
            WHERE       [dbo].[FactWorkItemHistory].[WorkItemHistoryBK] IS NULL


    -- Compensating Records.
            SET XACT_ABORT OFF;
          BEGIN TRY
                INSERT INTO [dbo].[FactWorkItemHistory]
                (    [WorkItemHistoryBK]
                    ,[WorkItemSK]
                    ,[TeamProjectSK]
    ,[DateSK]
                     ,[RecordCount]
                    ,[RevisionCount]
                    ,[StateChangeCount]
                    ,[LastUpdatedDateTime]
    ,[RemainingWork]
                    ,[CompletedWork] 
                ,[OriginalEstimate]
    ,[Scheduling_Effort]
                )
                SELECT
                    CONVERT(NVARCHAR(80),ws.[WorkItemHistoryPrevBK])+ '|-1' AS [WorkItemHistoryBK]
                    ,wih.[WorkItemSK]
                    ,wih.[TeamProjectSK]
                    ,ws.[System.ChangedDate] AS [DateSK]
                    ,-1 AS [RecordCount]
                    ,NULL AS [RevisionCount]
                    ,NULL AS [StateChangeCount]
                    ,GETUTCDATE()
                    ,-1 * wih.[RemainingWork]
    ,-1 * wih.[CompletedWork],
    -1 * wih.[OriginalEstimate]
    ,wih.Scheduling_Effort
                FROM #workingSet AS ws
                LEFT JOIN [dbo].[FactWorkItemHistory] AS wih WITH(HOLDLOCK, UPDLOCK)
            ON wih.[WorkItemHistoryBK] = ws.[WorkItemHistoryPrevBK] + '|1'
                LEFT JOIN [dbo].[FactWorkItemHistory] AS wih_c
            ON wih_c.[WorkItemHistoryBK] = ws.[WorkItemHistoryPrevBK] + '|-1'
                WHERE   ws.[WorkItemHistoryPrevBK] IS NOT NULL AND
            wih_c.[WorkItemHistoryBK] IS NULL

           END TRY
           BEGIN CATCH
       -- ROLLBACK TRAN
      END CATCH
            SET XACT_ABORT ON;
           COMMIT TRAN


    Best Regards, Pragati


    Tuesday, June 18, 2019 4:07 PM

Answers

  • I have taken the second insert completely out of transaction and added a nolock hint.

    This seems to work.


    Best Regards, Pragati

    Friday, June 21, 2019 12:20 PM
  • looks like the process is blocking itself.

    A parallel query will appear to block itself during execution (CXPACKET waits). This is due to thread coordination rather than blocking locks. If the query is running longer than you would like, query and index tuning may be the long term solution.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, June 24, 2019 10:57 AM

All replies

  • Hi Pragati,

    Please check if there are locks held on the table which is preventing data to be inserted.

    you can use sys.dm_tran_locks to find that out

    SELECT * FROM sys.dm_tran_locks
      WHERE resource_database_id = DB_ID()
      AND resource_associated_entity_id = OBJECT_ID(N'dbo.yourtablename');

    Additionally you can also check sys.sysprocesses to view the session that is blocking.

    Thanks, Lokesh Vij

    Tuesday, June 18, 2019 4:32 PM
  • There could be few reasons

    1. You are using transaction

    2. You are using query hint (ROWLOCK UPDLOCK)

    You need to see what is blocking your query and then figure it out


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, June 18, 2019 5:42 PM
  • Hi Pragati Sharma,

     

    Please try to use SP_WHO or  use following query to identify blocking in you SQL. Also can check detail of particular SPID by using following command.

     
    SELECT
    db.name DBName,
    tl.request_session_id,
    wt.blocking_session_id,
    OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
    tl.resource_type,
    h1.TEXT AS RequestingText,
    h2.TEXT AS BlockingTest,
    tl.request_mode
    FROM sys.dm_tran_locks AS tl
    INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
    INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
    INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
    INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
    INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
    CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
    CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
    GO
    
    DBCC INPUTBUFFER(56) — Will give you the Event Info.
    
    KILL 56 -- Will kill the session of this id.
    

    Also , there is an article about How to identify blocking in SQL Server, please refer to it.

     

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 19, 2019 2:07 AM
  • Today , the Query runs fine by it self. I don't see the issue anymore.

    Thanks everyone for your response.


    Best Regards, Pragati

    Wednesday, June 19, 2019 11:55 AM
  • Hi Pragati Sharma,

     

    We are glad to hear that they are helpful to you. If you have solved your issue ,please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 20, 2019 7:45 AM
  • Unfortunately , the issue is back today.

    looks like the process is blocking itself.

    I have checked the Query provided by Lokesh Vij and there are entries in table sys.dm_tran_locks with Request Owner type 'Transaction'. I have killed it few times and executed the query again but it always goes in to block state :(


    Best Regards, Pragati

    Thursday, June 20, 2019 3:16 PM
  • If two inserts can be executed as separate transactions. Please try to do that and check!
    • Edited by Lokesh Vij Thursday, June 20, 2019 4:07 PM
    Thursday, June 20, 2019 4:07 PM
  • Hello Lokesh, I Tried separate transactions , but doesn't work. Still goes in lock.

    Best Regards, Pragati



    Friday, June 21, 2019 10:56 AM
  • I have taken the second insert completely out of transaction and added a nolock hint.

    This seems to work.


    Best Regards, Pragati

    Friday, June 21, 2019 12:20 PM
  • Hi Pragati Sharma,

     

    We are glad to hear that it seems to work. If you have solved your issue ,please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Also, if your still need help next time , please create a new posting to ask. 

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 24, 2019 9:56 AM
  • looks like the process is blocking itself.

    A parallel query will appear to block itself during execution (CXPACKET waits). This is due to thread coordination rather than blocking locks. If the query is running longer than you would like, query and index tuning may be the long term solution.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, June 24, 2019 10:57 AM