呼叫相同過程的相同資源死鎖

我想向社區尋求解決這種僵局情況的幫助：

<deadlock-list>
<deadlock victim="process17392264e8">
 <process-list>
  <process id="process17392264e8" taskpriority="5" logused="0" waitresource="OBJECT: 7:2018106230:0 " waittime="4449" ownerId="394509589" transactionname="user_transaction" lasttranstarted="2019-06-05T12:06:41.320" XDES="0x18ed9516f0" lockMode="IX" schedulerid="4" kpid="8348" status="suspended" spid="143" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2019-06-05T12:06:41.310" lastbatchcompleted="2019-06-05T12:06:41.310" lastattention="1900-01-01T00:00:00.310" clientapp="Helios Orange" hostname="???" hostpid="508" loginname="???" isolationlevel="read committed (2)" xactid="394509589" currentdb="7" currentdbname="XXXXXX" lockTimeout="4294967295" clientoption1="673384544" clientoption2="128568">
   <executionStack>
    <frame procname="XXXXXX.dbo.hp_ZdvojeniKonstrukceATech" line="73" stmtstart="6876" stmtend="7250" sqlhandle="0x03000700efbf9d27f8924d01e9a8000001000000000000000000000000000000000000000000000000000000">
UPDATE CZ SET IntPermanentniZmena=0 
        FROM TabDavka D 
          INNER JOIN TabCZmeny CZ ON (CZ.ID=D.ZmenaOd AND CZ.IntPermanentniZmena=1) 
        WHERE D.IDDilce=@IDKusovni     </frame>
    <frame procname="adhoc" line="2" stmtstart="44" stmtend="152" sqlhandle="0x010007000f5ba906f09b4b6f0700000000000000000000000000000000000000000000000000000000000000">
EXEC @ret=hp_ZdvojeniKonstrukceATech 120234, 39008, 1     </frame>
   </executionStack>
   <inputbuf>
DECLARE @ret integer
EXEC @ret=hp_ZdvojeniKonstrukceATech 120234, 39008, 1
SELECT @ret    </inputbuf>
  </process>
  <process id="process15bf6ce8c8" taskpriority="5" logused="0" waitresource="OBJECT: 7:2018106230:0 " waittime="6020" ownerId="394493191" transactionname="user_transaction" lasttranstarted="2019-06-05T12:06:39.750" XDES="0x16fb190ea0" lockMode="IX" schedulerid="1" kpid="3940" status="suspended" spid="83" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2019-06-05T12:06:39.750" lastbatchcompleted="2019-06-05T12:06:39.750" lastattention="1900-01-01T00:00:00.750" clientapp="Helios Orange" hostname="???" hostpid="14924" loginname="???" isolationlevel="read committed (2)" xactid="394493191" currentdb="7" currentdbname="XXXXXX" lockTimeout="4294967295" clientoption1="673384544" clientoption2="128568">
   <executionStack>
    <frame procname="XXXXXX.dbo.hp_ZdvojeniKonstrukceATech" line="73" stmtstart="6876" stmtend="7250" sqlhandle="0x03000700efbf9d27f8924d01e9a8000001000000000000000000000000000000000000000000000000000000">
UPDATE CZ SET IntPermanentniZmena=0 
        FROM TabDavka D 
          INNER JOIN TabCZmeny CZ ON (CZ.ID=D.ZmenaOd AND CZ.IntPermanentniZmena=1) 
        WHERE D.IDDilce=@IDKusovni     </frame>
    <frame procname="adhoc" line="2" stmtstart="44" stmtend="152" sqlhandle="0x0100070009fde604c031f1451a00000000000000000000000000000000000000000000000000000000000000">
EXEC @ret=hp_ZdvojeniKonstrukceATech 120073, 39047, 1     </frame>
   </executionStack>
   <inputbuf>
DECLARE @ret integer
EXEC @ret=hp_ZdvojeniKonstrukceATech 120073, 39047, 1
SELECT @ret    </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <objectlock lockPartition="0" objid="2018106230" subresource="FULL" dbid="7" objectname="XXXXXX.dbo.TabCzmeny" id="lock134d414780" mode="S" associatedObjectId="2018106230">
   <owner-list>
    <owner id="process15bf6ce8c8" mode="S"/>
    <owner id="process15bf6ce8c8" mode="IX" requestType="convert"/>
   </owner-list>
   <waiter-list>
    <waiter id="process17392264e8" mode="IX" requestType="convert"/>
   </waiter-list>
  </objectlock>
  <objectlock lockPartition="0" objid="2018106230" subresource="FULL" dbid="7" objectname="XXXXXX.dbo.TabCzmeny" id="lock134d414780" mode="S" associatedObjectId="2018106230">
   <owner-list>
    <owner id="process17392264e8" mode="S"/>
    <owner id="process17392264e8" mode="IX" requestType="convert"/>
   </owner-list>
   <waiter-list>
    <waiter id="process15bf6ce8c8" mode="IX" requestType="convert"/>
   </waiter-list>
  </objectlock>
 </resource-list>
</deadlock>
</deadlock-list>

它位於同一資源（表）上，並且是由從兩個會話呼叫的同一過程引起的。

程序的簡化流程是這樣的：

SELECT @Variable = Column FROM Table WITH (HOLDLOCK)
…
UPDATE Table SET...

我將不勝感激任何建議如何防止這種死鎖情況？

問題是在事務期間在該資源上HOLDLOCK創建“共享”鎖（鎖）。Mode: S這不會阻止其他程序（例如在另一個會話中執行的同一程序）將自己的“共享”鎖放在同一資源上。但是隨後兩個會話都進入了UPDATE試圖將其鎖轉換（即升級）為“Intent eXclusive”（Mode: IX鎖）的語句，由於另一個會話仍持有其“共享”鎖，因此無法完成。

如果 的目的HOLDLOCK是為該會話保留行（以便其他人無法修改它），那麼您需要防止其他會話能夠在同一資源上放置自己的“共享”鎖（同時，當然）。

一種方法是更改HOLDLOCK​​to UPDLOCK（即“更新”鎖）。這允許其他事務對同一資源（如HOLDLOCK）採取相同的鎖定，但其中一個事務可以轉換/升級為XLOCK然後將強制所有其他事務（仍然具有它們的UPDLOCK）等待直到事務結束（不像HOLDLOCK） . 此外，雖然鎖仍然是“更新”鎖（即在轉換為“排他”鎖之前），但其他事務/會話可以讀取該資源（也像HOLDLOCK）。

如果至少有一行要更新並且鎖轉換為“獨占”鎖，並且您有其他程序需要訪問此表而無需等待所有這些更新完成，那麼您有幾個選擇：

1. 使用WITH (NOLOCK)提示（讀取已修改但尚未送出，因此可能會回滾行）
2. 使用WITH (READPAST)提示（完全跳過行，就好像它不存在一樣）
3. 查看SNAPSHOT ISOLATION（讀取目前/未修改的行）

以下測試應說明所有這些：

查詢選項卡 A

將以下內容粘貼到 SSMS（即會話）中的一個查詢選項卡中，並僅執行塊引用中的語句（現在；即USE,CREATE TABLE和INSERT）

USE [tempdb];

/*
-- DROP TABLE dbo.test;

USE [tempdb];
CREATE TABLE dbo.test
(
 ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
 [col1] INT NOT NULL
);
INSERT INTO dbo.test ([col1]) VALUES (11), (13), (15);
*/

DECLARE @ID INT;

BEGIN TRAN;

SELECT @ID = [ID] FROM dbo.test WITH (ROWLOCK, UPDLOCK) WHERE [col1] = 13;
WAITFOR DELAY '00:00:10.000';

UPDATE dbo.test SET [col1] = 88 WHERE [ID] = @ID;
WAITFOR DELAY '00:00:5.000';

ROLLBACK TRAN;

查詢選項卡 B

將以下內容粘貼到 SSMS 中的另一個查詢選項卡（即另一個會話），但不要執行它（還）。

USE [tempdb];

DECLARE @ID INT;

BEGIN TRAN;

SELECT @ID = [ID] FROM dbo.test WITH (ROWLOCK, UPDLOCK) WHERE [col1] = 15;
WAITFOR DELAY '00:00:10.000';

UPDATE dbo.test SET [col1] = 999 WHERE [ID] = @ID;
WAITFOR DELAY '00:00:5.000';

ROLLBACK TRAN;

查詢選項卡 C

將以下內容粘貼到 SSMS 中的另一個查詢選項卡（即另一個會話），但不要執行它（還）。

USE [tempdb];

SELECT * FROM sys.dm_tran_locks --WHERE [request_session_id] IN (x, y);

SELECT 'readpast', * FROM dbo.test WITH (READPAST);

SELECT 'nolock', * FROM dbo.test WITH (NOLOCK);
GO -- separate next query as it will lock up once an UPDATE statement starts

SELECT 'no hint', * FROM dbo.test

主要測試

1. 轉到“查詢選項卡 A”，點擊F5。

2. 等待一兩秒鐘，然後轉到“查詢選項卡 B”，點擊F5。

3. 轉到“查詢選項卡 C”，點擊F5。

4. 對於第一個查詢（即sys.dm_tran_locks），您應該會看到 6 行左右。如果系統中有一堆東西，請取消註釋該WHERE子句並用“查詢選項卡 A”和“查詢選項卡 B”的會話 ID（即 SPID）替換x和。y

5. 您還應該為 3 個SELECT ... FROM dbo.test ...查詢中的每一個返回 3 行。到目前為止，只使用了“更新”鎖。

6. 等待一兩秒鐘，然後F5再次擊中。這樣做幾次，直到最終查詢沒有立即返回。

7. 一旦“查詢選項卡 A”進入UPDATE語句，幾秒鐘：

   1. 您只會從第一個SELECT ... FROM dbo.test ...查詢中返回 2 行（即READPAST）
   2. 您將從下一個查詢（即NOLOCK）中獲取所有 3 行，但第 3 行將顯示未送出的值。
   3. 最終查詢根本不會回來（即沒有提示），因為它現在被“eXclusive”鎖阻塞（這就是為什麼GO存在，就在這個查詢之前，這樣其他人就可以在這個查詢時返回他們的結果集被封鎖）

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