SQL Server 的各种查询和要申请的锁
前期准备：
1、建表
create table T_Btree(X int primary key,Y nvarchar(4000));
create table T_Heap(X int,Y nvarchar(4000));
go
2、插入数据
declare @i as int =1;
while @i<4
begin
insert into T_Btree(X,Y) values(@i,REPLICATE(cast(@i as nchar(1)),4000));
insert into T_Heap(X,Y) values(@i,REPLICATE(cast(@i as nchar),4000));
set @i = @i +1;
end
go
内容输出：
表格1、
select 'T_Btree'as table_name,X,Y from T_Btree;
go
表格2、
select 'T_Heap' as table_name ,X,Y from T_Heap;
go
连接的隔离级别设置为：
repeatable read。
set transaction isolation level repeatable read;
设置为这个级别的原因：
repeatable read。
会一直持有锁直到事务完成。
用于查看事务锁的SQL：
select lock.request_type,lock.resource_description,lock.resource_associated_entity_id,lock.request_mode,lock.request_status
from sys.dm_tran_locks as lock;
go
-------------------------------------------------------------------------------------------------------------------------------------------------------------
1、
对于 select:
A、聚集索引的情况：
begin transaction;
select 'T_Btree' as table_name ,X,Y from T_Btree;
go
锁申请情况：
锁申请情况分析：
1、database S：是为了防止在读取数据期间database被别的事务删除。
2、page IS: 是为了防止在读取数据期间page被别的事务拆分。
3、key S:是为了防止在读取数据期间key(index)被别的事务删除。
4、object IS:是为了防止在读取数据期间table定义被别的事务修改。
B、非聚集索引的情况：
begin transaction;
select 'T_Heap'as table_name,X,Y from T_Heap
with (index(ix_nuc_for_THeap_X));---这里是为了使用索引ix_nuc_for_THeap_X、这样就可以看使用它会加一些什么锁。
go
索引使用情况：
1、database S：是为了防止在读取数据期间database被别的事务删除。
2、page IS: 是为了防止在读取数据期间page被别的事务拆分。
3、key S: 是为了防止在读取数据期间key(index)被别的事务删除。
4、object IS: 是为了防止在读取数据期间table定义被别的事务修改。
5、RID S：是为了防止在读取数据期间RID被别的事务删除。
2、
对于 update
SQL server 先可把目标数据找到、然后在做修改。打目标数据要加 S 、然后加 U 、 最后升级到 X；
A聚集索引的情况：
begin transaction
update T_Btree
set Y = 'AAA';
go
1、Key X ：索引的叶子结点就是数据、要修改数据所以要加X锁。
B非聚集索引的情况：
begin transaction
update T_Heap
set Y = 'AAA';
go
1、RID X ：修改后行号马上就变了、所以要加 X 锁。
2、key U ：索引就要更新了 U 锁。
3、
delete:
A聚集索引的情况：
B聚集索引的情况：
4、
insert、
A聚集索引的情况：
begin tran;
insert into T_Btree(X,Y) values(4,'44444');
go
B聚集索引的情况：
begin tran;
insert into T_Heap(X,Y) values(4,'44444');
go
-------------------------------------------------------------------------------------------------------------------------------------------------------
总结：
select
1、 查询运行中每一条读到的记录或键值加 S 锁，如果记录不用返回就马上释放锁，如果用就根据隔离级别而定。
2、对于每一个索引都会在上面的键值加 S 锁
3、对于每个读过的页面，都加 I（意向） 锁。
4、查询要扫描的页记录越多、锁的数目也越多、查询用到的索引越多，锁的数目也越多。
所以、
1、尽量减小记录集
2、尽量使用 index scan 避免全表扫描
3、尽量设计好index ，力求一个索引就可以完成一个查询任务
upate
1、对于每个索引都会加 U 锁
2、只对要修改的记录和键加 X 锁
3、与目标列相关的索引越多，锁的数目也会越多。
4、扫描过的数据页面越多、意向锁也就越多，在扫描的过程中，对所有扫描到的记录加锁，哪怕上面没有修改。
所以、
1、尽量减小记录集
2、尽量减少无用的索引
3、尽量使用 index scan 避免全表扫描
delete
1、第一步先要找到要删除的数据、第二步删除目标数据。数据有好的索引第一步申请的锁就会少一些。
2、 delete不只是要删除数据本身，还要删除索引键，所以一张表上的索引越多，删除时要加的锁就越多，
就越容易阻塞。
所以、
只有那些有必要的索引我们才创建。
