Row versionnning and isolation levels

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Row versionnning and isolation levels

Row versionnning and isolation levels

Rate Topic

Display Mode

Topic Options

Author

Message

Carl B.

Posted Monday, March 30, 2009 8:15 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, February 07, 2013 12:53 PM
Points: 491, Visits: 235

Hi everyone,

In our application we want to dynamically switch between two isolation levels : READ_COMMITTED_SNAPSHOT and SNAPSHOT.

Most of the time we want to run with the READ_COMMITTED_SNAPSHOT isolation level but under some circonstances
(consitent reads in multiple tables) we want to switch to the SNAPSHOT isolation level.

Right now we only use the READ_COMMITED_SNAPSHOT isolation level.

I read in the documentation that, in order to permit us to use these two isolation levels, we must ALTER the database to SET both READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION to ON.

I wanted to know what is the impact of doing this :

ALTER DATABASE my_db SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE my_db SET READ_COMMITTED_SNAPSHOT ON
GO
...
SET TRANSACTION ISOLATION LEVEL READ COMMITED
GO

Compared to doing just this:

ALTER DATABASE my_db SET READ_COMMITTED_SNAPSHOT ON
GO
...
SET TRANSACTION ISOLATION LEVEL READ COMMITED
GO

For a connection running just in READ_COMMITED_SNAPSHOT isolation level?

I ask this question because our ALTER DATABASE statements are not dynamic, they are issued at the creation of the database itself.

Best regards.

Carl

Post #686134

Matija Lah

Posted Tuesday, April 21, 2009 2:07 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 09, 2013 4:44 PM
Points: 2,869, Visits: 370

Are you suggesting setting these database settings whenever you connect to the database? That's not necessary. You should set this when the database is created, and only use those that you actually need. From your brief description of your business case it would seem that you need both, but I think READ_COMMITTED_SNAPSHOT could be enough. Perhaps you should provide more information regarding the actual business needs (in business terms).

As far as the differences between SNAPSHOT and READ COMMITTED SNAPSHOT are concerned, perhaps an example might help:
http://milambda.blogspot.com/2006/09/snapshot-transaction-isolation.html
(Skip to Mental Aggregation if you're only interested in the differences.)

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com

Post #701220

Carl B.

Posted Tuesday, April 21, 2009 6:41 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, February 07, 2013 12:53 PM
Points: 491, Visits: 235

Hi Matija,

I don't suggest to set these database settings at connection time. I wrote : "I ask this question because our ALTER DATABASE statements are not dynamic, they are issued at the creation of the database itself."

I was only asking if there is any penalty to set both database properties at database creation time and only using READ_COMMITED snapshot (since we think that the SNAPSHOT isolation level won't be use frequently, only in particular business cases). This is my most important concern.

We need to switch to SNAPSHOT isolation level in order to have a consistent read of several tables.

I read something on your blog that seems to be a limitation for me: "Switching to the snapshot isolation level from any other explicitly set isolation level will cause an exception followed by an immediate rollback of all transactions.".

Is it true that being in the READ_COMMITED_SNAPSHOT isolation level and switching to the SNAPSHOT isolation level (with : SET TRANSACTION ISOLATION LEVEL READ COMMITED) will raise an exception?

Best regards.

Carl

Post #701380

Matija Lah

Posted Tuesday, April 21, 2009 7:50 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 09, 2013 4:44 PM
Points: 2,869, Visits: 370

Answers inline.

Carl B. (4/21/2009)

Thanks for clarifying that. Smile

I was only asking if there is any penalty to set both database properties at database creation time and only using READ_COMMITED snapshot (since we think that the SNAPSHOT isolation level won't be use frequently, only in particular business cases). This is my most important concern.

I can't imagine the setting having any effect on performance, until tempdb is actually used (i.e. after snapshots have been created).

Anyway, your best bet would be to just try it out.

Speaking of tempdb, I suggest you read up on tempdb monitoring.
E.g.: http://milambda.blogspot.com/2006/09/sql-2005-and-tempdb.html

We need to switch to SNAPSHOT isolation level in order to have a consistent read of several tables.

I read something on your blog that seems to be a limitation for me: "Switching to the snapshot isolation level from any other explicitly set isolation level will cause an exception followed by an immediate rollback of all transactions.".

Is it true that being in the READ_COMMITED_SNAPSHOT isolation level and switching to the SNAPSHOT isolation level (with : SET TRANSACTION ISOLATION LEVEL READ COMMITED) will raise an exception?

It should. Let me get back to you with this one.

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com

Post #701457

Carl B.

Posted Tuesday, April 21, 2009 8:36 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, February 07, 2013 12:53 PM
Points: 491, Visits: 235

Oupps.. instead of this:

[b]

Is it true that being in the READ_COMMITED_SNAPSHOT isolation level and switching to the SNAPSHOT isolation level (with : SET TRANSACTION ISOLATION LEVEL READ COMMITED) will raise an exception?

I should ha written this:

Is it true that being in the READ_COMMITED_SNAPSHOT isolation level and switching to the SNAPSHOT isolation level (with : SET TRANSACTION ISOLATION LEVEL SNAPSHOT) will raise an exception?

Sorry for the mistake and thank's for your help.

Carl

Post #701517

Matija Lah

Posted Tuesday, April 21, 2009 10:27 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 09, 2013 4:44 PM
Points: 2,869, Visits: 370

Changing the isolation level of a transaction to SNAPSHOT will cause an exception.

Repro:

-- step 1 start
create database	SnapshotPromotion
go

alter database	SnapshotPromotion
	set	allow_snapshot_isolation	on
go

alter database	SnapshotPromotion
	set	read_committed_snapshot	on
go

use	SnapshotPromotion
go

create table	dbo.test
	(
	c	int
	)
go
-- step 1 end

-- step 2 start
set	transaction isolation level read committed

begin tran

print	'Read committed using snapshots'

print	@@trancount

insert	dbo.test
	(
	c
	)
	select	1 as c
go
-- step 2 end

-- step 3 start
set	transaction isolation level snapshot

print	'Snapshot' 

print	@@trancount

insert	dbo.test
	(
	c
	)
	select	2 as c

commit tran

print	@@trancount
go

select	*
	from	dbo.test
-- step 3 end

-- step 4 start
use	master
go

set	transaction isolation level read committed
go

drop database	SnapshotPromotion
go
-- step 4 end

Changing the isolation level from SNAPSHOT is supported.

E.g.:

-- step 1 start
create database	SnapshotPromotion
go

alter database	SnapshotPromotion
	set	allow_snapshot_isolation	on
go

alter database	SnapshotPromotion
	set	read_committed_snapshot	on
go

use	SnapshotPromotion
go

create table	dbo.test
	(
	c	int
	)
go
-- step 1 end

-- step 2 start
set	transaction isolation level snapshot

begin tran

print	'Snapshot' 

print	@@trancount

insert	dbo.test
	(
	c
	)
	select	1 as c
go
-- step 2 end

-- step 3 start
set	transaction isolation level read committed

print	'Read committed using snapshots'

print	@@trancount

insert	dbo.test
	(
	c
	)
	select	2 as c

commit tran

print	@@trancount
go

select	*
	from	dbo.test
-- step 3 end

-- step 4 start
use	master
go

set	transaction isolation level read committed
go

drop database	SnapshotPromotion
go
-- step 4 end

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com

Post #701654

Carl B.

Posted Tuesday, April 21, 2009 10:41 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, February 07, 2013 12:53 PM
Points: 491, Visits: 235

Hi Matija,

Ok thank's. I noticed that you were changing the isolation level during (inside a transaction). That's why this exception arise.

I made some tests changing the isolation level wihtout being in a transaction and it works without any problem. Smile

Best regards.

Carl

Post #701667

« Prev Topic | Next Topic »

Permissions