/****************************************************
AboutSQLServer.com blog
Written by Dmitri Korotkevitch

Optimistic isolation levels
2011-09-08
This script requires the database creation script:
http://dwkor.net/blog/2011-08-25/aboutsqlserver(2011-08-25).zip
*****************************************************/

use AboutSqlServerDb
go

/* FILLFACTOR DEMO */
alter index PK_Data on dbo.Data REBUILD
go

select 
	index_level, min_record_size_in_bytes
	,max_record_size_in_bytes, page_count 
	,avg_fragmentation_in_percent
	,avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(
	DB_ID(),
	OBJECT_ID(N'dbo.Data'),
	1,
	NULL,
	'DETAILED'
)
go

set transaction isolation level snapshot
begin tran
	update dbo.Data
	set Value = Value * -1
commit
go

select 
	index_level, min_record_size_in_bytes
	,max_record_size_in_bytes, page_count 
	,avg_fragmentation_in_percent
	,avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(
	DB_ID(),
	OBJECT_ID(N'dbo.Data'),
	1,
	NULL,
	'DETAILED'
)
go


-- REFERENTIAL INTEGRITY 
create table dbo.MasterData
(
	MasterDataId int not null,
			
	constraint PK_MasterData
	primary key clustered(MasterDataId)
)
go

create table dbo.DetailData
(
	MasterDataId int not null,
	DetailDataId int not null,
			
	constraint PK_DetailData
	primary key clustered(MasterDataId, DetailDataId)
)
go

create trigger trgDetailDataAfterInsert
on dbo.DetailData
after insert
as
begin
	if @@ROWCOUNT = 0
		return
		
	if not exists
	(
		select * 
		from dbo.MasterData
		where MasterDataId in
			(select MasterDataId from inserted)
	)
	begin
		rollback tran
		raiserror('Referential Integrity Error',16,1)
		return
	end
end
go

insert into dbo.MasterData(MasterDataId) values(1)
go

insert into dbo.DetailData(MasterDataId, DetailDataId)
values(2,2)
go

-- SESSION 1 CODE

-- STEP 1: 
set transaction isolation level snapshot
begin tran
	select * from dbo.MasterData
	-- RUN SESSION 2 CODE BEFORE INSERT	
	insert into dbo.DetailData(MasterDataId, DetailDataId)
	values(1,2)
commit
go

select * from dbo.MasterData
select * from dbo.DetailData
go

-- SESSION 2 CODE
set transaction isolation level snapshot
begin tran
	delete from dbo.MasterData
commit
go

--TO SOLVE THE PROBLEM
alter trigger trgDetailDataAfterInsert
on dbo.DetailData
after insert
as
begin
	if @@ROWCOUNT = 0
		return
		
	if not exists
	(
		select * 
		from dbo.MasterData WITH (READCOMMITTED)
		where MasterDataId in
			(select MasterDataId from inserted)
	)
	begin
		rollback tran
		raiserror('Referential Integrity Error',16,1)
		return
	end
end
go

-- UPDATING SAME ROW
delete from dbo.MasterData
delete from dbo.DetailData
insert into dbo.MasterData(MasterDataId) values(2)
insert into dbo.DetailData(MasterDataId, DetailDataId)
values(2,2)
go

-- SESSION 1 CODE
-- STEP 1: 
set transaction isolation level snapshot
begin tran
	select * from dbo.DetailData
	-- RUN SESSION 2 CODE 
	
	update dbo.DetailData 
	set DetailDataId = 3 
	where MasterDataId = 2 and DetailDataId = 2
commit
go

-- SESSION 2 CODE
set transaction isolation level snapshot
begin tran
	update dbo.DetailData 
	set DetailDataId = 10 
	where MasterDataId = 2 and DetailDataId = 2
commit
go


-- Different update behavior

delete from dbo.MasterData
delete from dbo.DetailData
insert into dbo.MasterData(MasterDataId) values(1),(2)
insert into dbo.DetailData(MasterDataId, DetailDataId)
values(1,1),(2,2)
go

select * from dbo.DetailData
go

-- SESSION 1 CODE
-- STEP 1: 
set transaction isolation level snapshot
begin tran
	select * from dbo.DetailData
	-- RUN SESSION 2 CODE 
	update dbo.DetailData 
	set DetailDataId = 1 
	where DetailDataId = 2
commit
go

-- SESSION 2 CODE
set transaction isolation level snapshot
begin tran
	update dbo.DetailData 
	set DetailDataId = 2 
	where DetailDataId = 1
commit
go

select * from dbo.DetailData
go
