大量 INSERT 阻塞 SELECT
我遇到了大量阻止我的 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: 1
InsertOrUpdateInverterData 上的命令值得研究。儘管該命令沒有執行很長時間,但您應該檢查您沒有不必要的包含事務(在應用程序或更高級別的儲存過程中)。最佳實踐是盡可能縮短事務。這可能沒什麼,但您絕對應該檢查一下。潛在的解決方案
正如 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。