Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Row versionnning and isolation levels Expand / Collapse
Author
Message
Posted Monday, March 30, 2009 8:15 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 1:31 PM
Points: 504, Visits: 270
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
Posted Tuesday, April 21, 2009 2:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 28, 2013 10:48 AM
Points: 2,869, Visits: 371
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
Posted Tuesday, April 21, 2009 6:41 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 1:31 PM
Points: 504, Visits: 270
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
Posted Tuesday, April 21, 2009 7:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 28, 2013 10:48 AM
Points: 2,869, Visits: 371
Answers inline.

Carl B. (4/21/2009)
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."


Thanks for clarifying that.



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
Posted Tuesday, April 21, 2009 8:36 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 1:31 PM
Points: 504, Visits: 270
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
Posted Tuesday, April 21, 2009 10:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 28, 2013 10:48 AM
Points: 2,869, Visits: 371
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
Posted Tuesday, April 21, 2009 10:41 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 1:31 PM
Points: 504, Visits: 270
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.

Best regards.

Carl



Post #701667
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse