阻塞 upsert 過程

設置

我有一個簡單的表：

CREATE TABLE [dbo].[StringData](
   [ID] [bigint] IDENTITY(1,1) NOT NULL,
   [DCStringID] [bigint] NOT NULL,
   [TimeStamp] [datetime] NOT NULL,
   [Hour]  AS (dateadd(hour,datediff(hour,(0),[TimeStamp]),(0))) PERSISTED,
   [Date]  AS (CONVERT([date],[TimeStamp],(0))) PERSISTED,
   [DCVoltage] [decimal](18, 2) NULL,
   [DCCurrent] [decimal](18, 2) NULL,
   [DCPower]  AS (([DCVoltage]*[DCCurrent])/(1000)) PERSISTED,
   [IsCompressed] [bit] NULL,
   [TimeStamp15Minutes]  AS (dateadd(minute,(datediff(minute,(0),[TimeStamp])/(15))*(15),(0))),
CONSTRAINT [PK_StringData1] PRIMARY KEY CLUSTERED 
(
   [TimeStamp] DESC,
   [DCStringID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

我有一個名為 upsert 的程序**$$ InsertOrUpdateStringData $$**

ALTER PROCEDURE [dbo].[InsertOrUpdateStringData]
@DCStringID bigint, @TimeStamp datetime, @DCVoltage decimal(18,2), @DCCurrent decimal(18,2)
AS
/****** avoid parameter spoofing ******/
DECLARE @DCStringID_Value AS bigint; SET @DCStringID_Value = @DCStringID;
DECLARE @TimeStamp_Value AS datetime; SET @TimeStamp_Value = @TimeStamp
DECLARE @DCVoltage_Value AS decimal(18,2); SET @DCVoltage_Value = @DCVoltage
DECLARE @DCCurrent_Value AS decimal(18,2); SET @DCCurrent_Value = @DCCurrent
/************/

MERGE [StringData] AS TARGET
USING (VALUES (@DCStringID_Value, @TimeStamp_Value))
   AS SOURCE ([DCStringID], [TimeStamp])
   ON TARGET.[DCStringID] = SOURCE.[DCStringID] AND TARGET.[TimeStamp] = SOURCE.[TimeStamp]
WHEN MATCHED THEN
   UPDATE
   SET [DCVoltage] = @DCVoltage_Value,
   [DCCurrent] = @DCCurrent_Value
WHEN NOT MATCHED THEN
   INSERT ([DCStringID], [TimeStamp], [DCVoltage], [DCCurrent]) 
   VALUES (@DCStringID_Value, @TimeStamp_Value, @DCVoltage_Value, @DCCurrent_Value);

使用

許多不同的應用程序執行緒正在使用**$$ InsertOrUpdateStringData $$**將數據同步更新插入表的過程。

表索引使用得很好，一次執行非常快，大約需要 31 毫秒。

問題

如果執行另一個非選擇表操作（如插入批量），則 are 阻塞很長時間。

很遺憾**$$ sp_WhoIsActive $$如果$$ InsertOrUpdateStringData $$**過程呼叫也相互阻塞。但看起來有一個鎖鏈，因為它是鎖定超過 10,000 毫秒的唯一解釋，就像我的範例中一樣。

題

看來我的 upsert 過程正在鎖定整個表並減慢其他插入操作。

我可以做些什麼來優化我的查詢以避免長時間阻塞？

更新 1 -與 Nic answere 相關

鎖定發生在所有非選擇操作上，因為我寫的批量插入只是一個範例。

請在此處查看 10 秒鎖定簡單合併呼叫（阻塞會話 176 是**$$ InsertOrUpdateStringData $$**稱呼）：

我也試過**@get_locks=1**這是結果。也許它可以幫助您了解更多細節。

更新 2 -與 Nic answere 相關

這是一個例子：

sid 52 被 sid 119 阻塞超過 40 秒！？但是 sid 119 似乎沒有被阻止。我真的不明白這一點。

由於您的 spid 119 在

$$ sleeping $$狀態，我建議您檢查：

• DBCC Opentran() 檢查 119 上是否有任何打開的事務（根據我的經驗很有可能）
• sp_lock 52 檢查未授予 spid 52 中的哪些鎖
• sp_lock 119 檢查它持有什麼鎖，您可以將鎖追溯到資源（例如表或索引或鍵等）

另外，也許您可以在數據庫上打開 READ_COMMITTED_SNAPSHOT 以避免讀取和寫入操作之間的阻塞。

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