Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Isolation levels - Database Engine


Isolation levels - Database Engine

Author
Message
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5689 Visits: 25280
Comments posted to this topic are about the item Isolation levels - Database Engine

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
That question kicks ***... more please.
William Vach
William Vach
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3158 Visits: 1711
Shouldn't the where clause of the 3rd statement be "where id = 2"?
Andeavour
Andeavour
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1002 Visits: 932
Excellent question. Fooled me, but then it's fooled 82% of us so far...Tongue



BHansen
BHansen
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 165
I got this one, but just wanted to clarify a couple of questions in my own mind ....

(1) If Read Committed Snapshot Isolation had been an option, that would have been correct as well, right?
(2) The question asks what isolation level is being used by the database. Isn't it more correct to have asked what isolation level is being used by the first connection?
StarNamer
StarNamer
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1294 Visits: 1992
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
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4069 Visits: 3648
William Vach (3/5/2009)
Shouldn't the where clause of the 3rd statement be "where id = 2"?


A great question that makes you think. But we need to fix the typo in the 3rd statement.
Irish Flyer
Irish Flyer
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1229 Visits: 240
It seems that READ COMMITTED gives the same result. Transaction 2 cannot change the row until transaction 1 finishes, but within transaction 2 it sees any updates it has made as done, even though they have not yet been committed so others can see them. This is regardless of the snapshot isolation setting.
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5689 Visits: 25280
In an effort to further explain what occurs with the SNAPSHOT and READ COMITTED Isolation using Row versioning - the following quotes were taken from
http://technet.microsoft.com/en-us/library/ms345124(SQL.90).aspx. The use of italics and bold facing were added by this poster.
Under the heading
Snapshot Isolation (Transaction-Level Read Consistency)

When snapshot isolation is set, it guarantees transaction-level read consistency where every statement within a snapshot isolation transaction sees only committed changes that occurred before the start of the transaction. Effectively, each statement in the transaction sees the same set of data while the data is available for modification outside of this transaction. Concurrent modifications are not prevented and this "snapshot" transaction is unaware of the changes that are made by other transactions. The version "refresh" occurs only in the start of each transaction as long as you run under snapshot isolation

Under the heading:
Read Committed Isolation Using Row Versioning (Statement-Level Read Consistency)

[quote]When set, statement-level read consistency guarantees that each statement under read committed isolation sees only committed changes that occurred before the start of the statement. Each new statement within the transaction picks up the most recent committed changes. In other words, this version of read committed is semantically similar to traditional read committed in that only committed changes are visible, but the timing of when those changes committed differs. Each statement sees the changes that were committed before the statement began instead of when the resource is read.


In SQL Server 2005, to use row versioning–based isolation, one of the following database options must already be set (and not pending):

Read committed isolation using row versioning for statement-level read consistency
Snapshot isolation for transaction-level read consistency


Hope this clarifies the situation

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Irish Flyer
Irish Flyer
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1229 Visits: 240
Bitbucket,

I understand your explation and I read the same information from BOL, etc. That doesn't change the fact that the way you framed the scenario, it makes no difference whether snapshot isolation is on or not, you should get the same result. You specifically state that Transaction 1 makes its second query before Transaction 2 commits, hence it does not see the change Transaction 2 made. Transaction 2, however, will see the change made with itself when it queries the second time, even though it hasn't committed, because it hasn't ended yet.

This is why a simple answer of READ COMMITTED should be valid.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search