Can someone clarify the question and answers here please?
The question is asking what isolation the database is set to, but the answers are transaction isolation levels.
Firstly, where is the isolation level for the database set?
Secondly, I ran a number of tests using the following 2 scripts, with variations.
Script 1:
use scratch
drop table t
drop table #t
drop table #u
create table t (id int, val varchar(20))
insert t values(2,'Original')
go
alter database scratch set allow_snapshot_isolation on
alter database scratch set read_committed_snapshot off
go
set transaction isolation level read committed
set nocount on
begin tran
select Val, getdate() from t where id=2
declare @i int
set @i=0
while @i<5
begin
select a.* into #t from sysobjects a, sysobjects b
select top 1 * into #u from #t
drop table #t
drop table #u
set @i = @i+1
end
select Val, getdate() from t where id=2
rollback tran
Script 2:
use scratch
set transaction isolation level read committed
set nocount on
begin tran
select val, getdate() from t where id=2
update t set val='gotcha' where id = 2
select val, getdate() from t where id=2
declare @i int
set @i=0
while @i<5
begin
select a.* into #t from sysobjects a, sysobjects b
select top 1 * into #u from #t
drop table #t
drop table #u
set @i = @i+1
end
rollback tran
Obviously, in each case, the loop is merely to slow down the transaction so that script 2 can start execution while script 1 is running and then script 1 completes before script 2.
The results were as follows:
Run 1:
Script 1:
alter database scratch set allow_snapshot_isolation on
alter database scratch set read_committed_snapshot on
go
set transaction isolation level snapshot
Val
-------------------- -----------------------
Original 2009-03-05 14:45:28.533
Original 2009-03-05 14:45:45.800
Script 2:
set transaction isolation level snapshot
val
-------------------- -----------------------
Original 2009-03-05 14:45:30.770
gotcha 2009-03-05 14:45:30.770
Run 2:
Script 1:
alter database scratch set allow_snapshot_isolation on
alter database scratch set read_committed_snapshot on
go
set transaction isolation level read committed
Val
-------------------- -----------------------
Original 2009-03-05 14:49:38.690
Original 2009-03-05 14:49:55.517
Script 2:
set transaction isolation level read committed
val
-------------------- -----------------------
Original 2009-03-05 14:49:40.893
gotcha 2009-03-05 14:49:40.893
Run 3:
Script 1:
alter database scratch set allow_snapshot_isolation on
alter database scratch set read_committed_snapshot off
go
set transaction isolation level read committed
Val
-------------------- -----------------------
Original 2009-03-05 14:53:53.733
Original 2009-03-05 14:54:08.673
Script 2:
set transaction isolation level read committed
val
-------------------- -----------------------
Original 2009-03-05 14:53:58.280
gotcha 2009-03-05 14:53:58.280
Run 4:
Script 1:
alter database scratch set allow_snapshot_isolation on
alter database scratch set read_committed_snapshot off
go
set transaction isolation level snapshot
Val
-------------------- -----------------------
Original 2009-03-05 15:12:49.790
Original 2009-03-05 15:13:09.010
Script 2:
set transaction isolation level snapshot
val
-------------------- -----------------------
Original 2009-03-05 15:12:58.633
gotcha 2009-03-05 15:12:58.633
In each case the results described in the question were returned. I.e. Script 1 read 'Original', Script 2 updated and read 'gotcha', then Script 1 read 'Original' again.
As far as I can see it is not possible to tell from the results described either whether the transaction isolation level is snapshot or read committed or whether the database has read_committed_snapshot set on or off.
Are my scripts wrong or have I missed the point of the question entirely?
[Edit: Added Run 4, isolation level snapshot with read_committed_snapshot off]
Derek