MSSQL2000 lock的简单探索

use mydb
go

create table t (id int)
go

sp_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 可以得知被锁定的对象是 t

alter table t add name varchar(10)--奇怪，同一个session可以执行DDL
go

commit

--2 探索update产生的锁 制定where条件
begin transaction 
update t set name='hanjs' where id=1

sp_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=1

sp_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 t

sp_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=1

alter table t drop column name --奇怪，同一个session可以执行DDL
go

sp_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

use 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 -- 被阻塞

53    7    0    0    DB                        S    GRANT
*/

总结如下：因MSSQL默认的事物隔离级别为readcommited，以及默认的锁定为rowlock 1 、 insert ，会阻塞其他session的update、 delete 、select及DDL，而不会阻塞insert 2 、 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 欢迎各位能指出错误的地方，多谢！

 


原帖
http://topic.csdn.net/u/20080622/13/ce37fa58-9506-49e5-a271-f68bbcce290a.html