具有更新同一觸發表中的列的插入觸發器的表的插入死鎖

根據規範，每個表都使用一個 guid 作為主鍵。另一個要求是每個資源都需要一個 6 數字標識符（100001、100002、…）作為公共 ID，該 ID 在擁有該資源的客戶中是唯一的（通過 相關Resource -> Location -> Customer）。

我嘗試使用插入後觸發器設置這 6 個數字。邏輯工作正常，但是當我模擬許多插入時會導致死鎖。我可以通過向tablockx插入添加提示來消除這種死鎖。但是，由於 Linq2SQL 將用於插入數據，因此我希望避免在插入時添加鎖定提示。我希望觸發器中的某些鎖定提示或索引提示的組合可能會起作用。

架構：

CREATE TABLE [Resource] (
   [pkGUID]                UNIQUEIDENTIFIER NOT NULL,
   [nIdentifier]           INT NOT NULL DEFAULT 0,
   [fkLocation]            UNIQUEIDENTIFIER NOT NULL,
   CONSTRAINT [PK_Resource] PRIMARY KEY CLUSTERED ([pkGUID] ASC)
)
CREATE TABLE [Location] (
   [pkGUID]                UNIQUEIDENTIFIER NOT NULL,
   [fkCustomer]            UNIQUEIDENTIFIER NOT NULL,
   CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED ([pkGUID] 
)
CREATE TABLE [Customer] (
   [pkGUID]                UNIQUEIDENTIFIER NOT NULL,
   CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ([pkGUID] 
)

目前Resource除了聚集主鍵之外，表上沒有索引。

觸發：

CREATE TRIGGER [dbo].[Trigger_Resource_Insert]
ON [dbo].[Resource]
FOR INSERT
AS
BEGIN
   SET NoCount ON

   ;with relevantCustomers as (
       select c.pkGUID
       from inserted i
       join Location l on i.fkLocation = l.pkGUID
       join Customer c on l.fkCustomer = c.pkGUID
       group by c.pkGUID
   ), maxNumbers as (
       select rc.pkGUID, max = case when max(x.nIdentifier) < 100000 then 100000 else max(x.nIdentifier) end
       from relevantCustomers rc
       join Location l on l.fkCustomer = rc.pkGUID
       join Resource x on x.fkLocation = l.pkGUID
       group by rc.pkGUID
   ), numbers as (
       select i.pkGUID, number = row_number() over (partition by n.pkGUID order by i.pkGUID) + n.max
       from inserted i
       join Location l on i.fkLocation = l.pkGUID
       join maxNumbers n on n.pkGUID = l.fkCustomer
   )
   update x
   set nIdentifier = n.number
   from Resource x
   join numbers n on x.pkGUID = n.pkGUID
END

生成一堆數據的插入：

;with x as (
   select num = 1
   union all
   select x.num + 1
   from x
   where x.num < {0}
), l as (
   select top {1} fkLocation = pkGUID, sLocationName from Location order by sLocationName
)
insert into Resource with (tablockx) (pkGUID, fkLocation, ...)
select newid(), l.fkLocation, ...
from x
cross join l

tablockx在沒有提示的情況下執行插入時來自 SQL 分析器的死鎖圖：

我不得不說我不太同意一些評論，即你所看到的必然是一個框架問題。Linq to SQL 框架確實允許您指定儲存過程作為行的輸入方式。

話雖如此，我強烈建議您盡可能使用該機製而不是觸發器。您將能夠在儲存過程中添加鎖定提示，並且您的插入將正常工作。

但是，您的規範對我來說似乎非常可疑，而 IMO 您現在似乎遇到的問題是很好的反例，說明了為什麼人們可能不想追求這樣的設計。所以我的（純粹是修辭）問題是針對設計師的。那個有問題的序號應該代表什麼？如果它是時間序列，那麼為什麼不使用時間並使用視窗函式來對視圖中的行進行排序（您也可以將 Linq 指向 SQL 到視圖）？是否有某種無間隙序列號的意圖？您是否簽署了所有復雜性和鎖定您將不得不處理只是為了保持這個數字？

不幸的是，我認為沒有真正的答案——沒有可以在你的框架和設計中使用的神奇的提示組合——可以解決你的問題。IMO 我的回答是退後一步，批判性地審視你的設計。

觸發器的程式碼似乎太多了。

讓我們退後一步，看看你想要實現什麼。

您想為您插入的任何記錄設置 nIdentifier。

我的解決方案是根本不使用觸發器，即

1. 插入記錄，使用 OUTPUT 擷取您插入臨時表的內容
2. 使用臨時表連接回資源表並進行更新

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