INSERT/UPDATE 儲存過程本身死鎖

我有一個表（Database2.dbo.OrganizerDataDependencyChange），其中包含有關某些其他表中的行上次更改時間的資訊。在這些表中的每一個上，我都有一個呼叫儲存過程 (Database2.dbo.SaveOrganizerDataDependencyChange) 的觸發器，該儲存過程只是用觸發器觸發的時間更新 Database2.dbo.OrganizerDataDependencyChange。這些觸發器是從 Database1 或 Database2 觸發的，因此它們通常是跨數據庫呼叫。

當表上的兩個不同行正在更新時，我在 Database1.dbo.OrganizerDataDependencyChange 表上遇到讀寫死鎖，我不明白為什麼。表上只有一個索引，它是完全覆蓋查詢的聚集索引，因此不會出現查找死鎖，雖然 proc 中有兩條語句，但我特意將其重寫為我理解的最佳方式避免並發問題，使用 WHERE NOT EXISTS 而不是使用 IF/ELSE 邏輯，所以它不應該從不同的角度出現在這個索引上，對吧？因為它是在觸發器和跨數據庫中觸發的，所以我在重現問題時遇到了一些困難，我當然可以重現阻塞，但這應該沒問題，也是我所期望的。

有人可以幫我理解這裡發生了什麼嗎？我可能可以用 NOLOCK 提示或 applock 來修復它，但我仍然不明白它為什麼會發生。

這是表格：

CREATE TABLE [dbo].[OrganizerDataDependencyChange](
[TableID] [INT] NOT NULL,
[Database] [VARCHAR](150) NOT NULL,
[Updated] [DATETIME] NULL
)
CREATE CLUSTERED INDEX [CX_OrganizerDataDependencyChange_TableID_DB] ON [dbo].[OrganizerDataDependencyChange]
(
[TableID] ASC,
[Database] ASC
)

這是儲存過程：

CREATE PROCEDURE [dbo].[SaveOrganizerDataDependencyChange]
(
@TableID int,
@Database varchar(150)
)
AS
BEGIN

SET NOCOUNT ON;

   DECLARE @rowcount INT;

INSERT INTO dbo.OrganizerDataDependencyChange
       ( TableID, [Database], Updated )
SELECT TOP 1
   TableID = @TableID,
   [Database] = ISNULL(@Database, ''),
   Updated = GETDATE()
FROM dbo.OrganizerDataDependencyChange
WHERE NOT EXISTS
(
   SELECT
       1
   FROM dbo.OrganizerDataDependencyChange
   WHERE TableID = @TableID
   AND [Database] = @Database
)

SET @rowcount = @@ROWCOUNT  --How many rows were inserted?

UPDATE dbo.OrganizerDataDependencyChange
SET Updated = GETDATE()
WHERE @rowcount = 0 --Only run this if no rows were inserted
AND TableID = @TableID
AND [Database] = ISNULL(@Database, '')

END

最後，我從 XE 中得到的幾個死鎖圖顯示了荒謬的嵌套觸發器：

死鎖 1

死鎖 2

MSSQL2014 標準，如果這有所作為。隔離級別是預設的已送出讀。如果還有什麼我可以分享以闡明更多資訊，請告訴我！

我想你會發現這種模式更好：

BEGIN TRANSACTION;

UPDATE dbo.OrganizerDataDependencyChange WITH (HOLDLOCK)
 SET Updated = GETDATE()
 WHERE TableID = @TableID
 AND [Database] = ISNULL(@Database, '');

IF @@ROWCOUNT = 0
BEGIN
 INSERT INTO dbo.OrganizerDataDependencyChange
     ( TableID, [Database], Updated )
 VALUES(@TableID, ISNULL(@Database, ''), GETDATE());
END

COMMIT TRANSACTION;

不要再考慮必須“檢查”是否有要更新的行，然後更新它，這可能會導致兩次完整掃描。**只是嘗試更新它。**如果沒有更新任何行，則沒有傷害，沒有犯規 - 您可以簡單地執行插入（我在這裡稍微討論一下）。

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