【SQL SERVER】锁机制

SQL Server中锁机制保证并发情况下的数据访问，开发过程中利用好索引减少数据，能减少数据扫描数据加锁的过程，合理规范使用事务，能减少死锁发生

锁定是 sql Server 数据库引擎用来同步多个用户同时对同一个数据块的访问的一种机制。

1. 基本概念
2. 利用SQL Server Profiler观察锁
3. 死锁产生的原因及避免
4. 总结

基本概念

数据库引擎隔离级别

隔离级别	定义
未提交的读取	隔离事务的最低级别，只能保证不读取物理上损坏的数据。 在此级别上，允许脏读，因此一个事务可能看见其他事务所做的尚未提交的更改
已提交的读取	允许事务读取另一个事务以前读取（未修改）的数据，而不必等待第一个事务完成。 SQL Server 数据库引擎保留写锁（在所选数据上获取）直到事务结束，但是一执行 SELECT 操作就释放读锁。 这是SQL Server 数据库引擎默认级别
可重复的读取	SQL Server 数据库引擎保留在所选数据上获取的读锁和写锁，直到事务结束。 但是，因为不管理范围锁，可能发生虚拟读取
可序列化	隔离事务的最高级别，事务之间完全隔离。 SQL Server 数据库引擎保留在所选数据上获取的读锁和写锁，在事务结束时释放它们。 SELECT 操作使用分范围的 WHERE 子句时获取范围锁，主要为了避免虚拟读取

 

锁粒度

资源	说明
RID	用于锁定堆中的单个行的行标识符，也就是常说的行锁
KEY	索引中用于保护可序列化事务中的键范围的行锁
PAGE	数据库中的 8 KB 页，例如数据页或索引页，也就常说的业级锁
EXTENT	一组连续的八页，例如数据页或索引页
HoBT	堆或 B 树。 用于保护没有聚集索引的表中的 B 树（索引）或堆数据页的锁
TABLE	包括所有数据和索引的整个表
FILE	数据库文件
APPLICATioN	应用程序专用的资源
METADATA	元数据锁
ALLOCATION_UNIT	分配单元
DATABASE	整个数据库

 

锁类型

锁	说明
共享 (S)	用于不更改或不更新数据的读取操作，如 SELECT 语句
更新 (U)	用于可更新的资源中。 防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁
排他 (X)	用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。 确保不会同时对同一资源进行多重更新
意向 (I)	用于建立锁的层次结构。 意向锁包含三种类型：意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)
架构 (Sch-)	在执行依赖于表架构的操作时使用。 架构锁包含两种类型：架构修改 (Sch-M) 和架构稳定性 (Sch-S)
大容量更新 (BU)	在将数据大容量复制到表中且指定了 TABLOCK 提示时使用
键范围 (Range)	当使用可序列化事务隔离级别时保护查询读取的行的范围。 确保再次运行查询时其他事务无法插入符合可序列化事务的查询的行

 

利用SQL Server Profiler观察锁

1. 准备数据10条数据

create table DataTable(Id int identity(1,1), [Name] varchar(50), [Address] varchar(200), CreateTime datetime2)
    
insert into DataTable
select "Wilson","广东省广州市",GETDATE() uNIOn all
select "Alice","北京市朝阳区",GETDATE() union all
select "Miksovsky","吉林省松原市",GETDATE() union all
select "Hines","甘肃省兰州市",GETDATE() union all
select "Kane","辽宁省沈阳市",GETDATE() union all
select "Gode","湖北省荆州市",GETDATE() union all
select "Chen","湖南省岳阳市",GETDATE() union all
select "Trenary","福建省厦门市",GETDATE() union all
select "Achong","广西省玉林市",GETDATE() union all
select "Nixon","江西省景德镇",GETDATE() 

View Code

2. 打开SQL Server Profiler选中锁事件，勾选type和mode，建议取消不需要观察的列，然后用列筛选器过滤要观察的DB

3. 查询数据

 可以看到在页面级别加上意向共享锁，因为我们数据只有一页

4. 更新一条数据

表上加上意向排它锁（IX），可以用select OBJECT_NAME(581577110) 查看objectid代表的东西

页级别加上意向更新锁（IU），告诉SQL Server引擎这里有更新锁

获取第一行的更新锁（U），这里条件匹配

页级别升级为意向排他锁（IX）, 告诉SQL Server引擎这里有排他锁

第一个行更新锁 升级为排它锁（X）

释放锁

随条扫描后面的记录，只是条件不符合，也就不会升级锁级别

 

可以看到是全表扫描，因为没聚集索引（堆表），我们也没做一个主键，下面将Id添加主键然后再更新试试

alter table DataTable add constraint PK_DataTable primary key(Id asc)

 

 

 可以看出，直接在表，页级别加上意向排它锁（IX），然后在键上加上排它锁（X）

因为这里我们用主键更新，而且SQL Server主键默认是聚集索引，如果指定是非聚集索引主键，这里也会经历更新锁 到 排他锁，有兴趣的可以自行验证

5. 删除一条数据

 

 

 这次我们没用主键删除，过程和更新的第一种情况差不多，就不列了。

因为加了聚集索引，索引定位器执行聚集索引Key的hash，要验证是否那条记录，可以在删除前加上%%lockres%%去查

 

死锁产生的原因及避免

死锁产生的原因

微软文档是这样说

在两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永久阻塞，从而出现死锁

我理解就是有2个事务循环依赖对方的资源导致产生死锁。

例如

事务A 获取 Row1 资源

事务B 获取 Row2 资源

事务A获取Row2资源，由于这时Row2是被事务B占有，所以必须等事务B完成

事务B获取Row1资源，由于这时Row1是被事务A占有，所以必须等事务A完成

SQL Server处理死锁策略

定期检查陷入死锁的任务

若检查到循环依赖

选择其中一个作为牺牲品，然后终止事务，然另外一个得以完成

模拟死锁

分别在两个不同的会话执行下面语句

begin tran;

update DataTable set Address = "上海市" where Id = 2;
--延迟5秒执行
WAITFOR DELAY "00:00:05";
update DataTable set Address = "上海市" where Id = 3;

commit;

begin tran;

update DataTable set Address = "上海市" where Id = 3;
--延迟5秒执行
WAITFOR DELAY "00:00:05";
update DataTable set Address = "上海市" where Id = 2;

commit;

执行一段时间，其中一个会出现下面错误

SQL Server Profiler 捕获死锁分析

打开Locks事件的死锁图形

 

 重新执行上面语句，模拟死锁，Profiler捕获到死锁

 

可以看出

进程56 请求的Key 的排它锁  被进程 54 占有

进程54 请求的Key 的排他锁 被进程 56 占有

形成了循环依赖

我们这里的Sql比较简单，而且没有用参数化执行，所以我们指定是哪一行被锁，线上的通常不能直接看到哪一行被锁

我们可以通过xml查看等待的资源，在xml里面有process-list 下面有多个process，process节点上面有个waitresource属性，这个指出每个进程等待的资源

锁类型：db_id : hobt_id : (hashvalue)

KEY: 6:72057594043760640 (61a06abd401c)

通过%%lockres%% 查到被锁资源

select %%lockres%%,* from DataTable where %%lockres%% = "(98ec012aa510)"

锁类型不一样，得到的会不一样，根据各自的格式用db_name / object_name  / dbcc去查到当前被锁的资源，有时候需要利用DBCC查询Page存储页面，可以参考上一篇文章【SQL SERVER】数据内部存储结构简单探索

避免死锁

首先需要说明死锁不能完全避免，但遵守特定的编码惯例可以将发生死锁的机会降至最低

 按同一顺序访问对象，一个获取锁，另外一个就必须等待

 避免事务中的用户交互 ，这样导致事务时间过长，容易造成死锁

 保持事务简短并处于一个批处理中，道理和2一样，尽量让事务运行时间短。

使用较低的隔离级别，这个看能不能接受脏读，幻读等副作用

总结

锁机制保证并发情况下的数据访问。

开发中应该尽量利用索引检索数据，特别是UPDATE/DELETE这种需要排它锁，应该利用唯一聚集索引字段更新（通常是主键）

规范使用事务能减少死锁发生

转发请标明出处：https://www.cnblogs.com/WilsonPan/p/12618849.html

参考文章

事务锁定和行版本控制指南 - SQL Server | Microsoft Docs