【SQL SERVER】锁机制

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

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

数据库引擎隔离级别

锁粒度

锁类型

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() 

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

3. 查询数据

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

4. 更新一条数据

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

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

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

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

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

6. 释放锁

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

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

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

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

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

5. 删除一条数据

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

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

死锁产生的原因

微软文档是这样说

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

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

例如

1. 事务A 获取 Row1 资源

2. 事务B 获取 Row2 资源

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

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

SQL Server处理死锁策略

1. 定期检查陷入死锁的任务

2. 若检查到循环依赖

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

模拟死锁

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

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捕获到死锁

可以看出

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

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

3. 形成了循环依赖

我们这里的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】数据内部存储结构简单探索

避免死锁

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

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

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

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

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

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

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

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

参考文章

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