<deadlock>
<victim-list>
<victimProcess id="process1e08743848" />
<victimProcess id="process8bb66cca8" />
</victim-list>
<process-list>
<process id="process1e08743848" taskpriority="0" logused="0" waitresource="KEY: 6:72057594043301888 (975922a7848b)" waittime="1632" ownerId="2391059109" transactionname="user_transaction" lasttranstarted="2021-04-02T12:35:07.783" XDES="0xe30748970" lockMode="U" schedulerid="8" kpid="39844" status="suspended" spid="93" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-04-02T12:35:07.783" lastbatchcompleted="2021-04-02T12:35:07.783" lastattention="1900-01-01T00:00:00.783" clientapp="Report Server" hostname="SL01537" hostpid="24252" loginname="NT SERVICE\ReportServer" isolationlevel="read committed (2)" xactid="2391059109" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="ReportServer.dbo.DereferenceSessionSnapshot" line="16" stmtstart="752" stmtend="1302" sqlhandle="0x030005001c5c8438b3fb3201d4a6000001000000000000000000000000000000000000000000000000000000"> UPDATE SN SET TransientRefcount = TransientRefcount - 1 FROM [ReportServerTempDB].dbo.SnapshotData AS SN INNER JOIN [ReportServerTempDB].dbo.SessionData AS SE ON SN.SnapshotDataID = SE.SnapshotDataID WHERE SE.SessionID = @SessionID AND SE.OwnerID = @OwnerI </frame>
<frame procname="ReportServer.dbo.ClearSessionSnapshot" line="13" stmtstart="590" stmtend="700" sqlhandle="0x0300050056c60d42c0fb3201d4a6000001000000000000000000000000000000000000000000000000000000"> EXEC DereferenceSessionSnapshot @SessionID, @OwnerID </frame>
</executionStack>
<inputbuf> Proc [Database Id = 5 Object Id = 1108198998] </inputbuf>
</process>
<process id="process8bb66cca8" taskpriority="0" logused="0" waitresource="KEY: 6:72057594043301888 (975922a7848b)" waittime="1632" ownerId="2391059218" transactionname="user_transaction" lasttranstarted="2021-04-02T12:35:07.843" XDES="0x2d9191a040" lockMode="U" schedulerid="1" kpid="22812" status="suspended" spid="77" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-04-02T12:35:07.843" lastbatchcompleted="2021-04-02T12:35:07.843" lastattention="1900-01-01T00:00:00.843" clientapp="Report Server" hostname="SL01537" hostpid="24252" loginname="NT SERVICE\ReportServer" isolationlevel="read committed (2)" xactid="2391059218" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="ReportServer.dbo.DereferenceSessionSnapshot" line="16" stmtstart="752" stmtend="1302" sqlhandle="0x030005001c5c8438b3fb3201d4a6000001000000000000000000000000000000000000000000000000000000"> UPDATE SN SET TransientRefcount = TransientRefcount - 1 FROM [ReportServerTempDB].dbo.SnapshotData AS SN INNER JOIN [ReportServerTempDB].dbo.SessionData AS SE ON SN.SnapshotDataID = SE.SnapshotDataID WHERE SE.SessionID = @SessionID AND SE.OwnerID = @OwnerI </frame>
<frame procname="ReportServer.dbo.ClearSessionSnapshot" line="13" stmtstart="590" stmtend="700" sqlhandle="0x0300050056c60d42c0fb3201d4a6000001000000000000000000000000000000000000000000000000000000"> EXEC DereferenceSessionSnapshot @SessionID, @OwnerID </frame>
</executionStack>
<inputbuf> Proc [Database Id = 5 Object Id = 1108198998] </inputbuf>
</process>
<process id="process22a01908c8" taskpriority="0" logused="592" waitresource="KEY: 6:72057594040745984 (aed6c0e5137d)" waittime="1632" ownerId="2391059166" transactionname="user_transaction" lasttranstarted="2021-04-02T12:35:07.810" XDES="0x19c62630a0" lockMode="X" schedulerid="2" kpid="38184" status="suspended" spid="73" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-04-02T12:35:07.810" lastbatchcompleted="2021-04-02T12:35:07.810" lastattention="1900-01-01T00:00:00.810" clientapp="Report Server" hostname="SL01537" hostpid="24252" loginname="NT SERVICE\ReportServer" isolationlevel="read committed (2)" xactid="2391059166" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="ReportServer.dbo.ClearSessionSnapshot" line="15" stmtstart="702" stmtend="1426" sqlhandle="0x0300050056c60d42c0fb3201d4a6000001000000000000000000000000000000000000000000000000000000"> UPDATE SE SET SE.SnapshotDataID = null, SE.IsPermanentSnapshot = null, SE.SnapshotExpirationDate = null, SE.ShowHideInfo = null, SE.HasInteractivity = null, SE.AutoRefreshSeconds = null, SE.Expiration = @Expiration FROM [ReportServerTempDB].dbo.SessionData AS SE WHERE SE.SessionID = @SessionID AND SE.OwnerID = @OwnerI </frame>
</executionStack>
<inputbuf> Proc [Database Id = 5 Object Id = 1108198998] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594043301888" dbid="6" objectname="ReportServerTempDB.dbo.SnapshotData" indexname="IX_SnapshotData" id="lock2a9c434800" mode="X" associatedObjectId="72057594043301888">
<owner-list>
<owner id="process8bb66cca8" mode="U" requestType="wait" />
</owner-list>
<waiter-list>
<waiter id="process1e08743848" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594043301888" dbid="6" objectname="ReportServerTempDB.dbo.SnapshotData" indexname="IX_SnapshotData" id="lock2a9c434800" mode="X" associatedObjectId="72057594043301888">
<owner-list>
<owner id="process22a01908c8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process8bb66cca8" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594040745984" dbid="6" objectname="ReportServerTempDB.dbo.SessionData" indexname="IDX_SessionData" id="lock2baca83f00" mode="U" associatedObjectId="72057594040745984">
<owner-list>
<owner id="process1e08743848" mode="S" />
</owner-list>
<waiter-list>
<waiter id="process22a01908c8" mode="X" requestType="convert" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>

CREATE PROCEDURE [dbo].[ClearSessionSnapshot]
@SessionID as varchar(32),
@OwnerSid as varbinary(85) = NULL,
@OwnerName as nvarchar(260),
@AuthType as int,
@Expiration as datetime
AS

DECLARE @OwnerID uniqueidentifier
EXEC GetUserID @OwnerSid, @OwnerName, @AuthType, @OwnerID OUTPUT

EXEC DereferenceSessionSnapshot @SessionID, @OwnerID

UPDATE SE
SET
SE.SnapshotDataID = null,
SE.IsPermanentSnapshot = null,
SE.SnapshotExpirationDate = null,
SE.ShowHideInfo = null,
SE.HasInteractivity = null,
SE.AutoRefreshSeconds = null,
SE.Expiration = @Expiration
FROM
[ReportServerTempDB].dbo.SessionData AS SE
WHERE
SE.SessionID = @SessionID AND
SE.OwnerID = @OwnerID

-----------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[DereferenceSessionSnapshot]
@SessionID as varchar(32),
@OwnerID as uniqueidentifier
AS

UPDATE SN
SET TransientRefcount = TransientRefcount - 1
FROM
SnapshotData AS SN
INNER JOIN [ReportServerTempDB].dbo.SessionData AS SE ON SN.SnapshotDataID = SE.SnapshotDataID
WHERE
SE.SessionID = @SessionID AND
SE.OwnerID = @OwnerID

UPDATE SN
SET TransientRefcount = TransientRefcount - 1
FROM
[ReportServerTempDB].dbo.SnapshotData AS SN
INNER JOIN [ReportServerTempDB].dbo.SessionData AS SE ON SN.SnapshotDataID = SE.SnapshotDataID
WHERE
SE.SessionID = @SessionID AND
SE.OwnerID = @OwnerID

-----------------------------------------------------------------------------------------------

CREATE TABLE [dbo].[SessionData](
[SessionID] [varchar](32) NOT NULL,
[CompiledDefinition] [uniqueidentifier] NULL,
[SnapshotDataID] [uniqueidentifier] NULL,
[IsPermanentSnapshot] [bit] NULL,
[ReportPath] [nvarchar](464) NULL,
[Timeout] [int] NOT NULL,
[AutoRefreshSeconds] [int] NULL,
[Expiration] [datetime] NOT NULL,
[ShowHideInfo] [image] NULL,
[DataSourceInfo] [image] NULL,
[OwnerID] [uniqueidentifier] NOT NULL,
[EffectiveParams] [ntext] NULL,
[CreationTime] [datetime] NOT NULL,
[HasInteractivity] [bit] NULL,
[SnapshotExpirationDate] [datetime] NULL,
[HistoryDate] [datetime] NULL,
[PageHeight] [float] NULL,
[PageWidth] [float] NULL,
[TopMargin] [float] NULL,
[BottomMargin] [float] NULL,
[LeftMargin] [float] NULL,
[RightMargin] [float] NULL,
[AwaitingFirstExecution] [bit] NULL,
[EditSessionID] [varchar](32) NULL,
[DataSetInfo] [varbinary](max) NULL,
[SitePath] [nvarchar](440) NULL,
[SiteZone] [int] NOT NULL,
[ReportDefinitionPath] [nvarchar](464) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO


CREATE UNIQUE CLUSTERED INDEX [IDX_SessionData] ON [dbo].[SessionData]
(
[SessionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [IX_EditSessionID] ON [dbo].[SessionData]
(
[EditSessionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [IX_SessionCleanup] ON [dbo].[SessionData]
(
[Expiration] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [IX_SessionSnapshotID] ON [dbo].[SessionData]
(
[SnapshotDataID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SessionData] ADD DEFAULT ((0)) FOR [SiteZone]
GO

--------------------------------------------------------------------------

CREATE TABLE [dbo].[SnapshotData](
[SnapshotDataID] [uniqueidentifier] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[ParamsHash] [int] NULL,
[QueryParams] [ntext] NULL,
[EffectiveParams] [ntext] NULL,
[Description] [nvarchar](512) NULL,
[DependsOnUser] [bit] NULL,
[PermanentRefcount] [int] NOT NULL,
[TransientRefcount] [int] NOT NULL,
[ExpirationDate] [datetime] NOT NULL,
[PageCount] [int] NULL,
[HasDocMap] [bit] NULL,
[Machine] [nvarchar](512) NOT NULL,
[PaginationMode] [smallint] NULL,
[ProcessingFlags] [int] NULL,
[IsCached] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO


CREATE CLUSTERED INDEX [IX_SnapshotData] ON [dbo].[SnapshotData]
(
[SnapshotDataID] ASC,
[ParamsHash] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [IS_SnapshotExpiration] ON [dbo].[SnapshotData]
(
[PermanentRefcount] ASC,
[ExpirationDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [IX_SnapshotCleaning] ON [dbo].[SnapshotData]
(
[PermanentRefcount] ASC,
[TransientRefcount] ASC
)
INCLUDE ( [Machine]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SnapshotData] ADD DEFAULT ((0)) FOR [IsCached]
GO

<keylock hobtid="72057594040745984" dbid="6" objectname="ReportServerTempDB.dbo.SessionData" indexname="IDX_SessionData" id="lock2baca83f00" mode="U" associatedObjectId="72057594040745984">
<owner-list>
<owner id="process1e08743848" mode="S" />
</owner-list>
<waiter-list>
<waiter id="process22a01908c8" mode="X" requestType="convert" />
</waiter-list>
</keylock>