对SQL Server中read committed隔离级别的全新认识 S X锁

关于锁，有很多可以学习的，比如，锁的粒度，锁的模式，锁的兼容性，以及隔离级别等等。

但是，具体到运行一个语句时，是怎么去获取到相应的锁的呢，比如，一个select语句，那么需要加S锁，可能是对记录，也可能是对页，也可能是对表，可能通过索引去找到记录，找到记录后，是一次获取所有的锁，还是一部分一部分的获取呢？

比如下面实验中，正好是一条记录占用1个数据页，一共查询32768条数据，那么到底是对32768个页都加上S锁，还是先对一些页加锁，查询出一部分数据，然后再对剩下的页加锁，再查询出一部分数据。

又比如，现在有2个会话，会话1运行select 语句来查询数据，假设查询数据需要大概耗时10秒，而会话2是一个update语句来更新同样的数据，大概需要耗时8秒，会话1先运行，会话2在一秒后运行，那么最那个语句先运行完呢？

一般的想法，应该是会话1先返回，因为会话1的select语句，会对数据页加上共享锁的，既然加上了共享锁，那么会话2要对页加独占的X锁，肯定是等待的，但实际情况却不是这样的。

言归正传，实验代码

1、建表，插入数据，建索引

if OBJECT_ID('test') is not null
   drop table test
go

create table test(id int,v varchar(8000))

insert into test
select 1,replicate('a',8000) union all
select 2,replicate('o',8000) union all
select 3,replicate('c',8000) union all
select 4,replicate('d',8000) union all
select 5,replicate('e',8000) union all
select 6,replicate('f',8000) union all
select 7,replicate('g',8000) union all
select 8,replicate('h',8000) union all
select 9,replicate('i',8000) union all
select 10,replicate('j',8000) union all
select 11,replicate('k',8000) union all
select 12,replicate('l',8000)
go

insert into test
select *
from test
go 18

create index idx_test_id on test(id)

2、建立一个temp_lock表，用来存储sys.dm_tran_locks的信息，这个主要是用来监控语句执行时详细的锁信息

if OBJECT_ID('temp_lock') is not null
   drop table temp_lock
go

select 0 id,* into temp_lock
from sys.dm_tran_locks
where 1 = 2

接下来，需要新建3个查询

3、会话1是监控代码，注意这个代码是死循环，所以需要在会话2和会话3的代码，执行完后， 取消执行查询 ，否则会一直运行下去

declare @i int

set @i = 1

while 1=1
begin
 insert into temp_lock
 select @i,*
 from sys.dm_tran_locks
 
 set @i = @i +1 
end

4、查询2是select语句

select GETDATE()

begin tran

  select id,
       %%lockres%%,   --文件id:页id:记录id
       v
  from test
  where id =2

commit tran

select GETDATE()

5、查询3是update语句

select GETDATE()

update test
set v =replicate('x',8000)
where id = 2 

select GETDATE()

6、运行结果:

select  语句的运行时间是 2014-03-05 14:45:10.510 - 2014-03-05 14:45:27.107 

update语句的运行时间是 2014-03-05 14:45:08.810 - 2014-03-05 14:45:24.247 

由于监控到的信息太多，这里只是选择了少量的数据，注意，在图的右边，有个字段request_session_id 是请求会话id，55是update语句所在的会话，而57是select语句所在的会话，显然， 这行记录上有一个X锁 ，而select语句的会话被update的会话所阻塞，所以request_status 显示为wait，也就是在等待获取S锁

7、那么从上面的图中能说明什么呢？

第一，update语句虽然晚2秒才运行，但是却更早完成了。

第二，select语句中返回的结果，既包含了没有修改的原来的数据，也包含了update语句更新后的数据。

第三，update语句阻塞了select语句。 

之前只知道，在默认的读已提交隔离级别下，在一个事务中，select语句会在语句运行结束后，才会释放S锁，但是通过上面的实验，应该是只要select语句在执行时，把一部分结果发送到客户端，也就是我们在ssms的结果集窗口看到结果的时候，这个S锁就会释放，而不是等select语句整个运行完后，才释放的。

既然是这样，那么select语句在运行时，肯定也是先获取一部分数据的S锁，然后输出，然后接下来再获取下一部分的数据的S锁，然后再输出，这样就导致了可能被update语句阻塞，因为这个时候update语句已经对同一批数据都加上了X锁了，所以就导致了上面的第三个阻塞的情况。

既然是被阻塞了，那么select语句就会比update语句运行的慢了，而update语句的X锁，会一直保持到事务提交或回滚。同样的，为什么select语句读取到了一部分update修改后 xxxxxxxx... 数据呢,而不全是ooooooooo....数据呢？

因为既然select语句被update语句阻塞住了，那么只有等update语句提交以后，才能读取到，所以读取了update语句提交之后的数据，也就是xxxxxxxx...

8、说了这么多，难道这个是SQL Server的bug？

我认为不是，因为 在read committed隔离级别下，只能保证select语句读取到已提交的数据，而不能保证在一个事务中的一个select语句(注意不是一个事务中的前后2个select语句)，只能读取某一个时间点的数据，也就是不能保证这个select语句，读取到的是开始运行时这个   2014-03-05 14:45:10.510 时间点的数据，因为在这个时间点后，数据被修改且提交了，所以他也会读取这个已经修改了的数据，所以这个不是bug。

不过这个多少都有点出乎意料之外，也就是一个查询语句，在执行过程中，完全可能读取到在这个语句开始运行之后，被其他事务所修改且提交了的数据。