- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我猜大多数死锁都是意外的,但即使查看死锁图,我仍然发现死锁是意外的。
这个在页面锁定上发生死锁。我后来添加了WITH(行锁)提示,但我仍然遇到死锁,只是在行而不是页面上。我使用这个示例是因为它是我发现的最简单的图表。
看起来查询是并行运行的(交换事件),并且不同的并行处理器彼此死锁,但我真的不希望这种情况发生在如此简单的事情上。
死锁图 XML:
<deadlock>
<victim-list>
<victimProcess id="process228fd04e108" />
</victim-list>
<process-list>
<process id="process228fd04e108" taskpriority="0" logused="0" waitresource="PAGE: 5:1:2288 " waittime="9103" ownerId="2582803" transactionname="UPDATE" lasttranstarted="2020-06-12T12:53:02.807" XDES="0x22776384df0" lockMode="U" schedulerid="2" kpid="45296" status="suspended" spid="123" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2020-06-12T12:53:02.807" lastbatchcompleted="2020-06-12T12:53:02.803" lastattention="1900-01-01T00:00:00.803" clientapp="Core Microsoft SqlClient Data Provider" hostname="RD501AC5B6ACC6" hostpid="16672" isolationlevel="read committed (2)" xactid="2582803" currentdb="5" currentdbname="fp-pinfo-prod-db" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="unknown" queryhash="0x16e67a1e6613ccaa" queryplanhash="0xd91db86605a474c5" line="1" stmtstart="150" stmtend="308" sqlhandle="0x02000000035f942bf8a99f27adc04a1730c088079e14a1a90000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p0 nvarchar(4000),@p1 datetime2(7),@p2 nvarchar(4000),@p3 nvarchar(4000))UPDATE KeyValue SET Value = @p0, TimestampUtc = @p1, Origin = @p2 WHERE Id = @p3 </inputbuf>
</process>
<process id="process228fd04fc28" taskpriority="0" logused="328" waitresource="PAGE: 5:1:2288 " waittime="6999" ownerId="2582770" transactionname="UPDATE" lasttranstarted="2020-06-12T12:53:02.573" XDES="0x22908ef4960" lockMode="U" schedulerid="2" kpid="54024" status="suspended" spid="122" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2020-06-12T12:53:02.573" lastbatchcompleted="2020-06-12T12:53:02.570" lastattention="1900-01-01T00:00:00.570" clientapp="Core Microsoft SqlClient Data Provider" hostname="RD501AC5B6ACC6" hostpid="14476" isolationlevel="read committed (2)" xactid="2582770" currentdb="5" currentdbname="fp-pinfo-prod-db" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="unknown" queryhash="0x16e67a1e6613ccaa" queryplanhash="0xd91db86605a474c5" line="1" stmtstart="150" stmtend="308" sqlhandle="0x02000000035f942bf8a99f27adc04a1730c088079e14a1a90000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p0 nvarchar(4000),@p1 datetime2(7),@p2 nvarchar(4000),@p3 nvarchar(4000))UPDATE KeyValue SET Value = @p0, TimestampUtc = @p1, Origin = @p2 WHERE Id = @p3 </inputbuf>
</process>
<process id="process228f451d088" taskpriority="0" logused="10000" waittime="4139" schedulerid="2" kpid="43452" status="suspended" spid="122" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-06-12T12:53:02.573" lastbatchcompleted="2020-06-12T12:53:02.570" lastattention="1900-01-01T00:00:00.570" clientapp="Core Microsoft SqlClient Data Provider" hostname="RD501AC5B6ACC6" hostpid="14476" loginname="superuser" isolationlevel="read committed (2)" xactid="2582770" currentdb="5" currentdbname="fp-pinfo-prod-db" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="unknown" queryhash="0x16e67a1e6613ccaa" queryplanhash="0xd91db86605a474c5" line="1" stmtstart="150" stmtend="308" sqlhandle="0x02000000035f942bf8a99f27adc04a1730c088079e14a1a90000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p0 nvarchar(4000),@p1 datetime2(7),@p2 nvarchar(4000),@p3 nvarchar(4000))UPDATE KeyValue SET Value = @p0, TimestampUtc = @p1, Origin = @p2 WHERE Id = @p3 </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="2288" dbid="5" subresource="FULL" objectname="9332623d-7c4f-4da3-b054-9be315e6f0a4.dbo.KeyValue" id="lock228c3bed800" mode="U" associatedObjectId="72057594043826176">
<owner-list>
<owner id="process228f451d088" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process228fd04e108" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="2288" dbid="5" subresource="FULL" objectname="9332623d-7c4f-4da3-b054-9be315e6f0a4.dbo.KeyValue" id="lock228c3bed800" mode="U" associatedObjectId="72057594043826176">
<owner-list>
<owner id="process228fd04e108" mode="U" requestType="wait" />
</owner-list>
<waiter-list>
<waiter id="process228fd04fc28" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<exchangeEvent id="Pipe2277614e600" WaitType="e_waitPipeGetRow" waiterType="Coordinator" nodeId="2" tid="0" ownerActivity="sentData" waiterActivity="needMoreData" merging="false" spilling="false" waitingToClose="false">
<owner-list>
<owner id="process228fd04fc28" />
</owner-list>
<waiter-list>
<waiter id="process228f451d088" />
</waiter-list>
</exchangeEvent>
</resource-list>
</deadlock>
编辑:
表的 DDL:
/****** Object: Table [dbo].[KeyValue] Script Date: 15-06-2020 21:41:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[KeyValue](
[Id] [varchar](900) NOT NULL,
[Value] [nvarchar](max) NULL,
[TimestampUtc] [datetime2](7) NOT NULL,
[Origin] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_KeyValue] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
编辑的实际查询计划:
另一个死锁图,这是添加行锁提示后的:
第三张图的 XML:
<deadlock>
<victim-list>
<victimProcess id="process28b6db54108" />
</victim-list>
<process-list>
<process id="process28b6db54108" taskpriority="0" logused="396" waitresource="KEY: 5:72057594043826176 (36d9edc841fc)" waittime="7137" ownerId="1491320" transactionname="UPDATE" lasttranstarted="2020-06-15T09:55:29.570" XDES="0x28b698c9280" lockMode="U" schedulerid="2" kpid="34896" status="suspended" spid="113" sbid="0" ecid="3" priority="0" trancount="0" lastbatchstarted="2020-06-15T09:55:29.570" lastbatchcompleted="2020-06-15T09:55:29.563" lastattention="1900-01-01T00:00:00.563" clientapp="Core Microsoft SqlClient Data Provider" hostname="RD501AC5B6ACC6" hostpid="26324" isolationlevel="read committed (2)" xactid="1491320" currentdb="5" currentdbname="fp-pinfo-prod-db" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="unknown" queryhash="0x01068720fbb54c87" queryplanhash="0xd91db86605a474c5" line="1" stmtstart="150" stmtend="338" sqlhandle="0x02000000916f1f032e9e459183cba2f8f5cf5fabfffb31b40000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p0 nvarchar(4000),@p1 datetime2(7),@p2 nvarchar(4000),@p3 nvarchar(4000))UPDATE KeyValue WITH (rowlock) SET Value = @p0, TimestampUtc = @p1, Origin = @p2 WHERE Id = @p3 </inputbuf>
</process>
<process id="process28b6db544e8" taskpriority="0" logused="796" waitresource="KEY: 5:72057594043826176 (f8db9e67957b)" waittime="14507" ownerId="1491488" transactionname="UPDATE" lasttranstarted="2020-06-15T09:55:31.380" XDES="0x28b698dedf0" lockMode="U" schedulerid="2" kpid="74440" status="suspended" spid="129" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2020-06-15T09:55:31.380" lastbatchcompleted="2020-06-15T09:55:31.377" lastattention="1900-01-01T00:00:00.377" clientapp="Core Microsoft SqlClient Data Provider" hostname="RD501AC552A9B5" hostpid="4176" isolationlevel="read committed (2)" xactid="1491488" currentdb="5" currentdbname="fp-pinfo-prod-db" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="unknown" queryhash="0x01068720fbb54c87" queryplanhash="0xd91db86605a474c5" line="1" stmtstart="150" stmtend="338" sqlhandle="0x02000000916f1f032e9e459183cba2f8f5cf5fabfffb31b40000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p0 nvarchar(4000),@p1 datetime2(7),@p2 nvarchar(4000),@p3 nvarchar(4000))UPDATE KeyValue WITH (rowlock) SET Value = @p0, TimestampUtc = @p1, Origin = @p2 WHERE Id = @p3 </inputbuf>
</process>
<process id="process28c66eff848" taskpriority="0" logused="796" waitresource="KEY: 5:72057594043826176 (5f732a583b1e)" waittime="7820" ownerId="1491488" transactionname="UPDATE" lasttranstarted="2020-06-15T09:55:31.380" XDES="0x28b69a2fba0" lockMode="U" schedulerid="1" kpid="67308" status="suspended" spid="129" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2020-06-15T09:55:31.380" lastbatchcompleted="2020-06-15T09:55:31.377" lastattention="1900-01-01T00:00:00.377" clientapp="Core Microsoft SqlClient Data Provider" hostname="RD501AC552A9B5" hostpid="4176" isolationlevel="read committed (2)" xactid="1491488" currentdb="5" currentdbname="fp-pinfo-prod-db" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="unknown" queryhash="0x01068720fbb54c87" queryplanhash="0xd91db86605a474c5" line="1" stmtstart="150" stmtend="338" sqlhandle="0x02000000916f1f032e9e459183cba2f8f5cf5fabfffb31b40000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p0 nvarchar(4000),@p1 datetime2(7),@p2 nvarchar(4000),@p3 nvarchar(4000))UPDATE KeyValue WITH (rowlock) SET Value = @p0, TimestampUtc = @p1, Origin = @p2 WHERE Id = @p3 </inputbuf>
</process>
<process id="process28cf7edd468" taskpriority="0" logused="10000" waittime="4232" schedulerid="1" kpid="50744" status="suspended" spid="129" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-06-15T09:55:31.380" lastbatchcompleted="2020-06-15T09:55:31.377" lastattention="1900-01-01T00:00:00.377" clientapp="Core Microsoft SqlClient Data Provider" hostname="RD501AC552A9B5" hostpid="4176" loginname="superuser" isolationlevel="read committed (2)" xactid="1491488" currentdb="5" currentdbname="fp-pinfo-prod-db" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="unknown" queryhash="0x01068720fbb54c87" queryplanhash="0xd91db86605a474c5" line="1" stmtstart="150" stmtend="338" sqlhandle="0x02000000916f1f032e9e459183cba2f8f5cf5fabfffb31b40000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p0 nvarchar(4000),@p1 datetime2(7),@p2 nvarchar(4000),@p3 nvarchar(4000))UPDATE KeyValue WITH (rowlock) SET Value = @p0, TimestampUtc = @p1, Origin = @p2 WHERE Id = @p3 </inputbuf>
</process>
<process id="process28cf7edc8c8" taskpriority="0" logused="10000" waittime="4864" schedulerid="1" kpid="59208" status="suspended" spid="113" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-06-15T09:55:29.570" lastbatchcompleted="2020-06-15T09:55:29.563" lastattention="1900-01-01T00:00:00.563" clientapp="Core Microsoft SqlClient Data Provider" hostname="RD501AC5B6ACC6" hostpid="26324" loginname="superuser" isolationlevel="read committed (2)" xactid="1491320" currentdb="5" currentdbname="fp-pinfo-prod-db" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="unknown" queryhash="0x01068720fbb54c87" queryplanhash="0xd91db86605a474c5" line="1" stmtstart="150" stmtend="338" sqlhandle="0x02000000916f1f032e9e459183cba2f8f5cf5fabfffb31b40000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" queryhash="0x0000000000000000" queryplanhash="0x0000000000000000" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p0 nvarchar(4000),@p1 datetime2(7),@p2 nvarchar(4000),@p3 nvarchar(4000))UPDATE KeyValue WITH (rowlock) SET Value = @p0, TimestampUtc = @p1, Origin = @p2 WHERE Id = @p3 </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594043826176" dbid="5" objectname="050344b0-8326-468d-9337-48fedc86da64.dbo.KeyValue" indexname="PK_KeyValue" id="lock28c65e13800" mode="U" associatedObjectId="72057594043826176">
<owner-list>
<owner id="process28cf7edd468" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process28b6db54108" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594043826176" dbid="5" objectname="050344b0-8326-468d-9337-48fedc86da64.dbo.KeyValue" indexname="PK_KeyValue" id="lock28c68183800" mode="X" associatedObjectId="72057594043826176">
<owner-list>
<owner id="process28cf7edc8c8" mode="X" />
<owner id="process28cf7edc8c8" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process28b6db544e8" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594043826176" dbid="5" objectname="050344b0-8326-468d-9337-48fedc86da64.dbo.KeyValue" indexname="PK_KeyValue" id="lock28c683a5700" mode="U" associatedObjectId="72057594043826176">
<owner-list>
<owner id="process28cf7edc8c8" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process28c66eff848" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<exchangeEvent id="Pipe28b69a26680" WaitType="e_waitPipeGetRow" waiterType="Coordinator" nodeId="2" tid="0" ownerActivity="sentData" waiterActivity="needMoreData" merging="false" spilling="false" waitingToClose="false">
<owner-list>
<owner id="process28b6db544e8" />
<owner id="process28c66eff848" />
</owner-list>
<waiter-list>
<waiter id="process28cf7edd468" />
</waiter-list>
</exchangeEvent>
<exchangeEvent id="Pipe28b699e0680" WaitType="e_waitPipeGetRow" waiterType="Coordinator" nodeId="2" tid="0" ownerActivity="sentData" waiterActivity="needMoreData" merging="false" spilling="false" waitingToClose="false">
<owner-list>
<owner id="process28b6db54108" />
</owner-list>
<waiter-list>
<waiter id="process28cf7edc8c8" />
</waiter-list>
</exchangeEvent>
</resource-list>
</deadlock>
最佳答案
找到了。简单的错误。您的 PK 是 varchar(900)
。您的 UPDATE 发送 nvarchar(4000)
类型的参数。 nvarchar
具有更高的 data type precedence比 varchar
更重要,因此 varchar
值必须转换为 nvarchar
进行比较。因此,原本应该是微不足道的聚集索引查找变成了扫描。扫描的额外成本导致了并行查询计划以及死锁的可能性。
要解决此问题,只需在 .NET 中使用正确的参数类型或在查询中强制进行转换即可。 EG
UPDATE KeyValue SET Value = @p0, TimestampUtc = @p1, Origin = @p2
WHERE Id = cast(@p3 as varchar(900))
这将消除并行计划、死锁,并使查询成本大大降低。
关于sql-server - SQL Server 意外死锁,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/62395079/
关闭。这个问题不符合Stack Overflow guidelines .它目前不接受答案。 我们不允许在 Stack Overflow 上提出有关通用计算硬件和软件的问题。您可以编辑问题,使其成为
当我尝试在 db2 中创建表时,它抛出以下错误 $ db2 CREATE TABLE employee(emp_id INT NOT NULL, emp_name VARCHAR(100)) sh:
我有: while (i < l) { if (one === two) { continue; } i++; } 但是 JSLint 说: Problem at line 1 chara
所以我有这个代码: char inputs[10] = ""; int numInputs = 0; while (numInputs < 10){ char c; printf("E
var ninja = { name: 'Ninja', say: function () { return 'I am a ' + this.name; }
我收到一个我不明白的错误,请注意,我是编码新手,所以这可能是一个简单的错误。 #include using namespace std; int main() { //Initialise Fahr
我正在使用 javascript 和 react,由于某种原因,我收到了一个奇怪的 token 错误。 这是发生错误的代码: renderNavBar() { if (!this.us
Closed. This question is off-topic。它当前不接受答案。
由于某种我无法解释的原因,编译器正在输出一个错误,指出它发现了一个意外的#else 标记。 这发生在文件的开头: #if defined( _USING_MFC ) #include "stda
这个问题不太可能帮助任何 future 的访问者;它只与一个小的地理区域、一个特定的时间点或一个非常狭窄的情况有关,这些情况并不普遍适用于互联网的全局受众。为了帮助使这个问题更广泛地适用,visit
这个问题在这里已经有了答案: Difference between sh and Bash (11 个答案) 关闭 2 年前。 我正在编写一个简单的 bash 脚本,我在 XX `(' unexpe
关闭。这个问题是not reproducible or was caused by typos .它目前不接受答案。 此问题是由拼写错误或无法再重现的问题引起的。虽然类似的问题可能是 on-topic
我在 Windows 7 上编写了一个脚本,它不断给我一个错误“(此时出乎意料。”对于以下代码 if %vardns%=="NODNS" ( netsh interface ipv4 set ad
我正在尝试使用xmlstarlet(使用xpath)解析XML文件,但是出现语法错误,并且我不知道如何更正我的代码。 这是我的脚本: #!/bin/bash if [ $1=="author" ];
以下脚本旨在在目录中的所有文件上运行程序“senna”,并将每个文件的输出(保留输入文件名)写入另一个目录 for file in ./Data/in/*; do ./senna -iobta
我从 challengers.coffee 运行此代码,并收到错误 ActionView::Template::Error (SyntaxError: [stdin]:3:31:unexpected
我在 config.db.database; 行中有语法错误(意外的标记“.”)。这是我在文件中的代码 const config = require('../config/config') const
这一定很明显,但是我无法使它正常工作。我正在尝试传输应该用于构建$ classKey的对象,这反过来又导致删除所需的软件(amd64或i386)。好吧,这里的代码: $name = @("softwa
我正在使用 1.3.7 版学习 Grails,但我一直无缘无故地遇到以下语法错误: unexpected token: mapping @ line x, column y. 有一次,我通过运行“gr
我正在尝试找出这段Pascal代码的问题 function Factorial(n: integer): integer; begin if n = 0 then Result := 1
我是一名优秀的程序员,十分优秀!