具有可重複讀隔離級別的死鎖

嘗試了解在呼叫儲存過程時使用可重複讀取隔離級別發生的死鎖。

這是死鎖 xml：

   <TextData>
 <deadlock-list>
<deadlock victim="process6fac029848">
 <process-list>
  <process id="process6fac029848" taskpriority="0" logused="0" waitresource="KEY: 8:72078600230076416 (d3c590b351cd)" waittime="1171" 
           ownerId="26116633497" transactionname="delete" lasttranstarted="2021-01-07T10:35:38.317" XDES="0x12553cbe3b0" 
           lockMode="U" schedulerid="3" kpid="19776" status="suspended" spid="92" sbid="0" ecid="0" priority="0" trancount="2" 
           lastbatchstarted="2021-01-07T10:35:38.310" lastbatchcompleted="2021-01-07T10:35:38.310" lastattention="1900-01-01T00:00:00.310" 
           clientapp=".Net SqlClient Data Provider" hostname="web01" hostpid="34852" loginname="someuser" 
           isolationlevel="repeatable read (3)" xactid="26116633497" currentdb="8" currentdbname="db1" lockTimeout="4294967295" clientoption1="673316896" 
           clientoption2="128056">
   <executionStack>
    <frame procname="db1.schema.deleteSP" line="124" stmtstart="8308" stmtend="8870" 
           sqlhandle="somehandle">
UPDATE [schema].[TableName] WITH(UPDLOCK)
       SET
           Status = 1 /* STATUS_DELETED */,
           userid = @id,
           UpdateTime = GETUTCDATE()
       FROM @oldatt oa
       WHERE [TableName].[Id] = oa.Id     </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 8 Object Id = 1688915952]    </inputbuf>
  </process>
  <process id="process6fab017088" taskpriority="0" logused="284" waitresource="KEY: 8:72064801531101184 (4b819525e255)" waittime="1283" 
  ownerId="26116632474" transactionname="delete" lasttranstarted="2021-01-07T10:35:38.290" XDES="0x44037ec3b0" 
  lockMode="X" schedulerid="25" kpid="25152" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" 
  lastbatchstarted="2021-01-07T10:35:38.287" lastbatchcompleted="2021-01-07T10:35:38.283" lastattention="1900-01-01T00:00:00.283" 
  clientapp=".Net SqlClient Data Provider" hostname="web02" hostpid="57552" loginname="someuser" 
  isolationlevel="repeatable read (3)" xactid="26116632474" currentdb="8" currentdbname="db1" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
   <executionStack>
    <frame procname="db1.schema.deleteSP" line="124" stmtstart="8308" stmtend="8870" sqlhandle="somehandle">
UPDATE [schema].[TableName] WITH(UPDLOCK)
       SET
           Status = 1 /* STATUS_DELETED */,
           userid = @id,
           UpdateTime = GETUTCDATE()
       FROM @oldatt oa
       WHERE [TableName].[Id] = oa.Id     </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 8 Object Id = 1688915952]    </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <keylock hobtid="72078600230076416" dbid="8" objectname="db1.schema.TableName" indexname="IX_TableName_Id_Status_userid_UpdateTime" id="lock287c93a100" mode="U" associatedObjectId="72078600230076416">
   <owner-list>
    <owner id="process6fab017088" mode="U" />
   </owner-list>
   <waiter-list>
    <waiter id="process6fac029848" mode="U" requestType="wait" />
   </waiter-list>
  </keylock>
  <keylock hobtid="72064801531101184" dbid="8" objectname="db1.schema.TableName" indexname="IX_TableName_SomeId_Id" id="lock55fc471880" mode="S" associatedObjectId="72064801531101184">
   <owner-list>
    <owner id="process6fac029848" mode="S" />
   </owner-list>
   <waiter-list>
    <waiter id="process6fab017088" mode="X" requestType="convert" />
   </waiter-list>
  </keylock>
 </resource-list>
</deadlock>
</deadlock-list>
</TextData>

具有相關索引的創建表：

CREATE TABLE [schema].[TableName](
   [Id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
   [SomeId] [int] NOT NULL,
   [Userid] [int] NOT NULL,
   [UpdateTime] [datetime] NOT NULL,
   [RecordStatus] [tinyint] NOT NULL,
CONSTRAINT [PK_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]
GO
--- 2 indexes that are part of deadlock 
IX_TableName_SomeId_Id = filtered Index - SomeId, Id (include status) where status = 0
IX_TableName_Id_Status_userid_UpdateTime - non clustered index - Id include(status, userid, updatetime)

SP呼叫是：

• 隔離級別可重複讀
• 開始交易
• 從基表中進行選擇並將受影響的行插入到臨時變數中。
• 執行另一個 SP
• 更新基表。<<— 這是發生死鎖的地方
• 將數據與一些業務邏輯合併
• 如果沒有錯誤，則送出事務

基表具有對審計表進行插入和更新以跟踪更改的觸發器。

我只是想弄清楚為什麼我會遇到太多死鎖。

更新 ：

declare @oldtable(
       Id bigint,
       someId int
       PRIMARY key clustered (id)
   )
   insert into @oldtable (
       id,
       someid
   )
   select id, someid
   from [schema].[TableName]
   where id = @id and status = 0 and someid = @someid 

儲存過程流控制：

variables 
   @id 
   @someid 
   
begin 
   set nocount on
   set transaction isolation level repeatable read
   set xact_abort on

   begin tran delete
   begin try

   declare @oldtable(
       Id bigint,
       someId int
       PRIMARY key clustered (id)
   )
   insert into @oldtable (
       id,
       someid
   )
   select id, someid
   from [schema].[TableName]
   where id = @id and status = 0 and someid = @someid 

   some SP runs

   -- update statememt that causes deadlock 
   -- it basically sets status = 1 -- deleted (soft delete)

   -- merge data into tables using CTE and MERGE 
   END TRY

   begin catch 
       -- error handling 
       -- roll back tran 
   end catch

   if @@trancount > 0
   begin
       commit tran delete
   END

END

這是我的假設：

讓我們呼叫 proc P1和P2的死鎖呼叫。

P1和P2都使用過濾後的索引來定位行。因為這是沒有其他提示的可重複讀取，所以它們使用 S 鎖，並且不釋放它們。它們碰巧包括一個共同的行。我們將在過濾索引Row A中呼叫它的位置。

此時 P1 和 P2 在A 行都有 S 鎖。

現在，更新開始。

P1從另一個索引中讀取以定位需要更新的行。它在讀取時使用 U 鎖，並在Row B上放置一個 U 鎖，它實際上表示與 A 相同的行，只是在不同的索引中。

現在P1在 A 行有一個 S 鎖，在B**行有一個 U 鎖。

P1看到它有一行要修改，並開始該過程。它還必須從過濾索引中刪除該行，這將需要 X 鎖。

P1嘗試將其 S 鎖轉換為 X 鎖，但被P2的 S 鎖阻止。

P2開始更新。它試圖獲取B 行上的 U 鎖，但被P1的 U 鎖阻止。死鎖。

我相信在初始選擇上使用 UPDLOCK 提示應該可以避免死鎖。

引用自：https://dba.stackexchange.com/questions/282786