Tablock 提示觸發死鎖

我使用最少的日誌記錄將兩個數據集插入到一個空堆表中，使用兩個並行執行的執行 SQL 任務並使用以下形式的 SQL。

INSERT INTO Table (TABLOCK) SELECT FROM ...

作業掛起後，其中一個 SQL 任務成為死鎖犧牲品。下面是死鎖圖的 XML 輸出。

有人可以解釋引擎蓋下發生了什麼嗎？

 <resource-list>
  <objectlock lockPartition="0" objid="1586156746" subresource="FULL" dbid="7" objectname="dbo.TargetTable" id="lock7374a00" mode="IX" associatedObjectId="1586156746">
   <owner-list>
    <owner id="process9609dc8" mode="Sch-S"/>
    <owner id="process9609dc8" mode="IX"/>
   </owner-list>
   <waiter-list>
    <waiter id="process5e13048" mode="X" requestType="convert"/>
   </waiter-list>
  </objectlock>
  <objectlock lockPartition="0" objid="1586156746" subresource="FULL" dbid="7" objectname="dbo.TargetTable" id="lock7374a00" mode="IX" associatedObjectId="1586156746">
   <owner-list>
    <owner id="process5e13048" mode="Sch-S"/>
    <owner id="process5e13048" mode="IX"/>
   </owner-list>
   <waiter-list>
    <waiter id="process9609dc8" mode="X" requestType="convert"/>
   </waiter-list>
  </objectlock>
 </resource-list>

事情變得更加棘手，因為我發現在大多數情況下，兩個執行 SQL 任務可以成功並行執行。試試下面：

Create table dbo.TablockInsert (c1 int, c2 int, c3 int)

--then issue the script in two Execute Sql Task in parallel you won't fail:
insert into dbo.TablockInsert(TABLOCK) SELECT 1, 1, 1

由於唯一的區別是 SELECT… FROM… 語句，看起來 SELECT… FROM… 語句會影響這裡的鎖定模式？

數據載入性能指南是為 SQL Server 2008 編寫的，但據我所知，微軟在這方面沒有對堆進行任何改進。這是您的載入方案的報價：

批量載入一個未分區的空表

將數據載入到非分區表中雖然是一個簡單的操作，但可以通過多種方式進行優化。

…

僅當所選批量方法對錶發出批量更新 (BU) 鎖時，才可能對堆進行多個並發插入操作。兩個批量更新 (BU) 鎖是兼容的，因此兩個批量操作可以同時執行。

在這種情況下，INSERT … SELECT和 SELECT INTO 都有一個缺點。這兩個操作都在目標上採用排他 (X) 表級鎖定。這意味著在給定時間只能執行一個批量載入操作，從而限制了可伸縮性。但是，如果您指定 TABLOCK 提示，BCP、BULK INSERT 和集成服務都能夠獲取批量更新 (BU) 鎖。

重要的部分是您沒有獲得 BU 鎖INSERT ... SELECT。您將始終在表上獲得排他鎖，因此一次INSERT只能執行一個。

在評論中，您說您將插入 100k 行或更少，並且在插入期間其他程序不會在表上執行。當向數據庫發送兩個 INSERT 查詢時，我預計會發生以下三件事之一：

1. 一個插入首先執行並阻塞另一個插入。第二次插入等到第一次插入完成。
2. 一個插入在第二個插入開始之前完成。沒有顯式阻塞，但它們不會同時執行。
3. 您會遇到死鎖，並且只有一個插入成功完成。

在所有情況下，向查詢中添加提示都會使您受益或不會受到傷害TABLOCKX，因此這是我解決死鎖的建議。如果您想知道為什麼有時會發生死鎖，您需要尋找另一個答案。

對於真正需要並行插入的不同場景，解決 BU 問題的兩種方法是對堆進行分區並將每個會話插入到單獨的分區中，或者通過 BCP、BULK INSERT 或集成服務載入數據.

dbo.TargetTable您正在從兩個會話中插入，並且都使用TABLOCK提示 。兩者都process9609dc8和process5e13048程序保持Sch-S以及IX彼此兼容的鎖，因此兩個程序可以同時保持。但兩者都想將IX鎖轉換為Exclusive X類型。X鎖彼此不兼容。因此，SQL Server 選擇了其中一個會話作為死鎖犧牲品，而不是無限等待對方。

基本的死鎖資訊。

鎖定兼容性（數據庫引擎）圖表。

檢測和結束死鎖。

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