增删改查操作下锁的相关情况_查询时的锁分析

此系列主要分析在增删改查操作下，表是否有索引等情况下，锁的申请使用情况，研究分析并用于语句调优，数据库优化，死锁堵塞分析等；由于时间问题，该篇只分析查询的情况，下篇继续分析

锁的种类

首先，先了解下数据库关于锁的几种类型，如下：

1、 共享锁
用于只读操作(SELECT)，锁定共享的资源。共享锁不会阻止其他用户读，但是阻止其他的用户写和修改。

2、 更新锁
更新锁是一种意图锁，当一个事务已经请求共享琐后并试图请求一个独占锁的时候发生更新琐。例如当两个事务在几行数据行上都使用了共享锁，并同时试图获取独占锁以执行更新操作时，就发生了死锁：都在等待对方释放共享锁而实现独占锁。更新锁的目的是只让一个事务获得更新锁，防止这种情况的发生。

3、 独占锁
一次只能有一个独占锁用在一个资源上，并且阻止其他所有的锁包括共享缩。写是独占锁，可以有效的防止’脏读’。

4、 意图缩
在使用共享锁和独占锁之前，使用意图锁。从表的层次上查看意图锁，以判断事务能否获得共享锁和独占锁，提高了系统的性能，不需从页或者行上检查。

5、 计划锁
Sch-M,Sch-S。对数据库结构改变时用Sch-M，对查询进行编译时用Sch-S。这两种锁不会阻塞任何事务锁，包括独占锁。

另外还有相关锁的知识：

锁的粒度：
DB-----数据库，由于 dbid 列已包含数据库的数据库 ID，所以没有提供任何信息
FIL----文件
IDX----索引
PG-----页，数据或索引页。页码。页由 fileid:page 组合进行标识，其中，fileid 是 sysfiles 表中的 fileid，而 page 是该文件内的逻辑页码。
KEY----键,用于保护可串行事务中的键范围
TAB----表，包括所有数据和索引在内的整个表。由于 ObjId 列已包含表的对象 ID，所以没有提供任何信息
EXT----区域, 相邻的八个数据页或索引页构成的一组。正被锁定的扩展盘区中的第一个页码。页由 fileid:page 组合进行标识
RID----行，表内已锁定行的行标识符。行由 fileid:page:rid 组合进行标识，其中，rid 是页中的行标识符。

细分锁的模式：
0 Null 没有得到资源的访问权限
1 Sch-S (Schema stability) 对查询进行编译时。能防止加锁的对象被删除直到解锁
2 Sch-M (Schema Modification) 改变数据库结构时发生。能防止其他的事务访问加锁的对象
3 IS (Intent Shares) 意向共享锁。
4 SIU(Share Intent Update) 意图在维护资源的共享锁时，把更新锁放到锁层次结构的下层资源上
5 IS-S(Intent Share-shared) 复合键范围锁
6 IX(Intent Exclusive) 意向排他锁
7 SIX(Share Intent Exclusive) 允许并发 的IS 锁
8 S(Share) 共享锁
9 U(Update) 更新锁。防止死锁
10 Iin-Nul(Intent Insert-Null) 索引行层次的锁定，复合键范围锁
11 IS-X(Intent Share-Exclusive) 
12 IU(Intent Update) 意图更新锁
13 IS-U(Intent Share Update) 串行更新扫描
14 X(Exclusive) 排他锁
15 BU 块操作使用的锁

分析前的准备

首先建立4个表，分别为无索引、只有聚集索引、只有非聚集索引、有聚集和非聚集索引，每个表的话，都是一般的数据类型，以便操作，建表sql如下：

 1 create table t_noindex
 2 (
 3       id int not null  identity(1,1),
 4       nid int not null default 0,
 5       msg char(10) not null default ''
 6 )
 7 
 8 create table t_clu_index
 9 (
10       id int not null identity(1,1) primary key,
11       nid int not null default 0,
12       msg char(10) not null default ''
13 )
14 
15 create table t_noclu_index
16 (
17       id int not null identity(1,1),
18       nid int not null default 0,
19       msg char(10) not null default ''
20 )
21 
22 create nonclustered index noclu_t_noclu_index_id on t_noclu_index(nid)
23 
24 create table t_clu_noclu_index
25 (
26       id int not null identity(1,1) primary key,
27       nid int not null default 0,
28       msg char(10) not null default ''
29 )
30 
31 create nonclustered index noclu_t_clu_noclu_index_id on t_clu_noclu_index(nid)

View Code

查询锁情况的sql如下，只需改下表名就好了：

 1 use test;
 2 select 
 3 request_session_id,resource_type,request_status,request_mode,resource_description,
 4 OBJECT_NAME(p.object_id) as obj_name,p.index_id,p.object_id
 5 from 
 6 sys.dm_tran_locks 
 7 left join
 8 sys.partitions p
 9 on sys.dm_tran_locks .resource_associated_entity_id=p.hobt_id
10 where request_session_id in (
11 select 
12 request_session_id
13 from 
14 sys.dm_tran_locks 
15 left join
16 sys.partitions p
17 on sys.dm_tran_locks .resource_associated_entity_id=p.hobt_id
18 where OBJECT_NAME(p.object_id)='table'
19 )
20 order by 
21 request_session_id,resource_type

View Code

最后每个表都初始化100条数据把，此处就不贴sql了

分析-查询时锁的情况

 为了能够简单快捷的查看锁的情况，可按以下步骤处理

 1.设置事务的隔离级别为可重复读，确保数据在查询的时候获取共享锁直至事务结束

 2.在执行查询之前，开启事务

 3.执行查询语句但不提交事务

 4.在一个新的查询页上执行上面查看锁情况的sql，得到锁的详情

 5.将事务提交，避免堵塞

根据以上步骤，得到的锁情况如下：

1.无索引情况：

执行sql如下，注意先别提交事务：

1 use TEST;
2 set transaction isolation level repeatable read
3 go
4 set statistics  profile on
5 go
6 begin tran
7 select id,nid,msg from t_noindex where  id=1
8 
9 commit tran

View Code

得到锁的申请情况如下，数据顺序跟锁的申请顺序无关：

 锁的申请顺序说明，后续相同的就不多说：

（1）.首先在数据库申请了个共享锁，防止数据库被删除

（2）.然后就对表加了个意向共享锁，防止表的定义变更

（3）.接着找到那页数据了，在页面上申请了意向共享锁，防止页面被拆分

（4）.最后对那行数据申请共享锁，防止数据变更

2.只有聚集索引：

脚本：

1 use TEST;
2 set transaction isolation level repeatable read
3 go
4 set statistics  profile on
5 go
6 begin tran
7 select id,nid,msg from t_clu_index where  id=1
8 
9 commit tran

View Code

锁情况：

 

（1）.数据库申请共享锁

（2）.然后在表上申请了意向共享锁

（3）.接着在数据页上申请意向共享锁

（4）.最后在对应的索引键值上申请共享锁

3.只有非聚集索引

脚本：

1 use TEST;
2 set transaction isolation level repeatable read
3 go
4 set statistics  profile on
5 go
6 begin tran
7 select id,nid,msg from t_noclu_index where  nid=1
8 
9 commit tran

View Code

得到锁的情况：

这里得到的锁情况与无索引情况一致，由于我们上面执行的sql是会显示执行计划的，所以查看执行计划可知，这个sql并没有走索引而是全表扫描，经查阅相关

资料得知，

（1）.并不是查询条件是在索引上就会走索引查询，数据库是会根据查询的信息、条件以及相关统计信息，来决定执行计划；

（2）.在查询中，我们对返回的列在查询条件上若建立了非聚集索引，此时将可能尝试使用非聚集索引查找，如果返回的列没有创建非聚集索引，此时会返回到数据页中去获取这些列的数据，即使表中存在聚集索引或者没有，都会返回到表中或者聚集索引中去获取数据。对于以上场景描述，如果表没有创建聚集索引则称为Bookmar Lookup（标签查找），如果表中没有聚集索引但是存在非聚集索引我们称为RID Lookup（行ID查找），这将会提高查询的消耗

关于Bookmar Lookup、RID Lookup相关资料可点击查看Bookmar Lookup、RID Lookup有关资料

所以将所有查询的字段也放到索引上，那就会走索引查询了，修改索引如下：

1 drop index noclu_t_noclu_index_id on t_noclu_index ;
2 create nonclustered index noclu_t_noclu_index_id on t_noclu_index(id,nid,msg)

View Code

重新按照之前的步骤得到锁的情况如下：

这个是所有查询的列都在索引上，没发生Bookmar Lookup之类的情况，所以申请的锁大概跟聚集索引的情况差不多，下面就看看非聚集索引+RID Lookup的情况

我处理的方式是将数据增加至1000多，并增加一个无索引的字段，得到的情况如下：

从执行计划看到有Nested Loops，这个是统计信息更新问题导致的，可以不管先，然后是走了索引查询并发生RID Lookup

这时的锁的申请顺序：

（1）.数据库申请共享锁

（2）.在表上申请了意向共享锁

（3）.在数据页上申请意向共享锁

（4）.在对应的索引键值上申请共享锁

（5）.在数据所在行上申请共享锁

 4.聚集和非聚集索引共存的情况

根据上面的情况，这里就不细分出来，就看最复杂的情况，如下所示：

 

这个查询是属于非聚集索引+Bookmark Lookup或者Key Lookup这种情况，锁的申请情况为：

（1）.数据库申请共享锁

（2）.在表上申请了意向共享锁

（3）.在数据页以及非聚集索引页上申请意向共享锁

（4）.在所有对应的非聚集索引键值上申请共享锁

（5）.在数据所在的聚集索引键值上申请共享锁

总结

1.在查询时使用到的索引越多，查询的数据量越大，所需要的共享锁就越多

2.使用非聚集索引时，尽量查询的数据都在索引上，避免Bookmark Lookup等情况

3.事务中如无必要，请勿执行查询语句，尽量在事务外查询处理好数据

4.如果业务如无必要，可用nolock避免共享锁的申请

后面再继续对更新删除等情况进行分析，这里就先分析查询

转载于:https://www.cnblogs.com/9284chc0r0ij/p/11159259.html