gpt4 book ai didi

sql-server - SQL Server 意外死锁

转载 作者:行者123 更新时间:2023-12-03 05:36:09 26 4
gpt4 key购买 nike

我猜大多数死锁都是意外的,但即使查看死锁图,我仍然发现死锁是意外的。

  • Azure SQL Server。
  • 表格非常简单。主键(Id)上的聚集索引,没有其他索引。
  • 所有更新均位于由主键标识的单行上。
  • 多个服务器可能同时更新表(但通常只有两个)。

死锁图: enter image description here

这个在页面锁定上发生死锁。我后来添加了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

编辑的实际查询计划:

enter image description here

另一个死锁图,这是添加行锁提示后的:

enter image description here

第三张图: enter image description here

第三张图的 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 precedencevarchar 更重要,因此 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/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com