Sql-Server

大量 INSERT 阻塞 SELECT

  • October 20, 2017

我遇到了大量阻止我的 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