--常见SELECT操作要申请的锁
--查看当前数据库的版本信息:
print @@version
/*
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c)1988-2008 Microsoft Corporation
Developer Edition onWindows NT 6.1 <X86> (Build 7601: Service Pack 1)
*/
--我首先使用DBCC USEROPTIONS命令查看一下当前连接的隔离级别:
use AdventureWorks
go
DBCC USEROPTIONS
--在返回的结果的最后一行可以看到当前会话的隔离级别为可提交读
/*
isolation level readcommitted
*/
在可重复读级别下,要保留到事务提交后才释放,所以如果在这个隔离级别下开启一个事物,
再运行一个查询语句,就能够看到这个查询所申请的主要共享锁,因此可以使用这种简单的
方法分析一个查询语句会申请哪些锁,而不需要SQL Trace。
具体步奏如下:
1，在连接A中，将事务隔离级别设置为可重复读
2，在运行查询之前，先开启事务
3，运行查询语句，但是不要提交该事务
4，在连接B中查询sys.dm_tran_locks这张动态管理视图来分析查询结束以后,连接A还持有的锁
--连接A代码:
use AdventureWorks
go
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
SET STATISTICS PROFILE ON
GO
查询一:
BEGIN TRAN
SELECT
EmployeeID,LoginID,Title
FROM
HumanResources.Employee
WHERE
EmployeeIDIN(2,30,200)
--运行连接A代码后返回的结果:
--连接B代码:
USE AdventureWorks
GO
SELECT
request_session_id,
resource_type,
request_status,
request_mode,
resource_description,
OBJECT_NAME(p.object_id) as OBJECT_NAME,
p.index_id
FROM
sys.dm_tran_locks t
left join
sys.partitions p
on
t.resource_associated_entity_id=p.hobt_id
order by
request_session_id,resource_type
--连接B返回的结果:
连接持有的锁:
（1），因为连接正在访问的数据库，所以它在数据库一级加了一个共享锁，以防止别人将数据库删除
2），因为正在访问表格Employee，所以在表格上加了一个意向共享锁，以防止别人修改表的定义
（3），查询有三条记录返回，所以在这三条记录所在的聚集索引键上，分别持有一个共享锁。在这三个键的页面上，持有一个意向共享锁。
索引键上的锁信息:
页面上的锁信息:
从上面的分析可以看到，这个查询语句所申请的锁的数量是很少的。其它用户访问同一张表，
只要不访问这三条记录，就不会被影响到。这是因为查询使用了Clustered Index Seek的关系。
我们再来看看你下面的查询，在运行它之前，记得将前面那个事务提交或者回滚:
--查询二:
BEGIN TRAN
SELECT
EmployeeID,LoginID,Title
FROM
[HumanResources].[Employee_Test]
WHERE
EmployeeID=3
查询二返回的结果:
这时再运行连接B的代码，返回的结果如下图:
表[HumanResources].[Employee_Test]在EmployeeID字段上存在一个非聚集索引(NONCLUSTERED INDEX)，
所以SQL Server再用非聚集索引找到这条记录后，必须再到数据页面上把其他的行上面的数据找出来。
从连接B返回的结果可以看到:
该查询在数据库和表上申请的锁跟查询一是一样的。可是它在
PK_EMPLOYEE_EMPLOYEEID_TEST（index_id是2）上申请了一个KEY锁
在RID（data page上的row）申请了一个Row锁
在这两个资源所在的页上个申请了一个page意向锁
虽然查询二与查询一返回的数据是一样的，但是由于它使用的是非聚集索引+BookMark Lookup,所以申请的锁的数目要比查询一多。
一个查询要使用索引键（或者RID）数目越多，它申请的锁也就越多。没有使用到的索引上不会申请共享锁。
那么是不是所有的查询都会在返回的记录上加锁呢？接下来运行以下代码，在运行之前，记得将之前的那个失误提交或者回滚:
首先在连接A开启一个事务:
BEGIN TRAN
--修改:
UPDATE [HumanResources].[Employee_Test]
SET Title='aaa' WHEREEmployeeID=70
然后再在连接C，开启一个新的事物:
use AdventureWorks
go
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
SET STATISTICS PROFILE ON
GO
--查询三:
SELECT
EmployeeID,LoginID,Title
FROM
[HumanResources].[Employee_Test]
WHERE
EmployeeIDIN(3,30,200)
由于这三条记录都不在同一个页上，SQL Serevr会认为做非聚集索引扫描+Bookmark Lookup并不比做一个表扫面快，所以它直接选择了表扫描。
这样的执行计划会带来的效果就是：
我们先来看看连接B，看看查询返回的结果:
DMV显示，查询三已经得到了RID1：20611：29和1:20611:2上的锁，它应该是EmployeeID 3和30，再往下找200的时候，读到RID:20615：27，
它是EmployeeID 70，被连接A的那句UPDATE语句修改了。UPDATE的那个事务还没有提交，所以查询三就被阻塞了。
现在将修改一回滚，阻塞接触，查询三能够执行完毕，可以看到他的执行计划
最终持有的锁
和查询一不同的是，查询三不但在这三条记录所在的页面上申请了意向共享锁，还在表格的所有页面上都申请了意向共享锁,一下结果是使用
DBCC SHOWCONTIG('[HumanResources].[Employee_Test]')命令查看到的该表的页面信息，一共七页：
申请IS锁的页面
这就是一次全表扫描，扫描了所有的页面带来的结果。更重要的是，查询三在扫描每一张页面的时候，
会对读到的每一个数据记录加上一个共享锁（读完了这条记录就会被释放掉，不用等到整个语句结束）。
只要有任何一条记录上的锁没有申请到，查询就会阻塞，但是运行同样的修改在建有聚集索引的表上
就不会有任何问题，返回的结果为:
连接A:
BEGIN TRAN
--修改:
UPDATE [HumanResources].[Employee]
SET Title='aaa' WHEREEmployeeID=70
连接C：
use AdventureWorks
go
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
SET STATISTICS PROFILE ON
GO
--查询三:
begin TRAN
SELECT
EmployeeID,LoginID,Title
FROM
[HumanResources].[Employee]
WHERE
EmployeeIDIN(3,30,200)
返回的结果为：
未出现阻塞。
从查询计划来看，此时使用的是index seek，不需要每条记录都读取一次，索引就不会去读取 EmployeeID 70的记录，自然也就不会发生阻塞。
从上面的三个例子可以看出（在飞为提交读的隔离级别上）
（1）查询在运行中，会对每条读到的记录或者键值加共享锁，如果记录不被返回，所就会被释放，如果记录需要返回，则视隔离级别而定。
如果是已提交读，则释放，否则不释放
（2）对每一个使用到的索引，SQL Server也会对上面的键值加共享锁
（3）对每隔读过的页面，SQL Server也会加一个意向锁
（4）查询需要扫描的页数和记录数越多，锁的数目也会越多。查询用到的索引越多，锁的数目也会越多
所以，如果想减少一个查询被阻塞的几率，数据库设计这需要做一下事情：
（1）尽可能的使查询返回少的记录集。返回的结果越多，需要的索引也会越多
（2）如果返回的记录只是表格所有记录的一小部分，尽量使用index seek，避免全表扫描（TABLE SCAN）
（3）可能的话，设计好合适的索引，避免SQL Server通过多个索引才能找到数据
以上都是对于“已提交读”以上的隔离级别而言，如果选用“未提交读”，SQL Server就不会申请这些共享锁了，自然阻塞也就不会发生了。