MSSQL2000 lock的简单探索

Question

下面是我自己测试的一些代码，开启两个查询窗口进行的测试。spid 51 
use mydb
gocreate table t (id int)
gosp_lock
/*
51 7 0 0 DB                  S GRANT
51 1 85575343 0 TAB                  IS GRANT
53 7 0 0 DB                  S GRANT
*/
--1 探索insert产生的锁
begin transaction 
insert into t values (1)
sp_lock
/* 会产生一个行锁X，表锁IX insert会阻塞update、delete及DDL
51 7 0 0 DB                  S GRANT
51 7 1993058136 0 RID 1:28:0           X GRANT
51 7 0 0 PAG 1:28             IX GRANT
51 7 1993058136 0 TAB                  IX GRANT
51 1 85575343 0 TAB                  IS GRANT
53 7 0 0 DB                  S GRANT
*/
select object_name(1993058136) --objId 可以得知被锁定的对象是 talter table t add name varchar(10)--奇怪，同一个session可以执行DDL
gocommit--2 探索update产生的锁 制定where条件
begin transaction 
update t set name='hanjs' where id=1sp_lock
/* 仅仅给表加了IX锁，仅对DDL阻塞
51 7 0 0 DB                  S GRANT
51 7 1993058136 0 TAB                  IX GRANT
51 1 85575343 0 TAB                  IS GRANT
53 7 0 0 DB                  S GRANT
*/
commit--3 探索update产生的锁 无where条件
begin transaction 
update t set name='hanjs' sp_lock
/*
51 7 0 0 DB                  S GRANT
51 7 1993058136 0 RID 1:28:0           X GRANT
51 7 1993058136 0 RID 1:28:4           X GRANT
51 7 1993058136 0 PAG 1:28             IX GRANT
51 7 1993058136 0 TAB                  IX GRANT
51 1 85575343 0 TAB                  IS GRANT
53 7 0 0 DB                  S GRANT
*/
commit--4 探索delete产生的锁 有where条件
begin transaction 
delete from t where id=1sp_lock
/* 会看到行锁、页锁及表锁 同样insert可以执行，其他的DML及DDL都不可以
51 7 0 0 DB                  S GRANT
51 7 1993058136 0 RID 1:28:0           X GRANT
51 7 1993058136 0 PAG 1:28             IX GRANT
51 7 1993058136 0 TAB                  IX GRANT
51 1 85575343 0 TAB                  IS GRANT
53 7 0 0 DB                  S GRANT
*/
rollback--5 探索delete产生的锁 无where条件
begin transaction 
delete from tsp_lock
/* 可见，对于delete，会阻塞delete、update，而insert正常处理
51 7 0 0 DB                  S GRANT
51 7 1993058136 0 RID 1:28:0           X GRANT
51 7 1993058136 0 RID 1:28:4           X GRANT
51 7 1993058136 0 PAG 1:28             IX GRANT
51 7 1993058136 0 TAB                  IX GRANT
51 1 85575343 0 TAB                  IS GRANT
51 7 1993058136 0 RID 1:28:1           X GRANT
51 7 1993058136 0 RID 1:28:2           X GRANT
53 7 0 0 DB                  S GRANT
*/
commit--奇怪的地方，在通过session执行完DML后可以执行DDL
begin transaction 
update t set name='hanjs' where id=1alter table t drop column name --奇怪，同一个session可以执行DDL
gosp_lock
/* 此时会出现Sch-M锁，包括之前的update对应的锁，此时所有的DDL、DML都被阻塞
51 7 0 0 DB                  S GRANT
51 7 1 0 TAB                  IX GRANT
51 7 3 0 TAB                  IX GRANT
51 7 12 0 TAB                  IX GRANT
51 7 11 0 TAB                  IX GRANT
51 7 3 2 KEY (3301d4502d08)   X GRANT
51 7 1993058136 0 TAB                  Sch-M GRANT
51 1 85575343 0 TAB                  IS GRANT
51 7 3 1 KEY (5a009616a639)   X GRANT
51 7 1 1 KEY (5800c7965d9d)   X GRANT
53 7 0 0 DB                  S GRANT
*/
commit--
sp_lock
/*
51 7 0 0 DB                  S GRANT
51 1 85575343 0 TAB                  IS GRANT
53 7 0 0 DB                  S GRANT
*/spid 53use mydb
go
delete from t where id=2--不存在id=2的，可见会被阻止
update t set id=3 where id=2--被阻塞
insert into t values (2)--可以执行，不会被阻塞
select * from t
--被阻塞，和oracle差别很大，因为MSSQL默认的是readcommited 只有提交后才可以看到select * from t with (nolock)--读脏处理，可以看到信息--update 没有符合的纪录时
delete from t where id=5--不会被阻塞update t set id=3 where id=2--不会被阻塞insert into t values (3,'hanjs')--可以执行，不会被阻塞select * from t--不会被阻塞--update 无where条件
delete from t where id=2--被阻塞update t set id=3 where id=2--被阻塞insert into t values (3,'hanjs')--可以执行，不会被阻塞select * from t--被阻塞--delete 没有符合的纪录时
delete from t where id=2--不存在id=2的，可见会被阻止update t set id=3 where id=2--被阻塞insert into t values (2,'hanjs')--可以执行，不会被阻塞select * from t
--被阻塞，和oracle差别很大，因为MSSQL默认的是readcommited 只有提交后才可以看到select * from t with (nolock)--读脏处理，可以看到信息--delete 无where条件
delete from t where id=2--不存在id=2的，可见会被阻止update t set id=3 where id=2--被阻塞insert into t values (2,'hanjs')--可以执行，不会被阻塞select * from t
--被阻塞，和oracle差别很大，因为MSSQL默认的是readcommited 只有提交后才可以看到--奇怪的地方，在通过session执行完DML后可以执行DDL
delete from t where id=2--被阻塞update t set id=3 where id=2--被阻塞insert into t values (2,'hanjs')--被阻塞select * from t--被阻塞总结如下：因MSSQL默认的事物隔离级别为readcommited，以及默认的锁定为rowlock1、insert，会阻塞其他session的update、delete、select及DDL，而不会阻塞insert2、update，当没有符合条件的纪录被更新时，会阻塞DDL，而DML不会被阻塞，当存在符合的纪录时，会阻塞delete、update、select及DDL，而insert并不会被阻塞3、delete基本上与update相同rowlock可以这样看，在insert、update、delete时，有多少条纪录，就会在sp_lock中存在多少条TYPE=RID的X锁。用过oracle的都知道这种写法，select * from table [where...] for update，这样处理时避免其他session更改此信息,在MSSQL中，可以这样处理（但因oracle是rowlock因提供了回滚段而并不阻塞select以及其他的未涉及的行），select * from table with (updlock) where ...,这样就可以避免其他session修改数据了，但是会阻塞其他session对此表的update、delete等操作，而不会阻塞insert、select欢迎各位能指出错误的地方，多谢！

Answer 1

楼主你把t表的id设置成主键再试试 :)

Answer 2

呵呵，那样就会出现idx和key锁了吧。稍后测试下。多谢了！不知道你有没有什么心得体会可以分享下么？

Answer 3

如果你的条件字段是主键或包含主键，sql server是不会锁所有数据的，只是锁住你访问的那个范围，所以可以实现rowlock,不堵塞其他语句访问

Answer 4

rowlock可以这样看，在insert、update、delete时，有多少条纪录，就会在sp_lock中存在多少条TYPE=RID的X锁。
------------update的数据多的时候sql server会自动提升为tablelock，可以建一个大表试试。

Answer 5

如果在表上有主键，在通过 主键update、delete等，不会阻塞select、update、delete其他主键的处理。但是通过这样过滤会被阻塞。session 1
update test set name='hanjs' where id=5session 2select * from test where id<>5 --会被阻塞，而通过>5 <5都不会阻塞其他的就不总结了，大家作完测试就能清楚了！

Answer 6

to perfectaction<>会进行全索引扫描吧，怎么会不阻塞呢？我都测试完了才说明的。要不你的就是2005的，而2000的确实是这样！！

Answer 7

我试了下确实，看来这方面2005和2000不太一样。
在2000里，当update id=5的数据时，应该对该行放置X锁，对其所在的页放置IX锁，当select * from a where id<>5，会对id=5这条数据放置S锁，S锁和X不容，所以只有等待，会产生阻塞。在2005里和2000一样，也是阻塞，不过，当你第二次再update同一条数据时，select * from a where id<>5可以读到数据,这一点和2000不同。