如何解決封鎖問題所造成的 SQL Server 中的鎖定擴大

文章翻譯 文章翻譯
文章編號: 323630 - 檢視此文章適用的產品。
全部展開 | 全部摺疊

在此頁中

結論

鎖定擴大規模是將許多精細的鎖定 (例如資料列或分頁鎖定) 轉換成資料表鎖定的程序。Microsoft SQL Server 動態地決定何時執行鎖定擴大規模。在進行這項決定時 SQL Server 會考慮特定掃描整筆交易與中所使用的鎖定系統整體的記憶體所持有的鎖定數目所保存的鎖定數目。 通常,SQL Server 的預設行為會導致發生只能在那些點,它會在此改善效能,或當您必須減少過度的系統鎖定記憶體至更合理的層級的鎖定擴大規模。不過,部份應用程式或查詢的設計可能會觸發一次時不令人滿意,且緊急的資料表鎖定可能會封鎖其他使用者鎖定擴大規模。本文將告訴您如何判斷是否要在封鎖,以及如何應付不希望發生鎖定擴大規模,造成鎖定擴大規模。

其他相關資訊

如何判斷是否鎖定擴大規模會造成封鎖

鎖定擴大規模並不會導致大多數封鎖問題。若要判斷是否發生鎖定擴大規模時您遭遇封鎖問題的時間前後,啟動 SQL Profiler 追蹤,其中包含 鎖定: 擴大 事件。如果您看不到任何的 鎖定: 擴大 事件不在您的伺服器上發生鎖定擴大規模本文資訊並不會套用到您的情況。

如果發生鎖定擴大規模請確認緊急的資料表鎖定封鎖其他使用者。

如更多有關如何識別頭封鎖程式,以及如何識別頭封鎖了其他伺服器處理序識別碼 (SPID) 的封鎖程式所持有的鎖定資源按一下下面的文件編號,檢視 「 Microsoft 知識庫 」 中的發行項:
224453瞭解並解決 SQL Server 7.0 或 2000年封鎖問題
如果封鎖其他使用者鎖定 TAB (資料表層級) 鎖定與鎖定模式的 S (共用) 或 X (獨占) 以外的其他,鎖定擴大規模不問題。在特別如果 TAB 鎖定意圖鎖定 (例如 IS、 IU,或 IX 的鎖定模式) 這不是結果的鎖定擴大規模。如果您封鎖的問題不由鎖定擴大規模所造成,請參閱文件 Q224453 疑難排解步驟。

如何避免鎖定擴大

避免鎖定擴大規模最簡單且最安全的方法是讓交易短,並減少鎖定足跡的昂貴的查詢,以便不超過鎖定擴大規模臨界值。有數種方法來取得這個目標其中有許多所列:
  • 分割成幾個較小的作業的大型的批次作業。例如,假設您執行下列查詢來移除的稽核] 表格中的幾個數百一千舊的記錄,然後您會發現它造成封鎖其他使用者的鎖定擴大規模:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'						
    依一次移除這些記錄幾個 hundred,您可以大幅減少累積每個交易,並避免鎖定擴大規模的鎖定數目。例如:
    SET ROWCOUNT 500
    delete_more:
         DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    IF @@ROWCOUNT > 0 GOTO delete_more
    SET ROWCOUNT 0
  • 藉由進行查詢有效率,盡可能降低查詢的鎖定足跡。大型掃描或大量的書籤尋查可能會增加鎖定擴大規模的機會 ; 此外,增加死結,機會,並通常有不利影響並行處理和效能。 尋找查詢之後,會導致鎖定擴大規模、 外觀的機會來建立新的索引,或將欄新增到現有的索引,移除索引或資料表掃描,並充分發揮效率索引的搜尋。請考慮將查詢貼到在其上執行自動索引分析的查詢分析查詢視窗。若要這麼做,在 [查詢] 功能表上按一下 [索引微調精靈 」 在 SQL Server 2000 中,或按一下 SQL Server 7.0 中 執行索引分析

    這項最佳化的其中一個目標,是讓索引搜尋傳回較少的資料列盡可能降至最低 (最大化特定查詢的索引的選擇性) 的書籤尋查的成本。如果 SQL Server 估計書籤查閱邏輯運算子可能傳回許多資料列,它可能會使用一個預先擷取執行書籤查閱。如果 SQL Server 不會使用預先擷取的書籤查閱,它必須增加查詢,以查詢的一部份的可重複讀取部分的交易隔離等級。這表示什麼看起來可能像讀取認可的隔離層級的 SELECT 陳述式可能會取得數千 (在叢集的索引和一個非叢集的索引),這樣會造成這類查詢以超過鎖定擴大規模臨界值的索引鍵鎖定。這點特別重要,如果您發現緊急的鎖定是共用的資料表鎖定這不過,不會經常出現預設讀取認可的隔離等級。如果預先擷取的書籤查閱子句會造成重大問題,請考慮加入其他的資料行到查詢計劃中會出現在搜尋索引或索引掃描邏輯運算子下方書籤查閱邏輯運算子的非叢集索引。可能有可能建立一個涵蓋索引 (包含查詢中使用的資料表中的所有資料行的索引,),或是至少的索引,涵蓋了適用於聯結篩選條件或 WHERE 子句中如果使用包括選取的資料行清單中的所有項目資料行並不實用。

    巢狀迴圈聯結可能也會使用預先擷取,及這會造成相同的鎖定行為。

    如需詳細資訊,請按一下下列的文件編號,檢視 「 Microsoft 知識庫 」 中的文件:
    260652巢狀的迴圈聯結會使用一個"書籤 LOOKUP...WITH 預先擷取 」 可能會發生鎖定較長的時間
  • 如果不同的 SPID 目前保留的不相容的資料表鎖定,不能發生鎖定擴大規模。鎖定擴大規模永遠 escalates 資料表鎖定,並不會分頁鎖定。此外,如果鎖定擴大規模嘗試失敗,因為另一個 SPID 會保留不相容的 TAB 鎖定,嘗試擴大規模的查詢並不會封鎖等候 TAB 鎖定時。而是,取得 (資料列、 索引鍵或頁面) 其原始、 更細微層級的鎖定會繼續,定期進行額外的擴大嘗試。因此,防止特定資料表的鎖定擴大規模的方法之一,是取得,並按住不同的連線不緊急的鎖定類型與相容的鎖定。在資料表層級的 IX (意圖獨占) 鎖定不會鎖定任何資料列] 或 [網頁,但具有一個緊急 S (共用) 或 X (獨占) TAB 鎖定是仍然不相容。比方說假設您必須執行發生的原因,是因為鎖定擴大一個批次工作,修改大量 mytable 表格中的資料列,並且已造成封鎖。如果少於一小時永遠完成這項作業,您可能會建立包含下列程式碼的 Transact-SQL 工作,但排定新工作啟動批次工作開始時間之前的幾分鐘:
    BEGIN TRAN
    SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
    WAITFOR DELAY '1:00:00'
    COMMIT TRAN				
    這個查詢取得及持有 mytable IX 鎖定可防止在這段時間的鎖定擴大資料表上的一個小時。此批次不會修改任何資料或封鎖其他查詢 (除非其他查詢會強制使用 TABLOCK 提示的資料表鎖定,或如果系統管理員有頁面或資料列鎖定使用停用 sp_indexoption 預存程序)。
此外,您可以藉由啟用追蹤旗標 1211年停用鎖定擴大規模。但是,此追蹤旗標會停用全域在 SQL Server 的執行個體中的所有鎖定擴大規模。鎖定擴大規模最大化否則會減速由取得和釋放幾千個鎖定的額外負荷的查詢的效率,SQL Server 中做很有用的用途。鎖定擴大規模也有助於減少所需的記憶體來追蹤的鎖定。 SQL Server 可以動態配置的鎖定結構的記憶體是有限的因此如果您停用鎖定擴大規模和鎖定記憶體成長不夠大、 嘗試為任何查詢配置其他鎖定可能會失敗並發生下列錯誤:

錯誤: 1204 嚴重性: 19 狀態: 1
SQL Server 無法取得鎖定資源,這一次。使用中的使用者人數較少時重新執行您的陳述式,或洽詢系統管理員檢查 SQL Server 的鎖定與記憶體組態。
附註一個 1204"] 錯誤時停止目前的陳述式的處理並會造成使用中交易的復原。復原本身可能會封鎖使用者,或如果您重新啟動 SQL Server 服務導致長資料庫復原時間。

使用鎖定提示,例如 ROWLOCK 只改變初始鎖定計劃。鎖定提示不會防止鎖定擴大規模。

其他的方法防止鎖定擴大規模本文稍早所討論的是比啟用追蹤旗標的更好選項。除此之外,其他方法通常造成查詢比停用整個執行個體的鎖定擴大規模的較佳效能。Microsoft 建議啟用因其他選項時的鎖定擴大此追蹤旗標僅以降低嚴重封鎖,例如那些討論稍早本文章中會被調查。啟用追蹤旗標,讓它已開啟時已啟動 SQL Server,將它加入做為伺服器啟動參數。

若要將伺服器啟動參數 SQL 企業管理員在伺服器上按一下滑鼠右鍵,並按一下 [內容,然後在 [一般] 索引標籤上按一下 [啟動參數,然後新增下列參數 (完全符合所示):
-T1211
您必須循環 SQL Server 服務,為新的啟動參數,才會生效。如果您在 Query Analyzer 中執行下列查詢追蹤旗標會立即生效:
DBCC TRACEON (1211, -1)				
但是,如果您未新增 -T1211 當啟動參數 traceon 命令的效果時,會遺失更新 SQL Server 服務。開啟追蹤旗標可防止任何未來鎖定] 擴大,但它不會反轉已經在使用中的交易中發生任何鎖定擴大。

屬性

文章編號: 323630 - 上次校閱: 2007年2月22日 - 版次: 10.3
這篇文章中的資訊適用於:
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
關鍵字:?
kbmt kbinfo KB323630 KbMtzh
機器翻譯
重要:本文是以 Microsoft 機器翻譯軟體翻譯而成,而非使用人工翻譯而成。Microsoft 同時提供使用者人工翻譯及機器翻譯兩個版本的文章,讓使用者可以依其使用語言使用知識庫中的所有文章。但是,機器翻譯的文章可能不盡完美。這些文章中也可能出現拼字、語意或文法上的錯誤,就像外國人在使用本國語言時可能發生的錯誤。Microsoft 不為內容的翻譯錯誤或客戶對該內容的使用所產生的任何錯誤或損害負責。Microsoft也同時將不斷地就機器翻譯軟體進行更新。
按一下這裡查看此文章的英文版本:323630
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應,不負任何擔保責任。Microsoft及(或)其供應商謹此聲明,不負任何對與此資訊有關之擔保責任,包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。

提供意見

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com