[SQL SERVER][Performance]淺談鎖定擴大
什麼是鎖定擴大?
鎖定擴大就是把低階層鎖定升級到高階層鎖定的過程，
這樣可以減少系統負擔(記憶體量變少)，
但可能會增加並行爭用的可能性(Blocking變多)。
觸發條件
(擷取BOL)
效能考量
SQL Server每一個鎖定都必須要耗費記憶體，所以鎖定的數量是和記憶體用量成正比，
為了解決這樣的問題，SQL2005新增鎖定擴大的機制，只要達到任一上述觸發條件，
馬上將鎖定擴大升級，這樣一來可以釋放先前低階層鎖定(row lock)記憶體用量，
鎖定擴大(table lock)後記憶體用量也相對減少，但鎖定擴大也帶來並行爭用的可能性，
即是封鎖(Blocking)變多了(你絕對不希望使用者查詢資料表時...一直....等等等)，
如果鎖定擴大的次數太頻繁，無疑也將對效能造成一定的影響，
這時就要考慮減少鎖定擴大次數，下面我將透過範例來實際了解鎖定擴大過程。
connection1執行以下TSQL
begin tran
update top(4000) t1
set c2=c2+'x'
查詢鎖定類型和物件
select resource_type,resource_associated_entity_id,request_mode,
request_type,request_status
from sys.dm_tran_locks
where resource_database_id = 7
and request_session_id=54
可以看到產生Key Lock(row level) 數量相當多 ，
這時使用者可以查詢未鎖定的資料列，
接下來先 rollback 後並將數量改成5000。
begin tran
update top(5000) t1
set c2=c2+'x'
由於觸發了鎖定擴大條件，所以鎖定層級由 Key(row level) 變成 Object (table level)，
並釋放之前低階層鎖定記憶體資源，但由於鎖定擴大到資料表，
這時使用者必須等待該交易完成後才可查詢該資料表。
如何減少鎖定擴大
1.設定追蹤旗標(trace flag)(不建議)
1211
(擷取BOL)
1224
(擷取BOL)
2. 修改資料表 LOCK_ESCALATION = DISABLE(SQL2008之後版本才支援)
(擷取BOL)
alter table t1 set (LOCK_ESCALATION=disable)
3.使用資料列版本控制
(擷取BOL)
ps:為了要避開封鎖問題，所以查詢可能會加上with(nolock)，但with(nolock)還請小心服用。
參考