转：sql server锁知识及锁应用

　　上一篇我只是做了一个堆表让大家初步的认识到锁的痉挛状态，但是在现实世界上并没有这么简单的事情，起码我的表不会没有索引对吧，，，还有就是我的表一定会有很多的连接过来，10:1的读写，很多码农可能都会遇到类似神乎其神的死锁，卡住，读不出来，插不进入等等神仙的事情导致性能低下，这篇我们一起来探讨下。

 

一： 当select遇到性能低下的update会怎么样？

1. 还是使用原始的person表，插入6条数据，由于是4000字节，所以两条数据就是一个数据页，如下图：

1 drop table dbo.person
2 create table person(id int identity,name char(4000) default 'aaaaa')
3 --插入6条数据，刚好3个数据页
4 insert into dbo.person default values
5 go 6

 

2. 为了模拟性能低下的update操作，我们开个显式事务来更新id=4的记录，并且用profile看一下，如下图：

1 begin tran
2 update dbo.person set name='bbbbb' where id=4

3. 然后我们开下另一个会话连接，读取id=6的记录会是怎样？好奇吗？

1 select * from person where id=6

从上面流程你是否看到，当扫描到89号数据页的slot1槽位的时候卡住了。。。我想你应该知道update正好已经给这条记录加上了x锁。。。如果你够细心，你还会发现，给s锁附加记录的条件是在当引擎发现记录所在的数据页已经附加上了ix锁的情况下，才给该号数据页下的每条记录附加s锁，对吧。。。好了，既然在profile上面看不到了，我还是有其他办法来判断到底select语句现在处于什么状态。

 

4. 使用sys.dm_tran_locks来看当前各个连接持有锁的状态。

1 select  l.request_session_id,
2         db_name(l.resource_database_id),object_name(p.object_id),
3         l.resource_description,l.request_type,
4         l.request_status,request_mode 
5 from sys.dm_tran_locks as l
6 left join sys.partitions as p
7 on l.resource_associated_entity_id=p.hobt_id

仔细观察上图可以看到，当前有51和52号会话，51号在1:89:1槽位上使用了x锁并且没有释放，52号此时也进入了1:89:1中，并且想给该rowid附加s锁，但是你也知道s和x锁是排斥的，所以很无奈的一直保持等待状态。

 

二：使用索引或许可以帮你逃过一劫

　　当你看完上面的讲叙，是不是有点害怕？？？要是在生产环境下出现了这种情况，那我们是不是死的很惨？？？那接下来使用索引是不是真的可以帮我们躲过一劫呢？下面跟我一起看一看。

 

1. 新建索引index

1 -- 在id列上建一个index
2 create index idx_person on dbo.person(id)

 

2. 然后我们看下数据页的分布情况，可以看到下图中78,89,90是表数据页，93号为索引数据页。

1 dbcc traceon(2588,3604)
2 dbcc ind(ctrip,person,-1)

 

3. 麻蛋的，继续执行上面的那个慢update

begin tran
update dbo.person set name='bbbbb' where id=4

 

4. 激动人心的时刻来了，由于数据太少，所以我这里强制让引擎执行我创建的索引，看看结果怎样？

 

居然没卡住？？？现在是不是有一股强烈的好奇心来了，狗狗狗。。。马上开启profile，看看到底都发生了什么？

仔细看完这个图，是不是觉得很有意思呢？？？具体步骤如下：

第一步：给表（object）加上is锁。

第二步：因为要走索引，给93号索引数据页加上is锁。

第三步：找到93号索引数据页的目标key，给这个key加上s锁，有人可能就会问了。。。这个key不就是6嘛，为什么这个key=(61005a25560e)，你要是太好奇我可以告                   诉你，年轻人说话不要太屌，每行索引记录都有一个散列值，这个值就是根据索引的几个字段散列出来的，好处就是防止你的索引长度过大，导致锁这个记录的                   时候太耗费锁空间了。。。。如果你还是不太相信的话，我用dbcc给你看一看。　　　　　　

            

第四步：根据这个key直接跳到存放记录的90号数据页中，万幸的是update的记录刚好不在90号数据页中。。。。就这样躲过一劫了。。。然后select顺利的读取到了该                  读的记录，最后释放相关的is锁。