鎖定擴大 (Database Engine)

發行項
12/15/2008

鎖定擴大是將許多的細緻鎖定轉換成較少的粗略鎖定之過程，可減少系統的負擔，但會增加並行爭用的可能性。

當 Microsoft SQL Server Database Engine 取得低層級鎖定時，它也會對含有低層級物件的物件進行意圖鎖定：

當鎖定資料列或索引鍵範圍時，Database Engine 會對含有資料列或索引鍵的頁面進行意圖鎖定。
當鎖定頁面時，Database Engine 會對含有頁面的高層級物件進行意圖鎖定。除了對資料表進行意圖物件 (OBJECT) 鎖定外，還會對下列物件要求意圖堆積或 B 型樹狀目錄 (HOBT) 鎖定：
- 非叢集索引 (若頁面為非叢集索引頁面)。
- 叢集索引 (若頁面為叢集索引頁面)。這包括資料表中含有叢集索引的資料頁。
- 資料頁的堆積 (若頁面為資料表中不具叢集索引的資料頁)。

Database Engine 可能會在相同的陳述式中同時進行資料列與頁面鎖定，以便盡可能減少鎖定數目，並降低必須使用鎖定擴大的可能性。例如，Database Engine 可能會對非叢集索引進行頁面鎖定 (若索引節點中已選取足夠的連續索引鍵來滿足查詢)，並對資料進行資料列鎖定。

為了擴大鎖定，Database Engine 會試圖將資料表的意圖鎖定變更為對應的完全鎖定，例如，將意圖獨佔 (IX) 鎖定變為獨佔 (X) 鎖定，或將意圖共用 (IS) 鎖定變為共用 (S) 鎖定。若鎖定擴大的嘗試成功而取得資料表的完全鎖定，則所有由堆積或索引上的交易所保有的堆積或 B 型樹狀目錄、頁面 (PAGE)、索引鍵範圍 (KEY) 或資料列層級 (RID) 鎖定，都會被釋放。若無法取得完全鎖定，則當時不會發生任何鎖定擴大，但 Database Engine 會繼續試圖取得資料列、索引鍵或頁面的鎖定。

Database Engine 不會將資料列或索引鍵範圍鎖定擴大為頁面鎖定，但會直接將它們擴大為資料表鎖定。同樣地，頁面鎖定一律會擴大為資料表鎖定。

若鎖定擴大的嘗試因並行交易所保有的鎖定衝突而失敗，Database Engine 會在交易每多取得 1,250 個鎖定時重新嘗試鎖定擴大。

每個擴大事件主要會在單一 Transact-SQL 陳述式的層級上運作。當事件開始時，Database Engine 會試圖針對使用中陳述式所參考之任何資料表中的現行交易，擴大其所擁有的所有鎖定，前提是該陳述式符合擴大臨界值需求。若擴大事件在陳述式存取資料表前即已開始，則不會嘗試擴大該資料表的鎖定。若鎖定擴大成功，則由先前陳述式中的交易取得且在事件開始時仍保有的所有鎖定，都將會擴大，只要現行陳述式參考了該資料表，且該資料表包含在擴大事件中即可。

舉例而言，假設某個工作階段執行了下列作業：

開始交易。
更新 TableA。此作業會在 TableA 中產生獨佔資料列鎖定，並保有鎖定到交易完成為止。
更新 TableB。此作業會在 TableB 中產生獨佔資料列鎖定，並保有鎖定到交易完成為止。
執行 SELECT 以聯結 TableA 與 TableC。查詢執行計劃要求先從 TableA 擷取資料列，再從 TableC 擷取資料列。
SELECT 陳述式會在它從 TableA 擷取資料列之際且在其存取 TableC 之前，觸發鎖定擴大。

若鎖定擴大成功，則只有 TableA 上的工作階段所保有的鎖定會擴大。其中包括 SELECT 陳述式的共用鎖定與先前 UPDATE 陳述式的獨佔鎖定。雖然只有工作階段在 TableA 中針對 SELECT 陳述式所取得的鎖定是決定應否執行鎖定擴大的考量依據，但一旦擴大成功，工作階段在 TableA 中所保有的所有鎖定都會擴大為資料表的獨佔鎖定，而 TableA 上所有其他較低資料粒度的鎖定 (包括意圖鎖定) 都會被釋放。

對 TableB 則不會有任何擴大鎖定的嘗試，因為在 SELECT 陳述式中並沒有對 TableB 的使用中參考。同樣地，對 TableC 也不會有任何擴大鎖定的嘗試，因為它在發生擴大時尚未被存取。

鎖定擴大臨界值

鎖定擴大會在下列情況下觸發：

當單一 Transact-SQL 陳述式對單一資料表或索引取得至少 5,000 個鎖定時。
當 Database Engine 執行個體中的鎖定數目超過記憶體或組態臨界值時。

如果因為鎖定衝突而無法擴大鎖定，Database Engine 會在每取得 1,250 個新鎖定時定期地觸發鎖定擴大。

Transact-SQL 陳述式的擴大臨界值

當 Transact-SQL 陳述式對資料表或索引的單一參考，或 (若資料表已資料分割) 資料表資料分割或索引資料分割的單一參考取得至少 5,000 個鎖定時，即觸發鎖定擴大。例如，若陳述式對單一索引取得 3,000 個鎖定，並且對相同資料表中的另一個索引也取得 3,000 個鎖定，則不會觸發鎖定擴大。同樣地，若陳述式具有資料表的自我聯結，而且資料表的每個參考只取得 3,000 個資料表的鎖定，則不會觸發鎖定擴大。

只有在擴大觸發時被存取的資料表，才會發生鎖定擴大。假設有個單一 SELECT 陳述式是一個依下列順序存取三個資料表的聯結：TableA、TableB 與 TableC。此陳述式對 TableA 的叢集索引取得了 3,000 個資料列鎖定，對 TableB 的叢集索引取得了至少 5,000 個資料列鎖定，但尚未存取 TableC。當 Database Engine 偵測到此陳述式已取得 TableB 中至少 5,000 個資料列鎖定時，即會試圖擴大 TableB 中目前交易所保留的所有鎖定。它也會試圖擴大 TableA 中目前交易所保留的所有鎖定，但由於 TableA 的鎖定數目 < 5000，因此擴大將不會成功。對 TableC 則不會嘗試進行鎖定擴大，因為在擴大發生時尚未存取 TableC。

Database Engine 執行個體的擴大臨界值

每當鎖定數目大於鎖定擴大的記憶體臨界值時，Database Engine 即觸發鎖定擴大。記憶體臨界值取決於 locks 組態選項的設定：

若 locks 選項設為預設值 0，則在鎖定物件所用記憶體為 Database Engine 所用記憶體的 24% 時 (AWE 記憶體除外)，就會達到鎖定擴大臨界值。用來表示鎖定的資料結構大約為 100 位元組。這屬於動態臨界值，因為 Database Engine 會動態地取得及釋放記憶體，以便針對隨時變動的工作負載進行調整。
若 locks 選項設為 0 以外的值，則鎖定擴大臨界值為鎖定選項值的 40% (若記憶體不足則較低)。

Database Engine 可從任何工作階段中選擇任何使用中陳述式來進行擴大，而且每產生 1,250 個新鎖定，它就會選擇陳述式進行擴大，只要執行個體中所用的鎖定記憶體保持在臨界值以上即可。

擴大混合鎖定類型

發生鎖定擴大時，針對堆積或索引而選取的鎖定足以符合最嚴格的低層級鎖定需求。

例如，假設有某個工作階段：

開始交易。
更新含有叢集索引的資料表。
發出參考相同資料表的 SELECT 陳述式。

UPDATE 陳述式會取得下列鎖定：

對於更新後資料列的獨佔 (X) 鎖定。
對於含有這些資料列之叢集索引頁面的意圖獨佔 (IX) 鎖定。
對於叢集索引的 IX 鎖定以及對資料表的其他 IX 鎖定。

SELECT 陳述式會取得下列鎖定：

它所讀取之所有資料列的共用 (S) 鎖定，已由 UPDATE 陳述式的 X 鎖定所保護的資料列則除外。
含有這些資料列之所有叢集索引頁面的意圖共用鎖定，已由 IX 鎖定所保護的頁面則除外。
不會取得叢集索引或資料表的鎖定，因為它們已由 IX 鎖定所保護。

若 SELECT 陳述式取得足夠的鎖定可觸發鎖定擴大，且擴大成功的話，則資料表的 IX 鎖定會轉換為 X 鎖定，且會釋放所有資料列、頁面與索引鎖定。資料表的更新與讀取都會受到 X 鎖定所保護。

減少鎖定與擴大

在大多數的情況下，Database Engine 以其鎖定與鎖定擴大的預設值來運作時會展現最佳效能。若 Database Engine 執行個體產生了大量鎖定，而且鎖定擴大頻繁的情況下，請考慮減少鎖定的數量，方法如下：

使用不會對讀取作業產生共用鎖定的隔離等級。
- 在 READ_COMMITTED_SNAPSHOT 資料庫選項為 ON 時，使用 READ COMMITTED 隔離等級。
- SNAPSHOT 隔離等級。
- READ UNCOMMITTED 隔離等級。只有能夠採取中途讀取的系統才可使用此等級。
使用 PAGLOCK 或 TABLOCK 資料表提示，讓 Database Engine 使用頁面、堆積或索引鎖定，而非資料列鎖定。然而，使用這個選項會增加使用者阻止其他使用者嘗試存取相同資料的問題，因此只應在具有較多並行使用者的系統上使用。

您也可以使用追蹤旗標 1211 與 1224 來停用所有或部分的鎖定擴大。如需詳細資訊，請參閱＜追蹤旗標 (Transact-SQL)＞。此外，請使用 SQL Server Profiler Lock:Escalation 事件來監視鎖定擴大，相關資訊請參閱＜使用 SQL Server Profiler＞。

請參閱

共用方式為