sql-server - 部署期间的 SSRS 死锁

问题描述

我想知道是否有人可以提供帮助。我没有从 MS 那里得到太多帮助。

设想

一个具有多个文件夹的报表服务器（SQL 2019 和 SSRS 2019）根据此图像报表服务器
运行 Powershell 脚本（见下文 - 请注意，我确实尝试过 ReportingServicesTools，但结果相同）

        $ReportFolderName = "/ReportFolder_SSRS";
        $ReportServerIP   = "localhost";
        $ReportsUserName  = "ReportUser";
        $ReportsUserPass  = "ReportUserPassword";

        $ReportFolderName = "/$($ReportFolderName)"
        $ReportFolderName = $ReportFolderName.Replace("//","/");

        if([System.String]::IsNullOrWhiteSpace($ReportsUserName) -or [System.String]::IsNullOrWhiteSpace($ReportsUserPass))
        {
            $Proxy = New-WebServiceProxy -Uri "http://$($ReportServerIP)/ReportServer/ReportService2010.asmx?WSDL" -UseDefaultCredential -ErrorAction Stop;
        }
        else
        {
        [System.Management.Automation.PSCredential]$Cred = $null;
        [securestring]$Secure = ConvertTo-SecureString -String $ReportsUserPass -AsPlainText -Force
        $Cred   = New-Object System.Management.Automation.PSCredential -ArgumentList $ReportsUserName,$Secure;
        $Proxy  = New-WebServiceProxy -Uri "http://$($ReportServerIP)/ReportServer/ReportService2010.asmx?WSDL" -Credential $Cred -ErrorAction Stop;
        }
    
        $DeleteAll = @(Get-RsFolderContent -ReportServerUri "http://$($ReportServerIP)/ReportServer/" -Path "$($ReportFolderName)")
    
        $CountReports  = $($DeleteAll | Where-Object { $_.TypeName -eq "Report" }).Count;
        $CountDataSets  = $($DeleteAll | Where-Object { $_.TypeName -ne "Report" }).Count;

        Write-Host "Deleting Reports from $($ReportFolderName)"
        foreach($item in $DeleteAll)
        {
        $count++;
        Write-Host "[$($item.TypeName)] Delete $($item.Name) from $($item.Path) ... ";
        $Proxy.DeleteItem($item.Path)
        }
        Write-Host "##[section] Deleted $($CountReports) Report$(if ($CountReports -ne 1) {"s"}) and $($CountDataSets) Data Source$(if ($CountDataSets -ne 1) {"s"})";

powershell 脚本被编译成在管道中运行的 Azure 任务
报表全部同时部署（必填）
报告用户有权访问报告数据库，并且是服务器上的本地管理员

通过我的测试，我部署到 4 个站点，但随后最多 3 个站点将失败并出现以下错误

2021-02-19T09：05：00.7668607Z ##[error] 使用“1”参数调用“DeleteItem”的异常：“System.Web.Services.Protocols.SoapException：报表服务器数据库中发生错误。这可能是由于数据库中的连接失败、超时或磁盘不足。---> Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: 报表服务器数据库中发生错误。这可能是由于连接失败、超时或数据库中的磁盘条件低。---> System.Data.SqlClient.SqlException: Transaction (Process ID 65) was deadlocked on lock resources with another process and has been selected as deadlockvictims.重新运行事务。

我们增加了超时，重新安装了报表服务器和报表服务，以确保没有发生损坏并且代理是正确的。

死锁发生在随机 ID 上，并不特定于该 ID 进程

上面的 Write-RsCatalogItem 和 Remove-RsCatalogItem 已发生这种情况，并且仅在报表服务器发生多个部署时发生。

任何帮助将不胜感激

** 编辑：这是我们运行的 1222 跟踪的提取，它已经告诉我们我们所知道的。

02/19/2021 12:20:46,spid23s,Unknown,waiter id=process195e1037468 mode=S requestType=wait
02/19/2021 12:20:46,spid23s,Unknown,waiter-list
02/19/2021 12:20:46,spid23s,Unknown,owner id=process195e106f468 mode=X
02/19/2021 12:20:46,spid23s,Unknown,owner-list
02/19/2021 12:20:46,spid23s,Unknown,keylock hobtid=72057594052280320 dbid=5 objectname=Reports.dbo.Catalog indexname=IX_Policy id=lock195c438e080 mode=X associatedObjectId=72057594052280320
02/19/2021 12:20:46,spid23s,Unknown,waiter id=process195e106f468 mode=U requestType=wait
02/19/2021 12:20:46,spid23s,Unknown,waiter-list
02/19/2021 12:20:46,spid23s,Unknown,owner id=process195e1037468 mode=X
02/19/2021 12:20:46,spid23s,Unknown,owner-list
02/19/2021 12:20:46,spid23s,Unknown,keylock hobtid=72057594047299584 dbid=5 objectname=Reports.dbo.SnapshotData indexname=PK_SnapshotData id=lock195d11c6280 mode=X associatedObjectId=72057594047299584
02/19/2021 12:20:46,spid23s,Unknown,resource-list
02/19/2021 12:20:46,spid23s,Unknown,Proc [Database Id = 5 Object Id = 1933249942]
02/19/2021 12:20:46,spid23s,Unknown,inputbuf
02/19/2021 12:20:46,spid23s,Unknown,EXEC CleanOrphanedPolicie
02/19/2021 12:20:46,spid23s,Unknown,frame procname=Reports.dbo.DeleteObject line=194 stmtstart=10482 stmtend=10532 sqlhandle=0x03000500960d3b7369c60401d1ac000001000000000000000000000000000000000000000000000000000000
02/19/2021 12:20:46,spid23s,Unknown,NOT EXISTS (SELECT ItemID FROM [Catalog] WHERE [Catalog].[PolicyID] = [Policies].[PolicyID]
02/19/2021 12:20:46,spid23s,Unknown,AND
02/19/2021 12:20:46,spid23s,Unknown,[Policies].[PolicyFlag] = 0
02/19/2021 12:20:46,spid23s,Unknown,WHERE
02/19/2021 12:20:46,spid23s,Unknown,[Policies]
02/19/2021 12:20:46,spid23s,Unknown,DELETE
02/19/2021 12:20:46,spid23s,Unknown,frame procname=Reports.dbo.CleanOrphanedPolicies line=5 stmtstart=196 stmtend=524 sqlhandle=0x030005001f6768460dc60401d1ac000001000000000000000000000000000000000000000000000000000000
02/19/2021 12:20:46,spid23s,Unknown,executionStack
02/19/2021 12:20:46,spid23s,Unknown,process id=process195e1037468 taskpriority=0 logused=23524 waitresource=KEY: 5:72057594052280320 (2b682d85aa08) waittime=2373 ownerId=6759604 transactionname=user_transaction lasttranstarted=2021-02-19T12:20:44.180 XDES=0x195e017c490 lockMode=S schedulerid=4 kpid=74580 status=suspended spid=51 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2021-02-19T12:20:44.190 lastbatchcompleted=2021-02-19T12:20:44.190 lastattention=1900-01-01T00:00:00.190 clientapp=Report Server hostname={Hidden} hostpid=74372 loginname=ReportUser isolationlevel=read committed (2) xactid=6759604 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
02/19/2021 12:20:46,spid23s,Unknown,Proc [Database Id = 5 Object Id = 1933249942]
02/19/2021 12:20:46,spid23s,Unknown,inputbuf
02/19/2021 12:20:46,spid23s,Unknown,(R.Path = @Path OR R.Path LIKE @Prefix ESCAPE '*'
02/19/2021 12:20:46,spid23s,Unknown,WHERE
02/19/2021 12:20:46,spid23s,Unknown,INNER JOIN [SnapshotData] AS SD ON R.Intermediate = SD.SnapshotDataID
02/19/2021 12:20:46,spid23s,Unknown,Catalog AS R WITH (XLOCK)
02/19/2021 12:20:46,spid23s,Unknown,FROM
02/19/2021 12:20:46,spid23s,Unknown,TransientRefcount = TransientRefcount + case when R.Type = 8 then 1 ELSE 0 END
02/19/2021 12:20:46,spid23s,Unknown,ExpirationDate = case when R.Type = 8 then DATEADD(d<c/> 14<c/> GETDATE()) ELSE ExpirationDate END<c/>
02/19/2021 12:20:46,spid23s,Unknown,-- to fix VSTS 384486 keep shared dataset compiled definition for 14 days
02/19/2021 12:20:46,spid23s,Unknown,SET PermanentRefcount = PermanentRefcount - 1<c/>
02/19/2021 12:20:46,spid23s,Unknown,UPDATE SnapshotData
02/19/2021 12:20:46,spid23s,Unknown,frame procname=Reports.dbo.DeleteObject line=54 stmtstart=2638 stmtend=3638 sqlhandle=0x03000500960d3b7369c60401d1ac000001000000000000000000000000000000000000000000000000000000
02/19/2021 12:20:46,spid23s,Unknown,executionStack
02/19/2021 12:20:46,spid23s,Unknown,process id=process195e106f468 taskpriority=0 logused=0 waitresource=KEY: 5:72057594047299584 (7166b320963a) waittime=2382 ownerId=6759842 transactionname=user_transaction lasttranstarted=2021-02-19T12:20:44.210 XDES=0x195d5adc490 lockMode=U schedulerid=11 kpid=55224 status=suspended spid=55 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2021-02-19T12:20:44.213 lastbatchcompleted=2021-02-19T12:20:44.213 lastattention=1900-01-01T00:00:00.213 clientapp=Report Server hostname={Hidden} hostpid=74372 loginname=ReportUser isolationlevel=read committed (2) xactid=6759842 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
02/19/2021 12:20:46,spid23s,Unknown,process-list
02/19/2021 12:20:46,spid23s,Unknown,deadlock victim=process195e106f468
02/19/2021 12:20:46,spid23s,Unknown,deadlock-list
02/19/2021 12:20:44,spid40s,Unknown,waiter id=process195e1037468 mode=S requestType=wait
02/19/2021 12:20:44,spid40s,Unknown,waiter-list
02/19/2021 12:20:44,spid40s,Unknown,owner id=process195e105eca8 mode=X
02/19/2021 12:20:44,spid40s,Unknown,owner-list
02/19/2021 12:20:44,spid40s,Unknown,keylock hobtid=72057594052280320 dbid=5 objectname=Reports.dbo.Catalog indexname=IX_Policy id=lock195ab386a80 mode=X associatedObjectId=72057594052280320
02/19/2021 12:20:44,spid40s,Unknown,waiter id=process195e105eca8 mode=U requestType=wait
02/19/2021 12:20:44,spid40s,Unknown,waiter-list
02/19/2021 12:20:44,spid40s,Unknown,owner id=process195e1037468 mode=X
02/19/2021 12:20:44,spid40s,Unknown,owner-list
02/19/2021 12:20:44,spid40s,Unknown,keylock hobtid=72057594047299584 dbid=5 objectname=Reports.dbo.SnapshotData indexname=PK_SnapshotData id=lock195b5e2ee00 mode=X associatedObjectId=72057594047299584
02/19/2021 12:20:44,spid40s,Unknown,resource-list
02/19/2021 12:20:44,spid40s,Unknown,Proc [Database Id = 5 Object Id = 1933249942]
02/19/2021 12:20:44,spid40s,Unknown,inputbuf
02/19/2021 12:20:44,spid40s,Unknown,EXEC CleanOrphanedPolicie
02/19/2021 12:20:44,spid40s,Unknown,frame procname=Reports.dbo.DeleteObject line=194 stmtstart=10482 stmtend=10532 sqlhandle=0x03000500960d3b7369c60401d1ac000001000000000000000000000000000000000000000000000000000000
02/19/2021 12:20:44,spid40s,Unknown,NOT EXISTS (SELECT ItemID FROM [Catalog] WHERE [Catalog].[PolicyID] = [Policies].[PolicyID]
02/19/2021 12:20:44,spid40s,Unknown,AND
02/19/2021 12:20:44,spid40s,Unknown,[Policies].[PolicyFlag] = 0
02/19/2021 12:20:44,spid40s,Unknown,WHERE
02/19/2021 12:20:44,spid40s,Unknown,[Policies]
02/19/2021 12:20:44,spid40s,Unknown,DELETE
02/19/2021 12:20:44,spid40s,Unknown,frame procname=Reports.dbo.CleanOrphanedPolicies line=5 stmtstart=196 stmtend=524 sqlhandle=0x030005001f6768460dc60401d1ac000001000000000000000000000000000000000000000000000000000000
02/19/2021 12:20:44,spid40s,Unknown,executionStack
02/19/2021 12:20:44,spid40s,Unknown,process id=process195e1037468 taskpriority=0 logused=28364 waitresource=KEY: 5:72057594052280320 (ed76754105b8) waittime=4998 ownerId=6758288 transactionname=user_transaction lasttranstarted=2021-02-19T12:20:36.750 XDES=0x195e017c490 lockMode=S schedulerid=4 kpid=74580 status=suspended spid=51 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2021-02-19T12:20:36.757 lastbatchcompleted=2021-02-19T12:20:36.753 lastattention=1900-01-01T00:00:00.753 clientapp=Report Server hostname={Hidden} hostpid=74372 loginname=ReportUser isolationlevel=read committed (2) xactid=6758288 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
02/19/2021 12:20:44,spid40s,Unknown,Proc [Database Id = 5 Object Id = 1933249942]
02/19/2021 12:20:44,spid40s,Unknown,inputbuf
02/19/2021 12:20:44,spid40s,Unknown,(R.Path = @Path OR R.Path LIKE @Prefix ESCAPE '*'
02/19/2021 12:20:44,spid40s,Unknown,WHERE
02/19/2021 12:20:44,spid40s,Unknown,INNER JOIN [SnapshotData] AS SD ON R.Intermediate = SD.SnapshotDataID
02/19/2021 12:20:44,spid40s,Unknown,Catalog AS R WITH (XLOCK)
02/19/2021 12:20:44,spid40s,Unknown,FROM
02/19/2021 12:20:44,spid40s,Unknown,TransientRefcount = TransientRefcount + case when R.Type = 8 then 1 ELSE 0 END
02/19/2021 12:20:44,spid40s,Unknown,ExpirationDate = case when R.Type = 8 then DATEADD(d<c/> 14<c/> GETDATE()) ELSE ExpirationDate END<c/>
02/19/2021 12:20:44,spid40s,Unknown,-- to fix VSTS 384486 keep shared dataset compiled definition for 14 days
02/19/2021 12:20:44,spid40s,Unknown,SET PermanentRefcount = PermanentRefcount - 1<c/>
02/19/2021 12:20:44,spid40s,Unknown,UPDATE SnapshotData
02/19/2021 12:20:44,spid40s,Unknown,frame procname=Reports.dbo.DeleteObject line=54 stmtstart=2638 stmtend=3638 sqlhandle=0x03000500960d3b7369c60401d1ac000001000000000000000000000000000000000000000000000000000000
02/19/2021 12:20:44,spid40s,Unknown,executionStack
02/19/2021 12:20:44,spid40s,Unknown,process id=process195e105eca8 taskpriority=0 logused=0 waitresource=KEY: 5:72057594047299584 (a7101ea26f8a) waittime=4998 ownerId=6758993 transactionname=user_transaction lasttranstarted=2021-02-19T12:20:38.020 XDES=0x195cacac490 lockMode=U schedulerid=9 kpid=76660 status=suspended spid=60 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2021-02-19T12:20:38.020 lastbatchcompleted=2021-02-19T12:20:38.020 lastattention=1900-01-01T00:00:00.020 clientapp=Report Server hostname={Hidden} hostpid=74372 loginname=ReportUser isolationlevel=read committed (2) xactid=6758993 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
02/19/2021 12:20:44,spid40s,Unknown,process-list
02/19/2021 12:20:44,spid40s,Unknown,deadlock victim=process195e105eca8
02/19/2021 12:20:44,spid40s,Unknown,deadlock-list
02/19/2021 12:20:39,spid26s,Unknown,waiter id=process195e1037468 mode=S requestType=wait
02/19/2021 12:20:39,spid26s,Unknown,waiter-list
02/19/2021 12:20:39,spid26s,Unknown,owner id=process195e106f468 mode=X
02/19/2021 12:20:39,spid26s,Unknown,owner-list
02/19/2021 12:20:39,spid26s,Unknown,keylock hobtid=72057594052280320 dbid=5 objectname=Reports.dbo.Catalog indexname=IX_Policy id=lock195aabfe180 mode=X associatedObjectId=72057594052280320
02/19/2021 12:20:39,spid26s,Unknown,waiter id=process195e106f468 mode=U requestType=wait
02/19/2021 12:20:39,spid26s,Unknown,waiter-list
02/19/2021 12:20:39,spid26s,Unknown,owner id=process195e1037468 mode=X
02/19/2021 12:20:39,spid26s,Unknown,owner-list
02/19/2021 12:20:39,spid26s,Unknown,keylock hobtid=72057594047299584 dbid=5 objectname=Reports.dbo.SnapshotData indexname=PK_SnapshotData id=lock195b5e2ee00 mode=X associatedObjectId=72057594047299584
02/19/2021 12:20:39,spid26s,Unknown,resource-list
02/19/2021 12:20:39,spid26s,Unknown,Proc [Database Id = 5 Object Id = 1933249942]
02/19/2021 12:20:39,spid26s,Unknown,inputbuf
02/19/2021 12:20:39,spid26s,Unknown,EXEC CleanOrphanedPolicie
02/19/2021 12:20:39,spid26s,Unknown,frame procname=Reports.dbo.DeleteObject line=194 stmtstart=10482 stmtend=10532 sqlhandle=0x03000500960d3b7369c60401d1ac000001000000000000000000000000000000000000000000000000000000
02/19/2021 12:20:39,spid26s,Unknown,NOT EXISTS (SELECT ItemID FROM [Catalog] WHERE [Catalog].[PolicyID] = [Policies].[PolicyID]
02/19/2021 12:20:39,spid26s,Unknown,AND
02/19/2021 12:20:39,spid26s,Unknown,[Policies].[PolicyFlag] = 0
02/19/2021 12:20:39,spid26s,Unknown,WHERE
02/19/2021 12:20:39,spid26s,Unknown,[Policies]
02/19/2021 12:20:39,spid26s,Unknown,DELETE
02/19/2021 12:20:39,spid26s,Unknown,frame procname=Reports.dbo.CleanOrphanedPolicies line=5 stmtstart=196 stmtend=524 sqlhandle=0x030005001f6768460dc60401d1ac000001000000000000000000000000000000000000000000000000000000
02/19/2021 12:20:39,spid26s,Unknown,executionStack
02/19/2021 12:20:39,spid26s,Unknown,process id=process195e1037468 taskpriority=0 logused=28364 waitresource=KEY: 5:72057594052280320 (d8f7d57a33e8) waittime=2187 ownerId=6758288 transactionname=user_transaction lasttranstarted=2021-02-19T12:20:36.750 XDES=0x195e017c490 lockMode=S schedulerid=4 kpid=74580 status=suspended spid=51 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2021-02-19T12:20:36.757 lastbatchcompleted=2021-02-19T12:20:36.753 lastattention=1900-01-01T00:00:00.753 clientapp=Report Server hostname={Hidden} hostpid=74372 loginname=ReportUser isolationlevel=read committed (2) xactid=6758288 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
02/19/2021 12:20:39,spid26s,Unknown,Proc [Database Id = 5 Object Id = 1933249942]
02/19/2021 12:20:39,spid26s,Unknown,inputbuf
02/19/2021 12:20:39,spid26s,Unknown,(R.Path = @Path OR R.Path LIKE @Prefix ESCAPE '*'
02/19/2021 12:20:39,spid26s,Unknown,WHERE
02/19/2021 12:20:39,spid26s,Unknown,INNER JOIN [SnapshotData] AS SD ON R.Intermediate = SD.SnapshotDataID
02/19/2021 12:20:39,spid26s,Unknown,Catalog AS R WITH (XLOCK)
02/19/2021 12:20:39,spid26s,Unknown,FROM
02/19/2021 12:20:39,spid26s,Unknown,TransientRefcount = TransientRefcount + case when R.Type = 8 then 1 ELSE 0 END
02/19/2021 12:20:39,spid26s,Unknown,ExpirationDate = case when R.Type = 8 then DATEADD(d<c/> 14<c/> GETDATE()) ELSE ExpirationDate END<c/>
02/19/2021 12:20:39,spid26s,Unknown,-- to fix VSTS 384486 keep shared dataset compiled definition for 14 days
02/19/2021 12:20:39,spid26s,Unknown,SET PermanentRefcount = PermanentRefcount - 1<c/>
02/19/2021 12:20:39,spid26s,Unknown,UPDATE SnapshotData
02/19/2021 12:20:39,spid26s,Unknown,frame procname=Reports.dbo.DeleteObject line=54 stmtstart=2638 stmtend=3638 sqlhandle=0x03000500960d3b7369c60401d1ac000001000000000000000000000000000000000000000000000000000000
02/19/2021 12:20:39,spid26s,Unknown,executionStack
02/19/2021 12:20:39,spid26s,Unknown,process id=process195e106f468 taskpriority=0 logused=0 waitresource=KEY: 5:72057594047299584 (a7101ea26f8a) waittime=2253 ownerId=6758401 transactionname=user_transaction lasttranstarted=2021-02-19T12:20:36.783 XDES=0x195d5adc490 lockMode=U schedulerid=11 kpid=55224 status=suspended spid=55 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2021-02-19T12:20:36.783 lastbatchcompleted=2021-02-19T12:20:36.783 lastattention=1900-01-01T00:00:00.783 clientapp=Report Server hostname={Hidden} hostpid=74372 loginname=ReportUser isolationlevel=read committed (2) xactid=6758401 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
02/19/2021 12:20:39,spid26s,Unknown,process-list
02/19/2021 12:20:39,spid26s,Unknown,deadlock victim=process195e106f468
02/19/2021 12:20:39,spid26s,Unknown,deadlock-list
02/19/2021 12:17:27,spid58,Unknown,DBCC TRACEON 1222<c/> server process ID (SPID) 58. This is an informational message only; no user action is required.

还附上了 SQL Profiler 输出

xml_report
<deadlock>
    <victim-list> <victimProcess id="process195e105f468" /> </victim-list>
    <process-list>
        <process
            id="process195e105f468"
            taskpriority="0"
            logused="508"
            waitresource="KEY: 5:72057594045136896 (0a1f1722cb01)"
            waittime="691"
            ownerId="14764658"
            transactionname="user_transaction"
            lasttranstarted="2021-02-22T08:42:24.593"
            XDES="0x195d5b28490"
            lockMode="U"
            schedulerid="9"
            kpid="36768"
            status="suspended"
            spid="68"
            sbid="0"
            ecid="0"
            priority="0"
            trancount="2"
            lastbatchstarted="2021-02-22T08:42:24.597"
            lastbatchcompleted="2021-02-22T08:42:24.597"
            lastattention="1900-01-01T00:00:00.597"
            clientapp="Report Server"
            hostname="{HOSTNAME HIDDEN}"
            hostpid="66240"
            loginname="{REPORTS USER HIDDEN}"
            isolationlevel="read committed (2)"
            xactid="14764658"
            currentdb="5"
            lockTimeout="4294967295"
            clientoption1="671088672"
            clientoption2="128056"
        >
            <executionStack>
                <frame procname="Reports.dbo.DeleteObject" line="92" stmtstart="5106" stmtend="5602" sqlhandle="0x03000500960d3b7369c60401d1ac000001000000000000000000000000000000000000000000000000000000">
                    UPDATE [DataSource] SET [Flags] = [Flags] &amp; 0x7FFFFFFD, -- broken link [Link] = NULL FROM [Catalog] AS C INNER JOIN [DataSource] AS DS ON C.[ItemID] = DS.[Link] WHERE (C.Path = @Path OR C.Path LIKE @Prefix ESCAPE
                    &apos;*&apos;
                </frame>
            </executionStack>
            <inputbuf> Proc [Database Id = 5 Object Id = 1933249942] </inputbuf>
        </process>
        <process
            id="process195e1067088"
            taskpriority="0"
            logused="32272"
            waitresource="KEY: 5:72057594052280320 (ec7e3c594ed4)"
            waittime="689"
            ownerId="14764660"
            transactionname="user_transaction"
            lasttranstarted="2021-02-22T08:42:24.593"
            XDES="0x195d9c00490"
            lockMode="S"
            schedulerid="10"
            kpid="38608"
            status="suspended"
            spid="69"
            sbid="0"
            ecid="0"
            priority="0"
            trancount="2"
            lastbatchstarted="2021-02-22T08:42:24.597"
            lastbatchcompleted="2021-02-22T08:42:24.597"
            lastattention="1900-01-01T00:00:00.597"
            clientapp="Report Server"
            hostname="{HOSTNAME HIDDEN}"
            hostpid="66240"
            loginname="{REPORTS USER HIDDEN}"
            isolationlevel="read committed (2)"
            xactid="14764660"
            currentdb="5"
            lockTimeout="4294967295"
            clientoption1="671088672"
            clientoption2="128056"
        >
            <executionStack>
                <frame procname="Reports.dbo.CleanOrphanedPolicies" line="5" stmtstart="196" stmtend="524" sqlhandle="0x030005001f6768460dc60401d1ac000001000000000000000000000000000000000000000000000000000000">
                    DELETE [Policies] WHERE [Policies].[PolicyFlag] = 0 AND NOT EXISTS (SELECT ItemID FROM [Catalog] WHERE [Catalog].[PolicyID] = [Policies].[PolicyID]
                </frame>
                <frame procname="Reports.dbo.DeleteObject" line="194" stmtstart="10482" stmtend="10532" sqlhandle="0x03000500960d3b7369c60401d1ac000001000000000000000000000000000000000000000000000000000000">
                    EXEC CleanOrphanedPolicie
                </frame>
            </executionStack>
            <inputbuf> Proc [Database Id = 5 Object Id = 1933249942] </inputbuf>
        </process>
    </process-list>
    <resource-list>
        <keylock hobtid="72057594045136896" dbid="5" objectname="Reports.dbo.DataSource" indexname="PK_DataSource" id="lock195bdb35180" mode="X" associatedObjectId="72057594045136896">
            <owner-list> <owner id="process195e1067088" mode="X" /> </owner-list> <waiter-list> <waiter id="process195e105f468" mode="U" requestType="wait" /> </waiter-list>
        </keylock>
        <keylock hobtid="72057594052280320" dbid="5" objectname="Reports.dbo.Catalog" indexname="IX_Policy" id="lock195c444a200" mode="X" associatedObjectId="72057594052280320">
            <owner-list> <owner id="process195e105f468" mode="X" /> </owner-list> <waiter-list> <waiter id="process195e1067088" mode="S" requestType="wait" /> </waiter-list>
        </keylock>
    </resource-list>
</deadlock>

*** 编辑更多：我还应该补充一点，这在 SQL Webservice URL 方法和 SQL 2019 Web API 方法中都发生了。

标签： sql-serverpowershellreporting-servicesssrs-2019

sql-server - 部署期间的 SSRS 死锁

问题描述

解决方案

推荐阅读