笔记46-徐 一个常见的select ,update ,insert ,delete动作要申请的锁

笔记46-徐 一个常见的select ,update ,insert ,delete动作要申请的锁

--一个常见的select ,update ,insert ,delete动作要申请的锁

--两个表都加了索引的字段
--employeeID
--managerid
--modifieddate

--------------------一个常见的select动作要申请的锁-----------------------------------------------
--在可重复读的隔离级别下，共享锁要保留到事务提交的时候才释放，所以如果
--这个隔离级别下开启另一个事务，再运行一个查询语句，就能看到这个查询所申请的主要
--共享锁。因此可以使用这种简单方法分析一下一个查询语句会申请哪些锁，并且不需要
--SQL Trace
--（1）在连接A中，将事务隔离级别设置为可重复读（repeatable read）
--（2）在运行查询语句之前先开启一个事务
--（3）运行查询语句，但是不提交这个事务
--（4）在第二个连接里，查询sys.dm_tran_locks动态管理视图来分析查询结束后连接
--A还持有的锁


--我们先在有聚集索引的那张表上运行一句最简单的查询 在SSMS里新建一个查询---------
--查询一：
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
SET STATISTICS PROFILE ON
GO
--以下查询使用了聚集索引查找 ctrl+l
BEGIN TRAN
SELECT [EmployeeID],[LoginID],[Title] FROM [dbo].[Employee_Demo_BTree] WHERE [EmployeeID] IN(3,30,200)
-------------------------------------------------------------------------------------------------------

--查看DMV看一下有多少个锁被这个连接持有-----------------------------------------------------------
USE [AdventureWorks] --要查询申请锁的数据库
GO
SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid]
WHERE c.[dbid]=DB_ID('AdventureWorks')  ----要查询申请锁的数据库
ORDER BY [request_session_id],[resource_type]


--查询一所持有的锁
--（1）因为连接正在访问数据库[AdventureWorks],所以在数据库一级加了一个共享锁,以防止
--别人将数据库删除


--（2）因为正在访问表格[Employee_Demo_BTree]，所以在表格上加了一个意向共享锁，以防止
--别人修改表的定义  resource_type:object


--（3）查询有3条记录返回，所以在这3条记录所在的聚集索引键上，分别持有一个共享锁。
--在这3个键所在的页面上，持有一个意向共享锁  resource_type:page,key 锁住整页

--可以说，这个查询申请锁的数目是很少的。其他用户访问同一张表，只要不访问这3条记录
--，就不会影响到。这是因为查询使用了“聚集索引查找”的关系

---------------------------------------------------------------------------------------------

--运行查询二：在SSMS里新建一个查询，运行之前记得将前面查询一的事务提交或者回滚 多次运行最后那句COMMIT TRAN
--BEGIN TRAN
--SELECT [EmployeeID],[LoginID],[Title] FROM [dbo].[Employee_Demo_BTree] WHERE [EmployeeID] IN(3,30,200)
--COMMIT TRAN

--查询二
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
SET STATISTICS PROFILE ON
GO
BEGIN TRAN
SELECT
[EmployeeID],
[LoginID],
[Title]
FROM [dbo].[Employee_Demo_Heap]
WHERE [EmployeeID]=3

--COMMIT TRAN
--运行之后最后记得加上COMMIT TRAN

---------------分析-------------------------------------------
--因为[Employee_Demo_Heap]的[EmployeeID]上是一个非聚集索引，所以SQL在用非聚集索引
--找到这条记录之后，必须再到数据页面上把其他的行上面的数据找出来（所谓的“书签查找” bookmark lookup）
--从申请的锁上也能看出来，虽然只返回一条记录，可是他在[PK_Employee_EmployeeID_Demo_Heap]
--（index_id是2 表明是非聚集索引）上申请了一个key锁，在RID（datapage数据页上的行row）
--申请了一个row锁。在这两个资源所在的页面上各申请了一个page意向锁


--与上面的例子比较，虽然查询二返回的结果和查询一是一样的，但是由于他使用的是非聚集索引+书签查找
--bookmark lookup，所以申请的锁的数目要比查询一多。一个查询要使用的索引键（或者RID）数目越多
--，他申请的锁也就会越多。没有用到的索引上不会申请共享锁




--那么是不是所有的查询都只在返回的记录上加锁呢？现在再来做下面这个试验，运行他之前
--请记得将前面那个事务提交或回滚

--------------------------------------------------------------------------------------
--修改一 ：update语句：在SSMS里新建一个查询
--首先开启一个事务，修改一条查询不会返回记录
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
SET STATISTICS PROFILE ON
GO
BEGIN TRAN
UPDATE [dbo].[Employee_Demo_Heap]
SET [Title]='aaa'
WHERE [EmployeeID]=70

--再在另一个连接里运行查询三
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
SET STATISTICS PROFILE ON
GO
BEGIN TRAN
SELECT
[EmployeeID],
[LoginID],
[Title]
FROM [dbo].[Employee_Demo_Heap]
WHERE [EmployeeID] IN(3,30,200)


---------------------分析--------------------------------------------------
--由于要返回3条分布在不同数据页上的记录，SQL认为做非聚集索引+书签查找并不比
--做一个表扫描快，所以他直接选择了一个表扫描。

--DMV显示查询三已经得到了RID 1:3181:2和1:3181:29上的锁，他们应该是EmployeeID为3和30
--的，他在往下找EmployeeID为200的时候，读到了RID 1:3208:22。他是EmployeeID为70，
--被上面的update语句修改了，update那个事务还没有提交，所以查询三被阻塞了

--现在把修改一回滚，阻塞解除，查询三能够执行完毕，可以看到他的执行计划与他持有的锁

--SQL一直往下找
--书签
--70   ->还没有提交
--80
--90
--100
--.
--.
--.
--.
--.
--.
--200

--所以不能继续往下找，如果跳过的话，他不知道跳过的是不是就是他要找的那条记录，所以阻塞了
--一个RID对应一个EmployeeID

--与查询一不同的是，查询三不但在这三条记录所在页面（1:3211,1:3181）申请了IS锁，
--还在表格的所有页面上都申请了IS锁。所以这就是全表扫描，扫描了所有的数据页面带来
--的后果。更严重的是，查询三在扫描每一张页面的时候，会对读到的每一个数据记录加上
--一个共享锁（读完了这条记录就会释放，不用等到整个语句结束）只要有任何一个记录
--上的锁没有申请到，查询就会被阻塞


--------------------------------------------------------------------------------------------
--运行修改二，再运行查询一
--修改二：update语句：在SSMS里新建一个查询
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
SET STATISTICS PROFILE ON
GO
BEGIN TRAN
UPDATE [dbo].[Employee_Demo_BTree]
SET [Title]='aaa'
WHERE [EmployeeID]=70



--再在另一个连接里运行查询一
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
SET STATISTICS PROFILE ON
GO
--以下查询使用了聚集索引查找 ctrl+l
BEGIN TRAN
SELECT [EmployeeID],[LoginID],[Title] FROM [dbo].[Employee_Demo_BTree] WHERE [EmployeeID] IN(3,30,200)


---------------------------分析-------------------------------------------------------------
--这是因为查询一使用的是索引查找index seek，不需要每条记录都读一遍，所以就不用
--去读EmployeeID70,也就不会被阻塞住  因为select是select书签里面的内容根本不用到表里去读取
--数据



--------------------------总结select动作--------------------------------------------------------
--规律：在非“未提交读”的隔离级别上
--已提交读
--可重复读
--可序列化
--（1）查询在运行过程中，会对每一条读到的记录或键值加共享锁。如果记录不用返回。
--那锁就会被释放。如果记录需要被返回，则视隔离级别而定，如果是“已提交读”，则也释放
--否则，不释放

--（2）对每一个使用到的索引，SQL也会对上面的键值加共享锁

--（3）对每个读过的页面，SQL会加一个意向锁

--（4）查询需要扫描页面和记录越多，锁的数目也会越多。查询用到的索引越多，锁的数目
--也会越多


--避免阻塞采取的方法
--（1）尽量返回少的记录集，返回的结果越多，需要的锁也就越多

--（2）如果返回结果集只是表格所有记录的一小部分，要尽量使用index seek，避免全表扫描这种
--执行计划

--（3）可能的话，设计好合适的索引，避免SQL通过多个索引才找到数据

--当然，这些对于“已提交读”以上隔离级别而言。如果使用“未提交读”，SQL就不会申请这些共享锁
--阻塞也不会发生




--------------------一个常见的update动作要申请的锁-----------------------------------------------
--对于update语句，可以简单理解为SQL先做查询，把需要修改的记录给找到，然后在这个记录
--上做修改。找记录的动作要加S锁，找到修改的记录后加U锁，再将U锁升级为X锁。

--这里用上面两张表做例子，选用repeatable read的隔离级别，运行一个update语句
USE [AdventureWorks]
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRAN
UPDATE [dbo].[Employee_Demo_Heap]
SET [Title]='changeheap'
WHERE [EmployeeID] IN(3,30,200)


--这个update语句在非聚集索引上申请了3个U锁，在RID上申请了3个X锁。这是因为语句借助非聚集索引
--PK_Employee_EmployeeID_Demo_Heap（index_id是2）找到了这3条记录。非聚集索引PK_Employee_EmployeeID_Demo_Heap
--本身没有用到Title这一列，所以他自己不需要做修改。但是数据RID上有了修改，所以RID上加的是X锁，其他
--索引上没有加锁

--从这个例子可以看出，如果update借助了哪个索引，这个索引的键值上就会有U锁,没有用到的
--索引上没有锁。真正修改发生的地方会有X锁。对于查询涉及的页面，SQL加了IU锁意向更新锁，修改
--发生的页面，SQL加了IX锁 意向排他锁 （先查询再修改）锁key 锁索引键值 因为修改的列没有被索引

--如果修改的列被一个索引使用到了，会是什么情况呢？为了完成这个测试，先在Employee_Demo_BTree
--上建一个会被修改的索引

CREATE NONCLUSTERED INDEX [Employee_Demo_BTree_Title] ON [AdventureWorks].[dbo].[Employee_Demo_BTree]
([Title] ASC)


--再运行下面语句
USE [AdventureWorks]
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRAN
UPDATE [dbo].[Employee_Demo_Heap]
SET [Title]='changeheap'
WHERE [EmployeeID] IN(3,30,200)



--语句利用聚集索引找到要修改的3条记录.但是我们看到有9个键上有X锁。
--很有意思：PK_Employee_EmployeeID_Demo_BTree（index_id=1）聚集索引，也是数据存放的地方。
--刚才做的update语句没有改到他的索引列，他只需把Title这个列的值改掉。所以在index1上，
--他只申请3个X锁，每条记录一个

--但是表格在Title上面有一个非聚集索引IX_Employee_ManagerID_Demo_BTree（index_id=5）,
--并且Title是第一列。他被修改后，原来的索引键值就要被删除掉，并且插入新的键值。
--所以在index_id=5 上要申请6个X锁，老的键值3个，新的键值3个

--因为其他索引没有使用到Title这一列，所以他们上面都没有申请锁
--这就是9个key锁的来源


--------------------update语句的规律---------------------------------------------------------
--（1）对每一个使用到的索引，SQL会对上面的键值加U锁

--（2）SQL只对要做修改的记录或键值加X锁

--（3）使用到要修改的列的索引越多，锁的数目也会越多

--（4）扫描过的页面越多，意向锁也会越多。在扫描的过程中，对所有扫描到的记录也会加锁，哪怕
--上面没有修改

-----------------------------结论-------------------------------------------------------
--想降低一个update语句被别人阻塞住的几率，除了注意他的查询部分之外，数据库设计者
--还要做的事情有：

--（1）尽量修改少的记录集。修改的记录越多，需要的锁也就越多

--（2）尽量减少无谓的索引。索引的数目越多，需要的锁也可能越多

--（3）但是也要严格避免表扫描的发生。如果只是修改表格记录的一小部分，要尽量使用index seek索引查找
--避免全表扫描这种执行计划






--------------------一个常见的delete动作要申请的锁-----------------------------------------------
--这次使用read committed这个默认隔离级别

USE [AdventureWorks]
BEGIN TRAN
DELETE [dbo].[Employee_Demo_BTree]
WHERE [LoginID]='adventure-works\kim1'


--可以看到delete语句在聚集索引（index_id=1）和两个非聚集索引（index_id=2和3）上各申请了一个X锁
--在她们所在的页面上申请了一个IX锁

--如果使用repeatable read这个级别运行上面的delete命令，就能看出好像做select的时候一样，做
--delete的时候SQL也需要先找到要删除的记录。在找的过程中也会加锁

--现在运行一个新的delete语句，会使用全表扫描

USE [AdventureWorks]
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRAN
DELETE [dbo].[Employee_Demo_Heap]
WHERE [LoginID]='adventure-works\tete0'

--可以看到delete语句在3个非聚集索引（index_id=2、3、4）上各申请了一个X锁。在她们
--所在的页面上申请了一个IX锁。在修改发生的heap数据页面上，申请了一个IX锁，相应的
--RID上（真正的数据记录）申请了一个X锁。其他扫描过的页面申请了IU锁

----------------------------规律-----------------------------------------------------------------
--（1）delete的过程是先找到符号条件的记录，然后做删除。可以理解为先是一个select,然后
--是delete.所以,如果有合适的索引,第一步申请的锁就会比较少 不用表扫描

--（2）delete不但是把数据行本身删除,还要删除所有相关的索引键.所以一张表上索引数目越多
--锁的数目就会越多,也就越容易发生阻塞

--为了防止阻塞,我们既不能绝对地不建索引,也不能随随便便地建立很多索引,
--而是要建立对查找有利的索引.对于没有使用到的索引,还是去掉比较好





--------------------一个常见的insert动作要申请的锁-----------------------------------------------
--相对于select,update,delete，单条记录的insert操作对锁的申请比较简单。SQL会为新插入
--的数据本身申请一个X锁，在发生变化的页面上申请一个IX锁。由于这条记录是新插入的，被
--其他连接引用到的概率会相对小一些，所以出现阻塞的几率也要小

--还是用刚才的那两张表做例子。首先要插入的是heap结构的表
USE [AdventureWorks]
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRAN
INSERT INTO [dbo].[Employee_Demo_Heap]
        ( [EmployeeID] ,
          [NationalIDNumber] ,
          [ContactID] ,
          [LoginID] ,
          [ManagerID] ,
          [Title] ,
          [BirthDate] ,
          [MaritalStatus] ,
          [Gender] ,
          [HireDate] ,
          [ModifiedDate]
        )
SELECT
501,
480168528,
1009,
'adventure-works\thierry0',
263,
'Tool Desinger',
'1949-08-29 00:00:00.000',
'M',
'M',
'1998-01-11 00:00:00.000',
'2004-07-31 00:00:00.000'




--如果插入的是有B树结构的表格

USE [AdventureWorks]
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRAN
INSERT INTO [dbo].[Employee_Demo_BTree]
        ( [EmployeeID] ,
          [NationalIDNumber] ,
          [ContactID] ,
          [LoginID] ,
          [ManagerID] ,
          [Title] ,
          [BirthDate] ,
          [MaritalStatus] ,
          [Gender] ,
          [HireDate] ,
          [ModifiedDate]
        )
SELECT
501,
480168528,
1009,
'adventure-works\thierry0',
263,
'Tool Desinger',
'1949-08-29 00:00:00.000',
'M',
'M',
'1998-01-11 00:00:00.000',
'2004-07-31 00:00:00.000'


--两者都申请了以下锁资源：
--（1）数据库上的S锁（resource_type=DATABASE）

--（2）表上的IX锁（resource_type=OBJECT）

--（3）每个索引上都要插入一条新数据，所以有一个key上的X锁

--（4）在每个索引上发生变化的那个页面，申请了一个IX锁（resource_type=PAGE）

--唯一不同的是，是在heap结构上还得申请一个RID锁。因为真正的数据不是放在索引上，而是放在heap数据页面上






----------------------------------------结论-------------------------------------------
--如果SQLDBA要控制SQL锁的申请和释放行为，以缓解阻塞和死锁问题，需要考虑的因素有：
--1、事务隔离级别的选定
--事务隔离级别越高，隔离度就越高，并发度也就越差。如果选择了比较高的隔离级别，SQL
--不可避免地要申请更多的锁，持有的时间也会增加。所以在设计应用的时候，一定要
--和用户谈好，尽量选择默认的隔离级别（read committed）


--2、事务的长短和事务的复杂度
--事务的长度和复杂度决定论这个事务在SQL内部会持续多长时间，也能决定SQL会同时在
--多少张表和索引上申请和持有锁。事务越简单，就越不容易发生阻塞和死锁。所以这
--也必须和用户商量好，尽量避免在一个事务里做很多事情


--3、从应用整体并发度考虑，单个事务一次处理的数据量不能过多
--应用的性能，不单要衡量单个连接的处理速度，也要衡量在并发处理的情况下，整体
--的平均速度怎么样。从连接个体来讲，可能在一个事务里把数据一次都处理掉比较快
--但是如果处理的数据量很大，就会影响到其他连接同时访问同一对象。所以，如果
--一个应用的并发要求比较高，就一定要严格控制单个事务处理的数据量。如果有什么
--事务操作需要访问或修改表格内的大量数据，最好调整到并发用户比较少的时候运行


--4、针对语句在表格上设计合适的索引
--合适的索引能使SQL在读取尽可能少的数据量的前提下，把需要处理的数据找到。如果
--没有合适的索引，SQL在做select,update,delete的时候，会申请比要处理的目标数据量
--多得多的锁，从而导致阻塞或死锁。这种情形可以通过加索引的方式提高并发度
--同时，SQL在做update,insert,delete的时候，会对有关联的所有索引都做修改，在她们
--上面申请锁。从这个角度讲，索引越多，产生的锁的数目也就越多，阻塞和死锁的几率
--也就会越高

--所以数据库设计员需要做的，是要确保有足够的索引，防止语句做全表扫描，但是也要
--去掉那些对语句运行贡献不大的索引。不能随便往表格上加索引