主页 / coding / 问题 / 78843729
Accepted
Merlin Nestler
Asked: 2024-08-07 20:34:21 +0800 CST

避免死锁 - 如何锁定行级

  • 772

我遇到了一个无法通过实验复制的死锁。我相信从我的实验中我找到了解决方法，但我想了解其基本原理

这是 SQL Server 提供的锁图。从对象 ID 可以推断，两个进程都在尝试访问同一张表。 在此处输入图片描述

该表如下所示。（我使用全局临时表进行测试）

CREATE TABLE ##tMyTable(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Fk_1_Id] [int] NOT NULL,
    [Fk_2_Id] [int] NULL,
    [Fk_3_Id] [int] NULL,
    [Day] [date] NOT NULL,
    [Quantity] [decimal](19, 3) NOT NULL,
 CONSTRAINT [Pk_MyTable_Id] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

+ foreign key stuff.

带有前缀 Fk_ 的列是外键。

该表上还有一个索引：

CREATE NONCLUSTERED INDEX [Idx_IndexName] ON ##tMyTable
(
    [Fk_1_Id] ASC,
    [Fk_2_Id] ASC--,
    [Day] ASC,
    [Id] ASC
)
INCLUDE([Fk_3_Id],[Quantity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

中止的进程是一个删除语句：

delete from ##tMyTable where Fk_2_Id = 86416

允许生存的过程是一个插入。（日期为德文格式）。

insert into ##tMyTable (Fk_1_Id, Fk_2_Id, Day, Quantity) select Fk_1_Id, 86415, Day, Quantity from tOtherTableWithSimilarColumns where Fk_4_Id = 22 and Day >= '01.07.2024' and Day < '01.08.2024'

为了测试，我使用以下代码设置了两个会话。

Begin transaction

Select @@SPID as FirstTransactionProcessID

Query I want to test

commit

commit如果我执行不包括事务锁定其资源的代码，我就可以模拟死锁的情况。

之后exec sp_lock我可以检查锁。

开始之前，我在表中插入了以下内容：

insert into ##tMyTable (Fk_1_Id, Fk_2_Id, Fk_3_Id, Tag, Menge)
Select 1, 1, NULL, '2024-01-01', 22.404

在会话 1 中我执行了此操作：

insert into ##tMyTable (Fk_1_Id, Fk_2_Id, Fk_3_Id, Tag, Menge)
Select 2, 2, NULL, '2024-01-01', 22.404

然后在第 2 节中我执行了以下操作：

delete from ##tMyTable where Fk_2_Id = 1

结果是这样的： 在此处输入图片描述

spid 77 是插入，spid 69 是删除。

如您所见，删除操作尝试获取某个键的 U 锁，但未能获取，因为插入操作 X 锁定了该键。但是，我预期看到插入操作使用 IX 锁锁定页面，而删除操作想要获取同一页面的 U 锁。

问题：我的测试设置哪里错了？为什么我得到的锁与原来的死锁不同？为什么删除操作不只是在键上放置 IX 锁并在行级别上操作？两个 IX 锁是兼容的，查询不应该互相干扰。毕竟它们针对的是不同的 ID，因此是不同的行。

似乎可以解决我的问题：如果我创建仅包含 Fk_2_Id 的索引，则我的测试设置中不会出现锁定冲突。但是，我想避免在不了解我的解决方案为何有效的情况下“修复”某些问题。

死锁 XML：

<deadlock>
 <victim-list>
  <victimProcess id="process1f4f20e5468" />
 </victim-list>
 <process-list>
  <process id="process1f4f20e5468" taskpriority="0" logused="0" waitresource="PAGE: 8:1:11652007 " waittime="2865" ownerId="213325556" transactionname="DELETE" lasttranstarted="2024-08-04T10:02:44.943" XDES="0x1f4e33c3aa0" lockMode="U" schedulerid="2" kpid="9392" status="suspended" spid="56" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2024-08-04T10:02:44.937" lastbatchcompleted="2024-08-04T10:02:44.933" lastattention="1900-01-01T00:00:00.933" clientapp=".Net SqlClient Data Provider" hostname="Our-Host" hostpid="11672" isolationlevel="read committed (2)" xactid="213325556" currentdb="8" currentdbname="Our_DB" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtend="152" sqlhandle="0x02000000cff49033589c5bbc920f9e3e6c20dd52ec7b2a6b0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="adhoc" line="1" stmtend="152" sqlhandle="0x02000000a28e5e12abc21669e1bfc6fb7c33f25bfee40b1c0000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
delete from tMyTable where Fk_2_Id = 86416   </inputbuf>
  </process>
  <process id="process1f4f5011c28" taskpriority="0" logused="9944" waitresource="PAGE: 8:1:11631539 " waittime="4125" ownerId="213327344" transactionname="INSERT" lasttranstarted="2024-08-04T10:02:46.687" XDES="0x1f4e45e4460" lockMode="IX" schedulerid="4" kpid="11516" status="suspended" spid="68" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2024-08-04T10:02:46.657" lastbatchcompleted="2024-08-04T10:02:46.653" lastattention="1900-01-01T00:00:00.653" clientapp=".Net SqlClient Data Provider" hostname="Our-Host" hostpid="11672" loginname="loginname" isolationlevel="read committed (2)" xactid="213327344" currentdb="8" currentdbname="Our_DB" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtend="568" sqlhandle="0x02000000679aaf081f4d1ffe0710dbf9b238a8db3e6adb6b0000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
       insert into tMyTable (Fk_1_Id, Fk_2_Id, Day, Quantity) select Fk_1t_Id, 86415, Day, Quantity from tOtherTableWithSimilarColumns where Fk_4_Id = 22 and Day &gt;= '01.07.2024' and Day &lt; '01.08.2024'   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <pagelock fileid="1" pageid="11652007" dbid="8" subresource="FULL" objectname="Our_DB.dbo.tMyTable" id="lock1f7bf84a200" mode="IX" associatedObjectId="72057661408477184">
   <owner-list>
    <owner id="process1f4f5011c28" mode="IX" />
   </owner-list>
   <waiter-list>
    <waiter id="process1f4f20e5468" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="11631539" dbid="8" subresource="FULL" objectname="Our_DB.dbo.tMyTable" id="lock1f4e3647700" mode="U" associatedObjectId="72057661408477184">
   <owner-list>
    <owner id="process1f4f20e5468" mode="U" />
   </owner-list>
   <waiter-list>
    <waiter id="process1f4f5011c28" mode="IX" requestType="wait" />
   </waiter-list>
  </pagelock>
 </resource-list>
</deadlock>
sql-server

1 个回答

  1. Best Answer

    每个外键都应该由一个索引支持，并且您缺少一个索引FK_2_id。

    DELETE 无法使用索引，因为DELETE 中没有查找Idx_IndexName前导键。因此，它会扫描整个表，这会导致大量死锁问题。这是一个典型的书签死锁。Fk_1_Id

    所以你需要一个额外的索引。

    • 2

相关问题