分離レベルread committedとjoinの関係

read committedだと対象データ取得し終わった時点で
即ロック解放しちゃうのですね。

/*
create table SampleA(
	ID int identity primary key,
	cnt int not null
)

create table SampleB(
	ID int identity primary key,
	cnt int not null
)

insert into SampleA values( 1)
insert into SampleB values( 1)

select * from SampleA
select * from SampleB
*/

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

while 1 <> 0
begin
	begin tran
		declare @a int, @b int
	
		begin try
			select @a = SampleA.cnt, @b = SampleB.cnt
			from SampleA
			inner join SampleB
			on SampleA.ID = SampleB.ID
			WHERE sampleA.ID = 1
	
			if @a <> @b
			begin
				select @a, @b
			end
		end try
		begin catch
			print 'デッドロック'
			rollback
			continue
		end catch
	
	commit tran
end

SET TRANSACTION ISOLATION LEVEL READ COMMITTED


while 1 <> 0
begin
	begin tran
		UPDATE SAMPLEA
		SET cnt = cnt + 1
		WHERE ID = 1
		
		UPDATE SAMPLEB
		SET cnt = cnt + 1
		WHERE ID = 1
	commit tran	
end