官方文件
https://learn.microsoft.com/zh-cn/sql/relational-databases/memory-management-architecture-guide
https://learn.microsoft.com/zh-cn/troubleshoot/sql/performance/resolve-blocking-problems-caused-lock-escalation

Sqlserver的鎖資源佔用記憶體，比較昂貴，所以才會有鎖升級的概念。SQL SERVER 記憶體空間主要可分為：編譯記憶體、緩衝池Buffer Pool、查詢執行記憶體授予、鎖管理器記憶體、 CLR1 記憶體

鎖（由鎖管理器維護）：每個所有者 64 位元組 + 32 位元組

**鎖升級可能在以下條件之一下發生：**
    達到記憶體閾值 - 達到鎖記憶體的40%的記憶體閾值。 當鎖記憶體超過緩衝池的24%時，可以觸發鎖升級。 鎖記憶體限制為可見緩衝池的60%。 鎖升級閾值設定為鎖記憶體的40%。 這是緩衝池60%中的40%，即24%。 如果鎖記憶體超過60%的限制(如果禁用鎖升級) ，則所有分配其他鎖的嘗試都會失敗，並且1204會生成錯誤。
    達到鎖閾值 - 檢查記憶體閾值後，評估在當前表或索引上獲取的鎖數。 如果該數字超過5000，則會觸發鎖升級。

**通俗的例子介紹：**
假如一張表有1000萬資料，每行資料1000bytes，Sqlserver資料頁預設8KB大小，也就是一個資料頁小於等於8KB/1000B=8行記錄，如果對這張表執行一個select語句，資料庫+架構+表+頁+行上面都是共享鎖。如果對這張表執行一個delete 40000行的語句，資料庫+架構都是共享鎖，表+頁都是意向排他鎖，行是排他鎖，其中資料庫(1個)+架構(1個)共享鎖總計2個，表(1個)和頁(40000行/8行=5000個)意向排他鎖總計5001個，行排他鎖40000個，總計1+1+5001+40000=45003個鎖，每個鎖消耗64+32=96Bytes的記憶體，這個delete 40000行的操作就要消耗鎖記憶體45003*96/1024/1024=4.12MB的記憶體。當很多delete併發出現時，鎖記憶體會大大增加，所以Sqlserver會有鎖升級，一旦把行鎖上升到表鎖，就可以大大減少鎖記憶體的佔用，比如前面delete 30000行的例子，行鎖上升到表鎖後，總計3個鎖=資料庫(1個)共享鎖+架構(1個)共享鎖+表(1個)排他鎖，鎖記憶體變成288B，大大小於之前的4.12MB。

SQL Server鎖升級的粒度不是從行級到頁級再到表級，而是直接從行級到分割槽級或表級，禁用鎖升級並不是最好的選項，因為SQL Server的鎖管理器會消耗大量的記憶體
ALTER TABLE TABLENAME SET (LOCK_ESCALATION={AUTO|TABLE|DISABLE})
AUTO：藉助此選項，SQL Server 資料庫引擎 可選擇適合於表架構的鎖升級粒度。
    如果該表已分割槽，鎖升級到分割槽級別。 鎖升級到分割槽級別之後，該鎖以後將不會升級到TABLE粒度。
    如果表未分割槽，鎖升級到TABLE粒度。
TABLE：無論表是否已分割槽，鎖都升級到表級粒度。 預設值為TABLE。
DISABLE：在大多數情況下禁止鎖升級。 表級鎖並未完全被禁止。 例如，如果掃描的表在可序列化隔離級別下沒有聚集索引，資料庫引擎必須使用表鎖來保證資料完整性。

**可以使用trace來捕獲Lock:Escalation記錄，Lock:Escalation的事件號是60，捕獲實驗如下**
1、建立trace，語句如下
declare @TraceID int
declare @TraceFile nvarchar(500)
set @TraceFile=N'T:\tracelog\trace'
declare @maxfilesize bigint
set @maxfilesize=50000
exec sp_trace_Create @TraceID output,2,@TraceFile,@maxfilesize,null,10
2、新建的trace編號，select * from sys.traces可以查到編號為2
3、新增需要被捕獲的事件60到2號trace，語句如下
DECLARE @on BIT
SELECT @on = 1
exec sp_trace_setevent 2,60,1,@on
exec sp_trace_setevent 2,60,6,@on
exec sp_trace_setevent 2,60,8,@on
exec sp_trace_setevent 2,60,10,@on
exec sp_trace_setevent 2,60,11,@on
exec sp_trace_setevent 2,60,14,@on
exec sp_trace_setevent 2,60,26,@on
exec sp_trace_setevent 2,60,34,@on
exec sp_trace_setevent 2,60,35,@on
4、啟用2號trace，語句exec sp_trace_setstatus 2,1
5、一張表2300萬行，每行100B，表總量不大才2.3GB，，透過查詢捕獲資訊select * FROM fn_trace_gettable('L:\trace\trace.trc',default)，發現刪除1萬和10萬行沒有出現鎖升級，但是刪除100萬行(總量100MB，佔表的23分之1)時出現了鎖升級，所以官方文件中的這句"評估在當前表或索引上獲取的鎖數。 如果該數字超過5000，則會觸發鎖升級。"，** 不是指刪除5000行就會出現鎖升級**。**且在刪除100萬行的過程中執行select該表不加with(nolock)時，發現select語句被delete堵塞，select * from sys.sysprocesses where blocked<>0看到堵塞事件是LCK_M_IS；在刪除100萬行的過程中執行select該表加with(nolock)時，select語句不會被delete堵塞。**

Sqlserver鎖升級的理解和例子

相關文章