locked
ReportServerTempDb Deadlocking Issue RRS feed

  • Question

  • SQL Server Version 13.0.4422.0

    I'm having a strange issue with Caching on a server. The ReportServerTempDB.dbo.ExecutionCache and ReportServerTempDB.dbo.SnapshotData are having deadlock issues.

    I have 2 shared schedules (Screenshot) - one for the Cache Refresh and the other for the Cache Expiration. 3 Shared Datasets use both shared schedules.

    Here is the SnapshotData Deadlock graph in XML.

    <deadlock>
      <victim-list>
        <victimProcess id="processdde3da1848" />
      </victim-list>
      <process-list>
        <process id="processdde3da1848" taskpriority="0" logused="0" waitresource="KEY: 6:72057594049462272 (fb940079ce24)" waittime="4331" ownerId="160142773" transactionname="user_transaction" lasttranstarted="2017-06-05T20:45:04.133" XDES="0xdc9304f900" lockMode="S"
        schedulerid="2" kpid="4016" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2017-06-05T20:45:04.137" lastbatchcompleted="2017-06-05T20:45:04.137" lastattention="1900-01-01T00:00:00.137" clientapp="Report Server"
        hostname="SERVERNAMESCRUBBED" hostpid="1728" loginname="LOGINNAMESCRUBBED" isolationlevel="read committed (2)" xactid="160142773" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
          <executionStack>
            <frame procname="ReportServer.dbo.GetDataSetForExecution" line="8" stmtstart="292" stmtend="1978" sqlhandle="0x030005007be4a07f987bee005ca7000001000000000000000000000000000000000000000000000000000000">
              SELECT SN.SnapshotDataID, SN.EffectiveParams, SN.QueryParams, (SELECT CachePolicy.ExpirationFlags FROM CachePolicy WHERE CachePolicy.ReportID = Cat.ItemID), Cat.Property FROM Catalog AS Cat LEFT OUTER JOIN ( SELECT TOP 1 ReportID, SN.SnapshotDataID, EffectiveParams,
              QueryParams FROM [ReportServerTempDB].dbo.ExecutionCache AS EC INNER JOIN [ReportServerTempDB].dbo.SnapshotData AS SN ON EC.SnapshotDataID = SN.SnapshotDataID AND EC.ParamsHash = SN.ParamsHash WHERE AbsoluteExpiration &gt; @now AND SN.ParamsHash =
              @ParamsHash AND EC.ReportID = @ItemID ORDER BY SN.CreatedDate DESC ) as SN ON Cat.ItemID = SN.ReportID WHERE Cat.ItemID = @ItemI </frame>
          </executionStack>
          <inputbuf>
            Proc [Database Id = 5 Object Id = 2141250683] </inputbuf>
        </process>
        <process id="processdd754c9c28" taskpriority="0" logused="792" waitresource="KEY: 6:72057594049593344 (3d62073e1914)" waittime="4332" ownerId="160142771" transactionname="user_transaction" lasttranstarted="2017-06-05T20:45:04.133" XDES="0xdc71733120" lockMode="X"
        schedulerid="3" kpid="6572" status="suspended" spid="90" sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2017-06-05T20:45:04.133" lastbatchcompleted="2017-06-05T20:45:04.133"  lastattention="1900-01-01T00:00:00.133" clientapp="Report Server"
        hostname="SERVERNAMESCRUBBED" hostpid="1728" loginname="LOGINNAMESCRUBBED" isolationlevel="repeatable read (3)" xactid="160142771" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
          <executionStack>
            <frame procname="ReportServer.dbo.AddReportToCache" line="64" stmtstart="4820" stmtend="5544" sqlhandle="0x0300050074fa8b43097bee005ca7000001000000000000000000000000000000000000000000000000000000">
              UPDATE [ReportServerTempDB].dbo.SnapshotData SET PermanentRefcount = PermanentRefcount + 1, IsCached = CONVERT(BIT, 1), TransientRefcount = CASE WHEN @EditSessionTimeout IS NOT NULL THEN TransientRefcount - 1 ELSE TransientRefCount END WHERE SnapshotDataID
              = @SnapshotDataI </frame>
          </executionStack>
          <inputbuf>
            Proc [Database Id = 5 Object Id = 1133247092] </inputbuf>
        </process>
      </process-list>
      <resource-list>
        <keylock hobtid="72057594049462272" dbid="6" objectname="ReportServerTempDB.dbo.ExecutionCache" indexname="IX_SnapshotDataID" id="lockddb4b6be80" mode="X" associatedObjectId="72057594049462272">
          <owner-list>
            <owner id="processdd754c9c28" mode="X" />
          </owner-list>
          <waiter-list>
            <waiter id="processdde3da1848" mode="S" requestType="wait" />
          </waiter-list>
        </keylock>
        <keylock hobtid="72057594049593344" dbid="6" objectname="ReportServerTempDB.dbo.SnapshotData" indexname="IX_SnapshotData" id="lockdcf2bdc700" mode="U" associatedObjectId="72057594049593344">
          <owner-list>
            <owner id="processdde3da1848" mode="S" />
          </owner-list>
          <waiter-list>
            <waiter id="processdd754c9c28" mode="X" requestType="convert" />
          </waiter-list>
        </keylock>
      </resource-list>
    < /deadlock>

    Here is the execution cache deadlock in XML.

    <deadlock>
      <victim-list>
        <victimProcess id="processdde3da1848" />
      </victim-list>
      <process-list>
        <process id="processdde3da1848" taskpriority="0" logused="0" waitresource="KEY: 6:72057594049462272 (fb940079ce24)" waittime="4331" ownerId="160142773" transactionname="user_transaction" lasttranstarted="2017-06-05T20:45:04.133" XDES="0xdc9304f900" lockMode="S"
        schedulerid="2" kpid="4016" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2017-06-05T20:45:04.137" lastbatchcompleted="2017-06-05T20:45:04.137" lastattention="1900-01-01T00:00:00.137" clientapp="Report Server"
        hostname="SERVERNAMESCRUBBED" hostpid="1728" loginname="LOGINNAMESCRUBBED" isolationlevel="read committed (2)" xactid="160142773" currentdb="5" lockTimeout="4294967295"  clientoption1="671088672" clientoption2="128056">
          <executionStack>
            <frame procname="ReportServer.dbo.GetDataSetForExecution" line="8" stmtstart="292" stmtend="1978" sqlhandle="0x030005007be4a07f987bee005ca7000001000000000000000000000000000000000000000000000000000000">
              SELECT SN.SnapshotDataID, SN.EffectiveParams, SN.QueryParams, (SELECT CachePolicy.ExpirationFlags FROM CachePolicy WHERE CachePolicy.ReportID = Cat.ItemID), Cat.Property FROM Catalog AS Cat LEFT OUTER JOIN ( SELECT TOP 1 ReportID, SN.SnapshotDataID, EffectiveParams,
              QueryParams FROM [ReportServerTempDB].dbo.ExecutionCache AS EC INNER JOIN [ReportServerTempDB].dbo.SnapshotData AS SN ON EC.SnapshotDataID = SN.SnapshotDataID AND EC.ParamsHash = SN.ParamsHash WHERE AbsoluteExpiration &gt; @now AND SN.ParamsHash =
              @ParamsHash AND EC.ReportID = @ItemID ORDER BY SN.CreatedDate DESC ) as SN ON Cat.ItemID = SN.ReportID WHERE Cat.ItemID = @ItemI </frame>
          </executionStack>
          <inputbuf>
            Proc [Database Id = 5 Object Id = 2141250683] </inputbuf>
        </process>
        <process id="processdd754c9c28" taskpriority="0" logused="792" waitresource="KEY: 6:72057594049593344 (3d62073e1914)" waittime="4332" ownerId="160142771" transactionname="user_transaction" lasttranstarted="2017-06-05T20:45:04.133" XDES="0xdc71733120" lockMode="X"
        schedulerid="3" kpid="6572" status="suspended" spid="90" sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2017-06-05T20:45:04.133" lastbatchcompleted="2017-06-05T20:45:04.133" lastattention="1900-01-01T00:00:00.133" clientapp="Report Server"
        hostname="SERVERNAMESCRUBBED" hostpid="1728" loginname="LOGINNAMESCRUBBED" isolationlevel="repeatable read (3)" xactid="160142771" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
          <executionStack>
            <frame procname="ReportServer.dbo.AddReportToCache" line="64" stmtstart="4820" stmtend="5544" sqlhandle="0x0300050074fa8b43097bee005ca7000001000000000000000000000000000000000000000000000000000000">
              UPDATE [ReportServerTempDB].dbo.SnapshotData SET PermanentRefcount = PermanentRefcount + 1, IsCached = CONVERT(BIT, 1), TransientRefcount = CASE WHEN @EditSessionTimeout IS NOT NULL THEN TransientRefcount - 1 ELSE TransientRefCount END WHERE SnapshotDataID
              = @SnapshotDataI </frame>
          </executionStack>
          <inputbuf>
            Proc [Database Id = 5 Object Id = 1133247092] </inputbuf>
        </process>
      </process-list>
      <resource-list>
        <keylock hobtid="72057594049462272" dbid="6" objectname="ReportServerTempDB.dbo.ExecutionCache" indexname="IX_SnapshotDataID" id="lockddb4b6be80" mode="X" associatedObjectId="72057594049462272">
          <owner-list>
            <owner id="processdd754c9c28" mode="X" />
          </owner-list>
          <waiter-list>
            <waiter id="processdde3da1848" mode="S" requestType="wait" />
          </waiter-list>
        </keylock>
        <keylock hobtid="72057594049593344" dbid="6" objectname="ReportServerTempDB.dbo.SnapshotData" indexname="IX_SnapshotData" id="lockdcf2bdc700" mode="U" associatedObjectId="72057594049593344">
          <owner-list>
            <owner id="processdde3da1848" mode="S" />
          </owner-list>
          <waiter-list>
            <waiter id="processdd754c9c28" mode="X" requestType="convert" />
          </waiter-list>
        </keylock>
      </resource-list>
    < /deadlock>

    Any ideas why the system is tripping over itself? Any solution to prevent this?


    Please 'Mark as Answer' if found helpful




    Please 'Mark as Answer' if found helpful - Chris@tier-1-support

    Tier 1 Support

    Tuesday, June 6, 2017 3:06 PM

All replies

  • Hi Tier 1 Support,

    Based on my research, this deadlock might be related to your data amount. So you could try to optimize the dataset structure, try to reduce the complexed expression in the dataset. In addition, you could try to increase the timeout of dataset and when you create a cache for shared dataset, you could try to modify the schedule time to make sure the refresh schedule should be greater than query time. You  can refer to Caching Shared Datasets (SSRS) for details.

    In addition, for stop the deadlock, you could try to use a NOLOCK table hint in the SELECT statement, to prevent it from acquiring shared locks. Or use range locks  when reading data, in place of the row or page level locking used under READ COMMITTED isolation. You can refer to Handling Deadlocks in SQL Server for details.

    If you have any question, please feel free to ask.

    Best regards,

    Zoe Zhi


    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 7, 2017 5:42 AM