我遇到了大量阻止我的 SELECT 操作的 INSERT 問題。

架構

我有一張這樣的桌子：

CREATE TABLE [InverterData](
   [InverterID] [bigint] NOT NULL,
   [TimeStamp] [datetime] NOT NULL,    
   [ValueA] [decimal](18, 2) NULL,
   [ValueB] [decimal](18, 2) NULL
   CONSTRAINT [PrimaryKey_e149e28f-5754-4229-be01-65fafeebce16] PRIMARY KEY CLUSTERED 
   (
       [TimeStamp] DESC,
       [InverterID] ASC
   ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
   , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
   , ALLOW_PAGE_LOCKS = ON)
)

我也有這個小助手程序，它允許我使用 MERGE 命令插入或更新（衝突更新）：

CREATE PROCEDURE [InsertOrUpdateInverterData]
   @InverterID bigint, @TimeStamp datetime
   , @ValueA decimal(18,2), @ValueB decimal(18,2)
AS
BEGIN
   MERGE [InverterData] AS TARGET
       USING (VALUES (@InverterID, @TimeStamp, @ValueA, @ValueB))
       AS SOURCE ([InverterID], [TimeStamp], [ValueA], [ValueB])
       ON TARGET.[InverterID] = @InverterID AND TARGET.[TimeStamp] = @TimeStamp
   WHEN MATCHED THEN
       UPDATE
       SET [ValueA] = SOURCE.[ValueA], [ValueB] = SOURCE.[ValueB]              
   WHEN NOT MATCHED THEN
       INSERT ([InverterID], [TimeStamp], [ValueA], [ValueB]) 
       VALUES (SOURCE.[InverterID], SOURCE.[TimeStamp], SOURCE.[ValueA], SOURCE.[ValueB]);
END

用法

我現在已經在多台伺服器上執行服務實例，通過快速呼叫[InsertOrUpdateInverterData]過程來執行大量更新。

還有一個網站對[InverterData]錶進行 SELECT 查詢。

問題

如果我對[InverterData]錶進行 SELECT 查詢，它們會在不同的時間跨度內進行，這取決於我的服務實例的 INSERT 使用情況。如果我暫停所有服務實例，則 SELECT 會非常快，如果實例執行快速插入，則 SELECT 會變得非常慢，甚至超時取消。

嘗試

我在[sys.dm_tran_locks]桌子上做了一些選擇來找到鎖定過程，就像這樣

SELECT
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingText,
tl.request_mode

FROM sys.dm_tran_locks AS tl

INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

這是結果：

S = 共享。保持會話被授予對資源的共享訪問權限。

題

[InsertOrUpdateInverterData]為什麼僅使用 MERGE 命令的過程會阻止 SELECT ？

我是否必須在內部使用某種具有定義隔離模式的事務[InsertOrUpdateInverterData]？

更新 1（與@Paul 的問題有關）

基於 MS-SQL 伺服器內部報告關於[InsertOrUpdateInverterData]以下統計：

• 平均 CPU 時間：0.12 毫秒
• 平均讀取程序：5.76 per/s
• 平均寫入程序：0.4 per/s

基於此，看起來 MERGE 命令主要忙於讀取將鎖定表的操作！（？）

更新 2（與@Paul 的問題有關）

該[InverterData]表具有以下儲存統計資訊：

• 數據空間：26,901.86 MB
• 行數：131,827,749
• 分區：真
• 分區數：62

這是（幾乎）完整的sp_WhoIsActive結果集：

SELECT命令

• dd hh:mm:ss.mss: 00 00:01:01.930
• session_id: 73
• 等待資訊：（12629 毫秒）LCK_M_S
• 中央處理器：198
• 阻塞會話ID：146
• 讀取：99,368
• 寫：0
• 狀態：暫停
• open_tran_count: 0

阻塞[InsertOrUpdateInverterData]命令

• dd hh:mm:ss.mss: 00 00:00:00.330
• session_id: 146
• 等待資訊：空
• 中央處理器：3,972
• 阻塞會話ID：NULL
• 讀取：376,95
• 寫：126
• 狀態：睡覺
• open_tran_count: 1

首先，雖然與主要問題稍有無關，但您的MERGE陳述可能由於競爭條件而存在錯誤風險。簡而言之，問題在於多個並發執行緒可能會得出目標行不存在的結論，從而導致插入嘗試發生衝突。根本原因是不可能在不存在的行上獲取共享或更新鎖。解決方案是添加提示：

MERGE [dbo].[InverterData] WITH (SERIALIZABLE) AS [TARGET]

可序列化的隔離級別提示確保鎖定行所在的鍵範圍。你有一個唯一的索引來支持範圍鎖定，所以這個提示不會對鎖定產生不利影響，你只會獲得對這種潛在競爭條件的保護。

主要問題

為什麼會被SELECTs屏蔽

$$ InsertOrUpdateInverterData $$只使用MERGE命令的程序？

在預設鎖定讀送出隔離級別下，讀取數據時會使用共享 (S) 鎖，並且通常（儘管並非總是）在讀取完成後立即釋放。一些共享鎖被保留到語句的末尾。

語句修改數據，因此它會在定位要更改的MERGE數據時獲取 S 或更新 (U) 鎖，這些鎖在執行實際修改之前轉換為排他 (X) 鎖。U 和 X 鎖都必須保持到事務結束。

這在所有隔離級別下都是正確的，除了“樂觀”快照隔離(SI) 不與版本控制讀取送出混淆，也稱為讀取送出快照隔離(RCSI)。

您的問題中沒有任何內容顯示等待 S 鎖的會話被持有 U 鎖的會話阻塞。這些鎖是兼容的。幾乎可以肯定，任何阻塞都是由持有的 X 鎖上的阻塞引起的。當在短時間內獲取、轉換和釋放大量短期鎖時，這可能有點難以捕捉。

open_tran_count: 1InsertOrUpdateInverterData 上的命令值得研究。儘管該命令沒有執行很長時間，但您應該檢查您沒有不必要的包含事務（在應用程序或更高級別的儲存過程中）。最佳實踐是盡可能縮短事務。這可能沒什麼，但您絕對應該檢查一下。

潛在的解決方案

正如 Kin 在評論中建議的那樣，您可以查看在此數據庫上啟用行版本控制隔離級別（RCSI 或 SI）。RCSI 是最常用的，因為它通常不需要太多的應用程序更改。一旦啟用，預設的讀送出隔離級別使用行版本而不是使用 S 鎖進行讀取，因此減少或消除了 SX 阻塞。一些操作（例如外鍵檢查）在 RCSI 下仍然需要 S 鎖。

請注意，儘管行版本會消耗 tempdb 空間，從廣義上講，它與更改活動的速率和事務的長度成正比。您需要在負載下徹底測試您的實現，以了解和計劃 RCSI（或 SI）對您的情況的影響。

如果您想本地化版本控制的使用，而不是為整個工作負載啟用它，SI 可能仍然是更好的選擇。通過將 SI 用於讀取事務，您將避免讀取器和寫入器之間的爭用，代價是讀取器在任何並發修改開始之前看到行的版本（更準確地說，SI 下的讀取操作將始終看到已送出狀態SI 事務開始時的行）。將 SI 用於寫入事務幾乎沒有好處，因為仍然會使用寫入鎖，並且您需要處理任何寫入衝突。除非那是你想要的:)

*注意：*與 RCSI 不同（一旦啟用，它適用於在讀取送出時執行的所有事務），必須使用 . 顯式請求 SI SET TRANSACTION ISOLATION SNAPSHOT;。

依賴於讀取器阻塞寫入器（包括在觸發程式碼中！）的微妙行為使測試變得必不可少。有關詳細資訊，請參閱我的連結文章系列和聯機叢書。如果您決定使用 RCSI，請務必特別查看 Read Committed Snapshot Isolation 下的 Data Modifications。

最後，您應該確保您的實例已修補到 SQL Server 2008 Service Pack 4。

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