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

Locking in Microsoft SQL Server (Part 14)
When transaction starts
2012-04-25
*****************************************************/
use master
go

create database TranTest
go

use TranTest
go

create table dbo.Test
(
	ID int 
)
go

select COUNT(*) as [LSN Count] 
from fn_dblog(null, null)
go

select * 
from fn_dblog(null, null) 
order by [Current LSN] desc
go

-- Run in session 2 > begin

begin tran

-- Run in session 2 > end


select COUNT(*) as [LSN Count] 
from fn_dblog(null, null)
go

select * 
from fn_dblog(null, null) 
order by [Current LSN] desc
go


-- Run in session 2 > begin

insert into dbo.Test values(1)

-- Run in another session end


select COUNT(*) as [LSN Count] 
from fn_dblog(null, null)
go

select * 
from fn_dblog(null, null) 
order by [Current LSN] desc
go

-- Run in session 2 > begin

commit

-- Run in session 2 > end

use master
go

alter database TranTest set ALLOW_SNAPSHOT_ISOLATION ON
go

use TranTest
go

create table dbo.Test2
(
	ID int
)

insert into dbo.Test2(ID)
values(-1)
go



-- SESSION 1 CODE. STEP 1
set transaction isolation level snapshot
begin tran
	select * 
	from sys.dm_tran_active_snapshot_database_transactions  
-- STEP 2
	select * from dbo.Test2
-- STEP 3
	select * from dbo.Test2
commit
go

-- SESSION 2 CODE. STEP 1
insert into dbo.Test2(ID) values(-2)
-- STEP 2
delete from dbo.Test2
go


-- SESSION 1 CODE. STEP 1
set transaction isolation level snapshot
begin tran
	select * from dbo.Test
	
	select * 
	from sys.dm_tran_active_snapshot_database_transactions  

-- STEP 2
	select * from dbo.Test2
commit
go

-- SESSION 2 CODE. STEP 1
insert into dbo.Test2(ID) values(-2)
go

use Master
go

drop database TranTest
go
