有表
ta
ca cb
1 a
2 b
3 c
4 d
5 f
set tran isolation level repeatable readbegin transelect * from id<=3 --这里执行后对前3行添加了共享锁然后update ta set cb='change' where id>=4 --这一行会对后两行添加更新锁,再转换为排它锁对吧?结果阻塞了,请问为什么?
ta
ca cb
1 a
2 b
3 c
4 d
5 f
set tran isolation level repeatable readbegin transelect * from id<=3 --这里执行后对前3行添加了共享锁然后update ta set cb='change' where id>=4 --这一行会对后两行添加更新锁,再转换为排它锁对吧?结果阻塞了,请问为什么?
可以在 id 列上添加索引,这样 update 语句会通过索引定位需要更新的记录,就不会再被阻塞。
有表
ta
id cb
1 a
2 b
3 c
4 d
5 f
set tran isolation level repeatable read begin tran select * from ta with(xlock) id <=3 --这里执行后对前3行添加了排他锁 然后 update ta set cb='change' where id>=4 --这一行会对后两行添加更新锁,再转换为排它锁对吧?结果阻塞了,请问为什么?
create table tb (id int identity,cb char(1));
insert into tb(cb)
select 'a' union all select 'b' union all
select 'c' union all select 'd' union all
select 'f' ;
create clustered index ix_tb_id on tb(id);set tran isolation level repeatable read
begin tran
select * from tb with(xlock) where id<=3
--rollback tran
sp_lock @@spid
/*
2089058478 1 KEY (0400a4427a09) X GRANT
2089058478 1 PAG 1:174 IX GRANT
2089058478 1 KEY (0300146b1a34) X GRANT
1115151018 0 TAB IS GRANT
2089058478 1 KEY (0200c411ba73) X GRANT
2089058478 0 TAB IX GRANT
*/
-- 会话二
update tb set cb='g' where id>=4
select * from tb where id>=4
/*
4 g
5 g
*/
-- 再看
set tran isolation level repeatable read
begin tran
select * from tb where id<=3
rollback transp_lock @@spid
/*
2089058478 1 KEY (0400a4427a09) S GRANT
2089058478 1 PAG 1:174 IS GRANT
2089058478 1 KEY (0300146b1a34) S GRANT
1115151018 0 TAB IS GRANT
2089058478 1 KEY (0200c411ba73) S GRANT
2089058478 0 TAB IS GRANT
*/