禁用sqlserver的鎖定擴大

標籤：

鎖定擴大
與鎖的數量和記憶體壓力有關，達到閥值之後，直接升級到table lock，而不會小升級到page lock

減少鎖定擴大的方式有以下幾種：

• 禁用sqlserver執行個體的鎖定擴大
• 禁用特定表的鎖定擴大
• 加大閥值，減少鎖定擴大的可能（sp_configure ‘locks‘, 10000; RECONFIGURE;）
• 啟用資料列版本設定

 

禁用資料庫執行個體的鎖定擴大

在SQL Server 2005 中，可以使用追蹤旗標 1211、1224 來禁用整個執行個體中的鎖定擴大。

• 標誌1211-完全禁止鎖定擴大，但鎖使用的記憶體會被限制在動態分配記憶體的60%，當超過這個值時，更多的鎖將會伴隨著記憶體溢出錯誤而失敗。
• 標誌1224-禁止鎖定擴大，但記憶體使用量超過40%時，會自動開啟鎖定擴大
• 如果標誌1211和1224追蹤旗標同時被設定了，只有標誌1211會生效。

 

禁止特定表的鎖定擴大

在SQL Server 2008及更高版本，可以使用新的選擇來禁止某個表的鎖定擴大

ALTER TABLE SET (LOCK_ECALATION = AUTO | TABLE | DISABLE)

• TABLE: 直接從行鎖定擴大到表鎖(此為預設值)
• AUTO:  如果存在表分區，則升級為分區鎖，但不會進一步升級。
• DISABLE:禁用鎖定擴大，這並不意味著禁用表鎖（在序列化隔離等級的條件下進行表掃描等操作時還需要表鎖）

 

關於 rowlock

網上很多資料提到，語句中使用rowlock可以強制使用行鎖，但實際測試發現沒有效果。

 

查閱資料顯示：使用鎖定提示如 ROWLOCK 只改變初始鎖定計劃。鎖提示不能防止鎖定擴大。

Using a lock hint such as ROWLOCK only alters the initial lock plan.  Lock hints do not prevent lock escalation.

 

https://msdn.microsoft.com/zh-cn/library/ms184286.aspx

https://support.microsoft.com/zh-cn/kb/323630

禁用sqlserver的鎖定擴大