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不只是要删除数据本身,还要删除索引键,所以一张表上的索引越多,删除时要加的锁就越多,
就越容易阻塞。
所以、
只有那些有必要的索引我们才创建。
最新文章
- 匹夫细说C#:庖丁解牛聊委托,那些编译器藏的和U3D给的
- 【BZOJ-3697&;3127】采药人的路径&;YinandYang 点分治 + 乱搞
- Windows服务器nginx+tomcat服务负载均衡
- URAL 1306 - Sequence Median 小内存求中位数
- random background
- MySQL 索引 总结
- [Splay伸展树]splay树入门级教程
- MySql入门(2-2)创建数据库
- 树莓派播放视频的播放器omxplayer
- 一个账户接管几乎所有阿里巴巴网站(CSRF漏洞+WAF绕过)
- BZOJ 5104
- Java_jsp.jstl.Function函数标签库.记录
- SQL Server里如何处理死锁
- Mysql命令行tab自动补全方法
- 界面编程之QT绘图和绘图设备20180728
- visual studio code 写c++代码
- FreeOpcUa compile
- Tips——RN canvas缩放处理
- Spark Core(二)Driver上的Task的生成、分配、调度(转载)
- 爬虫--Scrapy之Downloader Middleware