关于【deadlock】的问题- 第1页

Aaron Giambattista

Asked: 2024-09-11 03:23:04 +0800 CST

sp_blitzlock 在 SQL 托管实例中返回空白数据

7

我在 Azure 中有一个 SQL 托管实例，其中发生了一些阻塞/死锁。此数据库位于本地，并且安装了所有脚本，因此我卸载了它们，然后安装了 Azure 特定脚本 (Install-Azure.sql)，这些脚本是从 Brent Ozar 的网站最新下载的。

除了 sp_blitzlock 之外，所有脚本似乎都运行正常。SpBlitz 确实确认了“平均每天有 94 个死锁。要找到它们，请运行 sp_BlitzLock。”

我以系统管理员身份运行它，两个主表都是空白的。（当我以我的 EntraID 帐户运行它时，我遇到了权限问题：消息 50000，级别 11，状态 1，过程 sp_blitzlock，行 335 [批处理启动行 0] 名为 system_health 的会话不存在或当前不活动。

当我以实例 sysadmin 身份运行它时，我没有收到任何错误。数据只是空白。可能没有数据，但考虑到我们在其他地方观察到的情况以及 sp_blitz 确认的情况，我对此表示怀疑。

我下载了最新的 First Responder Kit，并使用 sp_BlitzLock 版本 8.21 和 sp_Blitz 版本 Jul 1 2024 12:00AM。这是 Azure 中的 SQL 托管实例，实例版本为 12.0.2000.8。我只是在 SSMS 中执行 sp_blitzlock; 来运行它。

值得一提的是，我们最近将服务层从“通用”升级到了“业务关键”。这总体上提高了性能和锁定，但我想知道它是否在后台触发了一个新实例，这就是为什么没有出现死锁数据的原因。（但是为什么它会出现在 sp_blitz 中？）

有什么想法吗？

igelr

Asked: 2022-09-06 05:44:31 +0800 CST

如何在 PostgreSQL 中检测和记录死锁

0

我想检测并记录我的 PostgreSQL 服务器中发生的所有死锁，最好包括查询的详细信息。这在 PostgreSQL 中可能吗？

我想要一个像 MS SQL 中提供的扩展事件这样的解决方案。

wmaciel

Asked: 2022-07-30 11:05:47 +0800 CST

为什么 SQL Server 查询会两次请求同一个锁并出现死锁？

2

我有 SQL Server 数据库，当两个查询尝试获取它们已经拥有的同一个表的锁时，它会一直死锁。上面的死锁图让我很困惑。两个PageLock是相同的，这让我相信它们是同一个页面。这似乎表明两个进程都已经拥有对该页面的锁定。左边的那个拥有一个更新（U）锁，而右边的那个拥有一个意图排他（IX）锁。

这怎么可能呢？

为什么他们在获取他们已经拥有的锁时会被阻止？

左边进程的查询是：

右侧进程查询为：

George Menoutis

Asked: 2022-03-15 03:10:06 +0800 CST

死锁xml上的重复资源

4

在调试死锁时，我注意到在 xml 中resource-list，第一个和第三个对象锁是相同的。这有什么原因吗？

<resource-list>逐字逐句：

 <resource-list>
  <objectlock lockPartition="0" objid="580509447" subresource="FULL" dbid="7" objectname="censoredV2.dbo.pt_sales" id="lock1929b2d5500" mode="Sch-M" associatedObjectId="580509447">
   <owner-list>
    <owner id="process194df0e5468" mode="Sch-M" />
   </owner-list>
   <waiter-list>
    <waiter id="process1947a066ca8" mode="Sch-M" requestType="wait" />
   </waiter-list>
  </objectlock>
  <objectlock lockPartition="0" objid="94623380" subresource="FULL" dbid="7" objectname="censoredV2.dbo.pt" id="lock1929a8cf480" mode="IX" associatedObjectId="94623380">
   <owner-list>
    <owner id="process1907da684e8" mode="IX" />
   </owner-list>
   <waiter-list>
    <waiter id="process194df0e5468" mode="Sch-M" requestType="wait" />
   </waiter-list>
  </objectlock>
  <objectlock lockPartition="0" objid="580509447" subresource="FULL" dbid="7" objectname="censoredV2.dbo.pt_sales" id="lock1929b2d5500" mode="Sch-M" associatedObjectId="580509447">
   <owner-list>
    <owner id="process1947a066ca8" mode="Sch-M" requestType="wait" />
   </owner-list>
   <waiter-list>
    <waiter id="process1907da684e8" mode="IX" requestType="wait" />
   </waiter-list>
  </objectlock>
 </resource-list>

Edi Mari

Asked: 2021-12-02 14:42:28 +0800 CST

SQL Server 无法解释的死锁事件

1

我们在 AWS 上使用 Microsoft SQL Server 2017 标准版（64 位），并且遇到无法解释的死锁事件。

我们正在尝试从不同的进程更新不同的唯一行，表和页面升级被禁用，sqlserver 使用键锁，我们仍然遇到死锁。

profiler 中的数据显示每个进程在不同的行上工作，所以我们不明白为什么会发生这种死锁。

** 我们认为我们设法在测试表中重现了这个死锁，但我们仍然没有对此死锁的解释（表和页面升级被禁用）。

CREATE TABLE TestTable 
( 
ID CHAR(8) NOT NULL PRIMARY KEY,
Val CHAR(1) 
) 

--in session 1 
BEGIN TRANSACTION 
  DECLARE @INX bigint ;
  DECLARE @INX2 nchar(8);

  SET @INX = NEXT VALUE FOR SequenceExample ; 
  

  set @INX2 = @INX;

  Print (@INX2)

  WAITFOR DELAY '00:00:05'
 
  INSERT INTO [dbo].[TestTable]
           ([ID]
           ,[Val])
     VALUES
           (@INX2,'Y');

  WAITFOR DELAY '00:00:05' 

  UPDATE [dbo].[TestTable] 
  SET Val='X' 
  WHERE ID= @INX2
  
  WAITFOR DELAY '00:00:05' 
  
  COMMIT ;

--in session 2 
BEGIN TRANSACTION 
  DECLARE @INX bigint ;
  DECLARE @INX2 nchar(8);

  SET @INX = NEXT VALUE FOR SequenceExample ; 
  

  set @INX2 = @INX;

  Print (@INX2)

  WAITFOR DELAY '00:00:05'
 
  INSERT INTO [dbo].[TestTable]
           ([ID]
           ,[Val])
     VALUES
           (@INX2,'Y');

  WAITFOR DELAY '00:00:05' 

  UPDATE [dbo].[TestTable] 
  SET Val='X' 
  WHERE ID= @INX2
  
  WAITFOR DELAY '00:00:05' 
  
  COMMIT ;

Martin Karouš

Asked: 2021-10-07 06:06:08 +0800 CST

表变量上的自死锁进程

7

我们正在为我们的一位客户运行 SQL Server 2019 CU12。前段时间我们开始遇到有线死锁，即单个进程在访问表变量时会自行死锁。

死锁报告示例

    <deadlock>
 <victim-list>
  <victimProcess id="process2ae9f9f7468" />
 </victim-list>
 <process-list>
  <process id="process2ae9f9f7468" taskpriority="0" logused="0" waitresource="OBJECT: 2:-1194094756:0 " waittime="110" ownerId="6978622122" transactionname="GetInitializedIMA" lasttranstarted="2021-09-15T21:46:44.243" XDES="0x2b4d9477be8" lockMode="Sch-S" schedulerid="3" kpid="10868" status="suspended" spid="102" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2021-09-15T21:46:44.113" lastbatchcompleted="2021-09-15T21:46:44.113" lastattention="2021-09-15T21:46:15.777" clientapp=".Net SqlClient Data Provider" hostname="removed" hostpid="15900" loginname="removed" isolationlevel="read committed (2)" xactid="6978622078" currentdb="15" currentdbname="MigrationSubjects" lockTimeout="4294967295" clientoption1="673187936" clientoption2="128056">
   <executionStack>
    <frame procname="unknown" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">unknown</frame>
   </executionStack>
   <inputbuf>
     (@Ids [SubjectRegistry.Consolidation.IdTable] READONLY)
     DELETE [reg].[HistoricalCompanyInfo] FROM [reg].[HistoricalCompanyInfo] t
     INNER JOIN @Ids ids ON ids.Id = t.HistoricalCompanyInfoId
   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <objectlock lockPartition="0" objid="-1194094756" subresource="FULL" dbid="2" objectname="tempdb.dbo.#B8D38F5C" id="lock2ac0942fa00" mode="Sch-M" associatedObjectId="-1194094756">
   <owner-list>
    <owner id="process2ae9f9f7468" mode="Sch-M" />
    <owner id="process2ae9f9f7468" mode="Sch-S" requestType="wait" />
   </owner-list>
   <waiter-list>
    <waiter id="process2ae9f9f7468" mode="Sch-S" requestType="wait" />
   </waiter-list>
  </objectlock>
 </resource-list>
</deadlock>

表类型定义如下：

CREATE TYPE [dbo].[SubjectRegistry.Consolidation.IdTable] AS TABLE(
    [Id] [bigint] NOT NULL, PRIMARY KEY CLUSTERED ([Id] ASC) WITH (IGNORE_DUP_KEY = OFF)
)
GO

知道什么会导致这些奇怪的死锁以及如何绕过它们吗？

Thomas K

Asked: 2021-09-18 03:05:20 +0800 CST

SQL Server - 两个并发 DELETE 期间的死锁

1

我有一个 Java 应用程序公开 REST API 以删除表中的记录（SQL Server），有时 API 必须处理并发删除请求（每个请求使用不同的数据库会话）

这是数据库模式的摘录：

MailingInstruction
------------------
id (PK)
[...]
addressId (FK to Address table)

Address
------------------
id (PK)
street
street_nr
[...]

MailingInstruction 记录的删除会触发关联地址的级联删除。每个 MailingInstruction 都有一个专用地址（不可重复使用）

但有时，其中一个并发删除会因为死锁而失败：

事务（进程 ID 51）与另一个进程在锁资源上死锁，并被选为死锁牺牲品。重新运行事务。

所以我启用了一些日志：

DBCC TRACEON(1222, 3605,-1)

似乎问题是地址记录上的删除，这很奇怪，因为当我通过主键删除时，我认为锁只会在特定记录上。

这是死锁跟踪：

2021-09-17 09:53:53.39 spid15s 死锁列表 2021-09-17 09:53:53.39 spid15s 死锁受害者=process80ba33848 2021-09-17 09:53:53.40 spid15s 进程列表 2021-09-17 09: 53:53.40 spid15s 进程 id=process80ba33848 taskpriority=0 logused=348 waitresource=KEY: 5:72057594043564032 (e82f06e0affb) waittime=1424 ownerId=30578 transactionname=implicit_transaction lasttranstarted=> 2021-09-0947T09:x=1151X151。 S schedulerid=4 kpid=684 status=suspended spid=51 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=> 2021-09-17T09:53:51.970 lastbatchcompleted=> 2021-09-17T09:53:51.967 lastattention =1900-01-01T00:00:00。967 clientapp=Microsoft JDBC Driver for SQL Server 主机名=MY-DESKTOP hostpid=0 loginname=sa 隔离级别=读取已提交 (2) xactid=30578 currentdb=5 currentdbname=MY_DB lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058 2021-09-17 09:53:53.44 spid15s executionStack 2021-09-17 09:53:53.45 spid15s frame procname=adhoc line=1 stmtstart=24 stmtend=100 sqlhandle=0x02000000627bbe0c9854b43c4ee3d7183c254e990331f4e80000000000000000000000000000000000000000 2021-09-17 09:53:53.46 spid15s unknown 2021-09- 17 09:53:53.46 spid15s frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 2021-09-17 09:53:53.47 spid15s unknown 2021-09-17 09:53:53.47 spid15s inputbuf 2021-09-17 09:53: 53.47 spid15s (@P0 bigint) 从地址中删除 addressId=@P0 2021-09-17 09:53:53.48 spid15s 进程 id=process80ba1f088 taskpriority=0 logused=352 waitresource=KEY: 5:72057594043564032 (036d9ed30da4) waittime=1424 ownerId =30579 transactionname=implicit_transaction lasttranstarted=> 2021-09-17T09:53:51.967 XDES=0x1137680420 lockMode=S schedulerid=2 kpid=604 status=suspended spid=53 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=> 2021-09-17T09:53:51.970 lastbatchcompleted=> 2021-09-17T09:53:51.967 lastattention=1900-01-01T00:00:00.967 clientapp=Microsoft JDBC Driver for SQL Server hostname=MY-DESKTOP hostpid=0 loginname= sa 隔离级别=已提交读取 (2) xactid=30579 currentdb=5 currentdbname=MY_DB lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058 2021-09-17 09:53:53。51 spid15s executionStack 2021-09-17 09:53:53.52 spid15s frame procname=adhoc line=1 stmtstart=24 stmtend=100 sqlhandle=0x02000000627bbe0c9854b43c4ee3d7183c254e990331f4e80000000000000000000000000000000000000000 2021-09-17 09:53:53.53 spid15s unknown 2021-09-17 09:53 :53.53 spid15s frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 2021-09-17 09:53:53.54 spid15s unknown 2021-09-17 09:53:53.54 spid15s inputbuf 2021-09-17 09:53:53.54 spid15s (@ P0 bigint) 从 addressId=@P0 2021-09-17 09:53:53.55 spid15s resource-list 2021-09-17 09:53:53.55 spid15s keylock hobtid=72057594043564032 dbid=5 objectname=MY_DB.dbo 的地址中删除。MailingInstruction indexname=PK__MailingI__F3FD20CC83FED272 id=lock1146070b80 mode=X associatedObjectId=72057594043564032 2021-09-17 09:53:53.56 spid15s owner-list 2021-09-17 09:53:53.56 spid15s owner id=process80ba1f088 mode=X 2021-09-17 09:53:53.57 spid15s 服务员列表 2021-09-17 09:53:53.57 spid15s 服务员 id=process80ba33848 mode=S requestType=wait 2021-09-17 09:53:53.57 spid15s keylock hobtid=72057594043564032 MY_DB.dbo.MailingInstruction indexname=PK__MailingI__F3FD20CC83FED272 id=lock11393baa00 mode=X associatedObjectId=72057594043564032 2021-09-17 09:53:53.58 spid15s owner-list 2021-09-17 09:53:53.59 spid15s owner id=process80ba33848 mode=X 2021 -09-17 09:53:53.59 spid15s 服务员列表 2021-09-17 09:53:53。59 spid15s 服务员 id=process80ba1f088 mode=S requestType=wait

知道为什么删除两条不同的记录会触发这种死锁吗？

谢谢

ASh

Asked: 2021-08-15 02:12:45 +0800 CST

报表服务器数据库死锁

3

从 ReportServer.dbo.ExpireExecutionLogEntries 存储过程中获取死锁数。我花了一整天的时间看有关死锁的教程，但无法弄清楚这一点。

<deadlock>
 <victim-list>
  <victimProcess id="Victim" />
 </victim-list>
 <process-list>
  <process id="Victim" taskpriority="0" logused="10000" waittime="101" schedulerid="1" kpid="596" status="suspended" spid="115" sbid="0" ecid="16" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1ee21307c28" taskpriority="0" logused="10000" waittime="419597" schedulerid="5" kpid="4200" status="suspended" spid="115" sbid="0" ecid="6" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1ef541ea108" taskpriority="0" logused="10000" waittime="419500" schedulerid="8" kpid="6792" status="suspended" spid="115" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1f225504ca8" taskpriority="0" logused="10000" waittime="419913" schedulerid="2" kpid="13276" status="suspended" spid="115" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1f8d4029088" taskpriority="0" logused="10000" waittime="419598" schedulerid="3" kpid="14192" status="suspended" spid="115" sbid="0" ecid="5" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1f8d5adc4e8" taskpriority="0" logused="10000" waittime="419598" schedulerid="6" kpid="26268" status="suspended" spid="115" sbid="0" ecid="4" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1e26338d848" taskpriority="0" logused="10000" waittime="419598" schedulerid="4" kpid="21616" status="suspended" spid="115" sbid="0" ecid="3" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1ee384044e8" taskpriority="0" logused="10000" waittime="419597" schedulerid="7" kpid="9460" status="suspended" spid="115" sbid="0" ecid="7" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1e0f41ed088" taskpriority="0" logused="10000" waittime="419598" schedulerid="13" kpid="16024" status="suspended" spid="115" sbid="0" ecid="10" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1ed54019c28" taskpriority="0" logused="10000" waittime="419596" schedulerid="14" kpid="25028" status="suspended" spid="115" sbid="0" ecid="9" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1e5f96828c8" taskpriority="0" logused="10000" waittime="419913" schedulerid="9" kpid="17316" status="suspended" spid="115" sbid="0" ecid="15" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1e4e925aca8" taskpriority="0" logused="10000" waittime="419598" schedulerid="15" kpid="24752" status="suspended" spid="115" sbid="0" ecid="8" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1f8d402c8c8" taskpriority="0" logused="10000" waittime="419913" schedulerid="12" kpid="10720" status="suspended" spid="115" sbid="0" ecid="14" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1e5eb190108" taskpriority="0" logused="10000" waittime="419596" schedulerid="11" kpid="17880" status="suspended" spid="115" sbid="0" ecid="11" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1f69dfae8c8" taskpriority="0" logused="10000" waittime="419913" schedulerid="16" kpid="14748" status="suspended" spid="115" sbid="0" ecid="13" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1ea5e987088" taskpriority="0" logused="0" waitresource="PAGE: 9:1:288319 " waittime="419598" ownerId="10654834267" transactionname="DELETE" lasttranstarted="2021-08-14T01:59:59.513" XDES="0x1f3d5876ca0" lockMode="U" schedulerid="10" kpid="17232" status="suspended" spid="115" sbid="0" ecid="12" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1e5eb1904e8" taskpriority="0" logused="0" waitresource="PAGE: 9:1:288319 " waittime="419669" ownerId="10654834619" transactionname="DELETE" lasttranstarted="2021-08-14T01:59:59.757" XDES="0x1f49a45b900" lockMode="U" schedulerid="11" kpid="18024" status="suspended" spid="88" sbid="0" ecid="12" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.757" lastbatchcompleted="2021-08-14T01:59:59.757" lastattention="1900-01-01T00:00:00.757" clientapp="Report Server" hostname="SERVER1" hostpid="24648" isolationlevel="read committed (2)" xactid="10654834619" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
  <process id="process1eb3737aca8" taskpriority="0" logused="10000" waittime="419914" schedulerid="9" kpid="25564" status="suspended" spid="115" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="SERVER2" hostpid="4180" loginname="MYPCORP\mypadmin" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="6" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1ee21307c28" />
   </owner-list>
   <waiter-list>
    <waiter id="Victim" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="7" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1ee384044e8" />
   </owner-list>
   <waiter-list>
    <waiter id="process1ee21307c28" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="2" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1e26338d848" />
   </owner-list>
   <waiter-list>
    <waiter id="process1ef541ea108" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="1" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1ef541ea108" />
   </owner-list>
   <waiter-list>
    <waiter id="process1f225504ca8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="5" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="Victim" />
   </owner-list>
   <waiter-list>
    <waiter id="process1f8d4029088" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="4" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1f8d4029088" />
   </owner-list>
   <waiter-list>
    <waiter id="process1f8d5adc4e8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="3" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1f8d5adc4e8" />
   </owner-list>
   <waiter-list>
    <waiter id="process1e26338d848" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="8" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1e0f41ed088" />
   </owner-list>
   <waiter-list>
    <waiter id="process1ee384044e8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="9" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1e4e925aca8" />
   </owner-list>
   <waiter-list>
    <waiter id="process1e0f41ed088" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="11" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1ea5e987088" />
   </owner-list>
   <waiter-list>
    <waiter id="process1ed54019c28" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="16" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1eb3737aca8" />
   </owner-list>
   <waiter-list>
    <waiter id="process1e5f96828c8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="10" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1ed54019c28" />
   </owner-list>
   <waiter-list>
    <waiter id="process1e4e925aca8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="15" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1e5f96828c8" />
   </owner-list>
   <waiter-list>
    <waiter id="process1f8d402c8c8" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="13" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1f69dfae8c8" />
   </owner-list>
   <waiter-list>
    <waiter id="process1e5eb190108" />
   </waiter-list>
  </exchangeEvent>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="14" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1f8d402c8c8" />
   </owner-list>
   <waiter-list>
    <waiter id="process1f69dfae8c8" />
   </waiter-list>
  </exchangeEvent>
  <pagelock fileid="1" pageid="288319" dbid="9" subresource="FULL" objectname="ReportServer.dbo.ExecutionLogStorage" id="lock1f4bb84dc80" mode="U" associatedObjectId="72057594050052096">
   <owner-list>
    <owner id="process1e5eb1904e8" mode="U" requestType="wait" />
   </owner-list>
   <waiter-list>
    <waiter id="process1ea5e987088" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="288319" dbid="9" subresource="FULL" objectname="ReportServer.dbo.ExecutionLogStorage" id="lock1f4bb84dc80" mode="U" associatedObjectId="72057594050052096">
   <owner-list>
    <owner id="process1e5eb190108" mode="U" />
   </owner-list>
   <waiter-list>
    <waiter id="process1e5eb1904e8" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
  <exchangeEvent id="Port1f8ec8c8f00" WaitType="e_waitPortOpen" waiterType="Coordinator" nodeId="2" tid="0" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
   <owner-list>
    <owner id="process1f225504ca8" />
   </owner-list>
   <waiter-list>
    <waiter id="process1eb3737aca8" />
   </waiter-list>
  </exchangeEvent>
 </resource-list>
</deadlock>

如果有人能给我一些见解，我会松一口气的。

谢谢

Irdis

Asked: 2021-05-20 11:11:08 +0800 CST

如何避免检查约束中的 IX 死锁

3

我有以下情况，我有一个带有主键的表，以及一个我不能有具有特定要求的行的约束。出于演示目的，这里我有不允许在 N 列中插入重复值的约束。在实际情况下，它使用其他表的外键和其他过滤器检查几列，所以我不能放置简单的唯一约束。所以这里是例子

create table dbo.T1 (
    Id int not null identity (1,1),
    N int not null
)

alter table dbo.T1
add primary key (Id);

go

create function [dbo].[fn_CheckN](@id int, @n int)
returns int
as 
begin
    if exists (select * from dbo.T1 t where t.n = @n and t.Id != @id)
        return 0

    return 1
end

go

alter table [dbo].T1 with nocheck add  constraint [CK_T1_Valid] check  (([dbo].[fn_CheckN]([Id],[N]) = 1))
go

alter table [dbo].T1 check constraint [CK_T1_Valid]
go

当我同时运行时

insert into dbo.T1 (N) 
values (@i)

我在主键上得到这个死锁 S -> X, X -> S。我有点明白为什么。死锁xml： https ://pastebin.com/hceR3sum

我第一次尝试解决这个问题是先抓住 S 锁

begin tran 
declare @lock int = (select top(1) 1 from dbo.T1 with (tablock, holdlock))

insert into dbo.T1 (N) 
values (@i)

commit

但它因死锁 S -> IX, IX -> S 而失败。有人可以解释发生了什么吗？死锁 xml：https ://pastebin.com/mLXJb59C 。

我用 X 锁锁定了整个表来修复它。可以吗？有更好的方法吗？

begin tran 
declare @lock int = (select top(1) 1 from dbo.T1 with (tablockx, holdlock))

insert into dbo.T1 (N) 
values (@i)

commit

如果我在 N 列上放置索引，我会遇到这个死锁https://pastebin.com/KJGmlDhH。真正的要求几乎相同，最简单的情况是有 4 列带有 accountIds 和 enabled 标志，当记录发生问题时，我必须检查 account id 在所有启用的记录中是否唯一。或者如果它被禁用，我没有什么要检查的。类似的东西。

我使用 C# 同时运行查询

class Program
{
    private const string connectionString = "Server=.;Database=Performance;Trusted_Connection=True;MultipleActiveResultSets=True; Max Pool Size=3000";

    static async Task Main(string[] args)
    {
        await ClearAsync();
        await Task.WhenAll(Enumerable.Range(0, 100000).Select(async i => await InsertAsync(i)).ToArray()); 
        Console.WriteLine("Done");
        Console.ReadKey();
    }

    public static async Task InsertAsync(int i)
    {
        using var connection = new SqlConnection(connectionString);
        using var cmd = new SqlCommand(@"
insert into dbo.T1 (N) 
values (@i)
", connection);
        await connection.OpenAsync();
        cmd.Parameters.Add("@i", SqlDbType.Int).Value = i;
        await cmd.ExecuteNonQueryAsync();
    }

    public static async Task ClearAsync()
    {
        using var connection = new SqlConnection(connectionString);
        using var cmd = new SqlCommand("delete from dbo.T1", connection);
        await connection.OpenAsync();
        await cmd.ExecuteNonQueryAsync();
    }
}

我使用 Microsoft SQL Server Express（64 位）13.0.1601.5

Aleksey Vitsko

Asked: 2021-03-25 07:37:52 +0800 CST

将死锁优先级设置为尽可能低的值，但我的脚本仍然不是死锁情况下的受害者

1

我对生产有以下两个疑问：

查询 #1：根据“日期”列从核心表“Table1”中删除旧历史数据的脚本

有约 20 个表通过外键通过级联规则指向“Table1”，因此当您从“Table1”中删除时，数据也会从约 20 个其他表中删除

脚本一次删除 50 行数据，批处理间隔为 1 秒
当批处理只有 50 行时，脚本不会导致排他锁升级为表级锁；它只放置行/页级别 (X) 锁

查询 #2：从“Table1”、“Table2”、“Table3”和其他一些表中提取数据的生产应用程序

这是一个重读查询，它在表上放置共享锁。它不做任何插入/更新/删除

问题：

有时，当Query # 2（Prod 应用程序）读取数据时会发生死锁，而我同时Query # 1在 SSMS 中运行（脚本删除旧数据）。Query # 2始终是受害者，据我了解，这是因为它没有对事务日志进行任何更改，而Query # 1确实

为了解决这个问题，我添加set deadlock_priority -10了Query # 1，希望它会使Query # 1任何可能的死锁成为受害者

首先它似乎有效，Query # 1成为死锁情况的受害者，在删除一些批次时抛出错误 1205。

但是-最近我发现有时Query # 2仍然会成为受害者

问题：

这怎么可能Query # 2有时甚至在我已经成为受害者set deadlock_priority -10之后Query # 1？

有没有办法解决它并确保Query # 1在 100% 的情况下成为受害者？

更新：在下面添加图表

sp_blitzlock 在 SQL 托管实例中返回空白数据

如何在 PostgreSQL 中检测和记录死锁

为什么 SQL Server 查询会两次请求同一个锁并出现死锁？

死锁xml上的重复资源

SQL Server 无法解释的死锁事件

表变量上的自死锁进程

SQL Server - 两个并发 DELETE 期间的死锁

报表服务器数据库死锁

如何避免检查约束中的 IX 死锁

将死锁优先级设置为尽可能低的值，但我的脚本仍然不是死锁情况下的受害者

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

问题[deadlock](dba)