手元の環境で、SQLServerを起動したあとにDML文が実行されていないテーブルでwith(xlock)しても、他のトランザクションがselectするのを排他しないという動作をしており、困惑しています(確認した環境は、SQLServer2012で、isolation level は read committedです。2008R2でも同じ動作を確認しました)。
具体的には、SQLServer起動後にDML文が実行されていないテーブルで下記トランザクションをA→Bの順番で実行しても、トランザクションBは結果が返ってきてしまいます(この動作をケースXとします)。
・トランザクションA
begin transaction
select * from t_sample with(xlock)
・トランザクションB
begin transaction
select * from t_sample
しかし、いったん下記トランザクションCを実行したあとに、A→Bと実行すると、BがAによってブロックされるようになります(この動作をケースZとします)。
・トランザクションC:
begin transaction
update t_sample set c1=100
rollback transaction
いったんケースZの動作となったあとに、ケースXが発生することはありません。ただ、SQLServerのサービスを再起動して試すと、ケースXが発生します。
また、SQLServer起動直後でも、トランザクションAを with(xlock,tablock)やwith(xlock,paglock)とすると排他が正しく行われます。(with(xlock,rowlock)だとケースXとなります)
動作としてはケースZが正しいと思うのですが、ケースXも間違いなく発生していて、その理由がわかりません。
なぜこのような動作になるのでしょうか?
補足:テストで使用したテーブルのクエリは次の通りです。
create table t_sample(
c1 int,
c2 int,
c3 char(8000)
);
declare @i int = 0;
while(@i<100)
begin
insert into t_sample values(@i, @i+1000, 'hello');
set @i = @i+1
end
