INSERT 上的表鎖定 - 如何優化

我們在數據庫中有一些長時間執行的過程，這會導致我們的主文本儲存表上的即時表鎖定，這會導致****Web 應用程序的所有其他請求等待（因為幾乎每個請求都使用該表）。

設置

1. 帶有 ASP.NET、實體框架、SQL Server 2008R2 的 Web 應用程序
2. 具有數據屬性的大量表（表每類策略）
3. 動態定義文本的一個表（“dbo.Translations”）（以啟用翻譯）。
4. 此表沒有外鍵

為了以正確的語言獲取對象的文本，對象 ID 與文本值一起保存到翻譯表中

表定義

所以翻譯表看起來像：

dbo.Translations (ID varchar(36), type varchar(10), lang varchar(5), value varchar(max))

而類型是為每個對象定義的，而語言就像’en-GB’（不相關）。

指數

該表有兩個索引： ID 上的 PK_Translations（集群，非唯一）所有列上的 UNQ_Translations（非集群，唯一）

問題

該表必須處理兩個操作：SELECTS 和 INSERTS。

更新和刪除不是常見的案例。

該表包含近 1.000.000 行和一個基於 ID、type、lang 的索引。

當 Web 應用程序要插入（多條）記錄時，該操作會花費大量時間並創建即時表鎖。這會停止對該表的所有其他請求（甚至可能對其他表，所以有時它似乎是數據庫鎖？！）並導致它們等待。

插入物

大數據的INSERTS是通過以下方式完成的：

INSERT INTO Translations
  (Id, Typ, lang, value)
SELECT
  td.nId,
  td.Typ,
  td.lang,
  td.value
  FROM #tempDictionary td

注意：#tempDictionairy 是一個臨時表

腳步

以下步驟經過測試並沒有改善情況：

1. 使用未送出的讀取啟動 SELECT 事務 -> 無更改
2. 將索引稀疏度擴展至 50% 以確保插入的記錄可以輕鬆保存 -> 無變化

如何找出 SQL Server 創建表/數據庫鎖的原因？

1. 您是否 100% 確定此過程正在升級為一個小塊？您可以使用 Adam Machanic 的sp_whoisactive. 在程序執行（並且可能是阻塞）時拍攝快照（執行過程）。收集鎖和查詢計劃 - 這將為您提供解決此問題所需的資訊。請注意插入中等待的 spid 正在等待什麼……如果它們正在等待閂鎖 - 我們有一個頁面拆分情況鎖定它是一個阻塞/鎖定問題
2. 請記住，當單個語句在表或索引上獲得 5K 鎖時，就會發生鎖升級。（http://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx）我發現使用 paglock 提示（這樣你可以使用更少的粗鎖）來避免 tablock升級。
3. 看一下在步驟 1 中收集的查詢計劃。正在插入的行的基數估計是多少（即 select 語句）……它是否比 5K 大得多？如果是，您可能想要編寫一個循環 - 這樣您一次只能插入 2500 行…

1. 看起來您還插入了主鍵（即聚集索引）：

插入翻譯（Id、Typ、lang、value）

這意味著整個表正在重組 - SQL Server必鬚根據您插入的新 Id**物理重新排列數據。**它可以鎖定幾個頁面。

但是，很高興看到您的 select 語句。我確定您是按交易 ID 選擇的。

解決方案：添加另一個標識列 - 整數 - 增加並使其成為主鍵（聚集索引）。將 TransactionID 設為非聚集索引。因此，插入新的 TransactionID 不會阻止讀取舊的 TransactionID（或者會以較小的效果阻塞，您可以使用快照隔離級別來否定）

不要使用未送出的讀取，因為它可能導致臟讀/幻讀和不可重複讀取問題。

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