Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

快照隔离和读取已提交快照同样加了独占锁,请指点!

0 views
Skip to first unread message

快照隔离和读取已提交快照同样加了独占锁,请指点!

unread,
Sep 26, 2006, 4:37:02 AM9/26/06
to
糟糕的快照隔离和读取已提交快照和传统方式一样会加独占锁,请高人指点!
sql
server2005提供了快照隔离和读取已提交快照这两种新的不加锁、无阻塞的事务隔离级别,但我测试的结果是,这两种方式与传统的隔离级别在某些情况下一样会加X锁(独占锁),因而阻塞其它事务的操作。例子:
1、先准备数据和设置数据库选项
use <database_name>
create table mytable(tid int,tname varchar(5000))
go

declare @i int
set @i=1
while @i<=20000
begin
insert into mytable(tid,tname) values(@i,'<'+space(4998)+'>')
set @i = @i + 1
end
go

alter database <database_name> set read_committed_snapshot on
go
alter database <database_name> set allow_snapshot_isolation on
go

2、在一个窗口(A窗口)中执行以下语句
use <database_name>
begin tran
update mytable set tname = 'hello' where tid > 5000
注意:只有"begin tran",先不要commit或rollback

3、在另一个窗口(B窗口)中执行以下语句
use <database_name>
update mytable set tname = 'hello' where tid = 1
go
结果B窗口被阻塞

4、再开一个窗口(C窗口)
查询sys.dm_tran_locks表,发现A窗口加了X锁,而B窗口处理WAIT状态

5、将A窗口rollback后,B窗口执行完成


6、在A窗口的begin tran 前增加“set transaction isolation level
snapshot”,使A窗口在“读取已提交快照”下执行,结果同样出现了加X锁的情况

7、将A窗口的“ where tid > 5000”改成“where tid = 2”,加锁的情况没有再出现

我很郁闷,在多台机器上测试都是这样的结果,还望多位高人多指点,在此多谢了


RM

unread,
Sep 26, 2006, 9:06:02 AM9/26/06
to
Snapshot isolation can reduce blocking, that means writer will not block
reader. But you can't expect it to let multiple processes write to same page
at same time.

快照隔离和读取已提交快照同样加了独占锁,请指点!

unread,
Sep 30, 2006, 3:21:02 AM9/30/06
to
1、窗口A
begin tran
update mytable set tname = 'bill' where tid = 1
2、窗口B
begin tran
update mytable set tname = 'bill' where tid = 2
这种情况同样没有更新同一条记录,那为什么这种情况又不会出现阻塞呢,
总的来说就是,什么情况下一个窗口的update语句会阻塞另一个窗口的update语句呢
thanks
“RM”编写:

RM

unread,
Sep 30, 2006, 12:14:02 PM9/30/06
to
If those rows are not on same page, they will not block each other.

快照隔离和读取已提交快照同样加了独占锁,请指点!

unread,
Oct 13, 2006, 4:37:22 AM10/13/06
to
how can I know these rows are on the same page?

thanks!
“RM”编写:

0 new messages