Sqlserver锁升级的理解和例子

官方文档
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堵塞。