--常见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返回的结果:
![](https://img-my.csdn.net/uploads/201301/14/1358141173_3544.jpg)
连接持有的锁:
(1),因为连接正在访问的数据库,所以它在数据库一级加了一个共享锁,以防止别人将数据库删除
![](https://img-my.csdn.net/uploads/201301/14/1358141198_8749.jpg)
2),因为正在访问表格Employee,所以在表格上加了一个意向共享锁,以防止别人修改表的定义
![](https://img-my.csdn.net/uploads/201301/14/1358141221_1827.jpg)
(3),查询有三条记录返回,所以在这三条记录所在的聚集索引键上,分别持有一个共享锁。在这三个键的页面上,持有一个意向共享锁。
索引键上的锁信息:
![](https://img-my.csdn.net/uploads/201301/14/1358141244_4500.jpg)
页面上的锁信息:
从上面的分析可以看到,这个查询语句所申请的锁的数量是很少的。其它用户访问同一张表,
只要不访问这三条记录,就不会被影响到。这是因为查询使用了Clustered Index Seek的关系。
我们再来看看你下面的查询,在运行它之前,记得将前面那个事务提交或者回滚:
--查询二:
BEGIN TRAN
SELECT
EmployeeID,LoginID,Title
FROM
[HumanResources].[Employee_Test]
WHERE
EmployeeID=3
查询二返回的结果:
![](https://img-my.csdn.net/uploads/201301/14/1358141307_1061.jpg)
这时再运行连接B的代码,返回的结果如下图:
表[HumanResources].[Employee_Test]在EmployeeID字段上存在一个非聚集索引(NONCLUSTERED INDEX),
所以SQL Server再用非聚集索引找到这条记录后,必须再到数据页面上把其他的行上面的数据找出来。
从连接B返回的结果可以看到:
该查询在数据库和表上申请的锁跟查询一是一样的。可是它在
PK_EMPLOYEE_EMPLOYEEID_TEST(index_id是2)上申请了一个KEY锁
![](https://img-my.csdn.net/uploads/201301/14/1358141379_1379.jpg)
在RID(data page上的row)申请了一个Row锁
![](https://img-my.csdn.net/uploads/201301/14/1358141424_6842.jpg)
在这两个资源所在的页上个申请了一个page意向锁
![](https://img-my.csdn.net/uploads/201301/14/1358141443_2912.jpg)
虽然查询二与查询一返回的数据是一样的,但是由于它使用的是非聚集索引+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,看看查询返回的结果:
![](https://img-my.csdn.net/uploads/201301/14/1358141480_2749.jpg)
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就不会申请这些共享锁了,自然阻塞也就不会发生了。