我想检测并记录我的 PostgreSQL 服务器中发生的所有死锁,最好包括查询的详细信息。这在 PostgreSQL 中可能吗?
我想要一个像 MS SQL 中提供的扩展事件这样的解决方案。
我想检测并记录我的 PostgreSQL 服务器中发生的所有死锁,最好包括查询的详细信息。这在 PostgreSQL 中可能吗?
我想要一个像 MS SQL 中提供的扩展事件这样的解决方案。
在调试死锁时,我注意到在 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>
我们在 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 ;
我们正在为我们的一位客户运行 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
知道什么会导致这些奇怪的死锁以及如何绕过它们吗?
我有一个 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
知道为什么删除两条不同的记录会触发这种死锁吗?
谢谢
从 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()) >= (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()) >= (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()) >= (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()) >= (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()) >= (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()) >= (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()) >= (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()) >= (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()) >= (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()) >= (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()) >= (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()) >= (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()) >= (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()) >= (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()) >= (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()) >= (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()) >= (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()) >= (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>
如果有人能给我一些见解,我会松一口气的。
谢谢
我有以下情况,我有一个带有主键的表,以及一个我不能有具有特定要求的行的约束。出于演示目的,这里我有不允许在 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
我对生产有以下两个疑问:
查询 #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% 的情况下成为受害者?
更新:在下面添加图表
不久前,我花了数周的噩梦来处理“检测到死锁”并试图弄清楚如何处理它。我最终以这样一种方式处理它,即我的代码能够检测到它何时发生,然后在每次重试之间以 50000 微秒无限期地重试相同的查询,直到它起作用。
也许这是不好的做法,但到目前为止(几个月),除了记录“检测到的死锁”所谓的“错误”之外,它没有引起任何问题。
我现在可以通过将“检测到死锁”错误标记为“不重要”来抑制“检测到死锁”错误,因此即使它们仍然登录到我的错误日志表中也不会向我显示?
请不要告诉我“首先避免它们”。这根本不可能。如果您在同一个表/事物上进行并发(多个进程/脚本实例),它们显然会发生。我一直在尝试“将它们编码”,但这似乎是不可能的。
显然,由于我问的是这个而不是仅仅添加忽略规则并完成它,我确实关心答案/响应。尽管如此,我认为目前我不能确信它们可以完全避免。我并不是说我每小时记录数千个或其他任何内容,而是每天记录一些,似乎总是在开始时我确实有很多并发进程在同一个表/查询上工作。