March 30, 2009 at 8:15 am
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 :
[font="Courier New"]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
[/font]
Compared to doing just this:
[font="Courier New"]ALTER DATABASE my_db SET READ_COMMITTED_SNAPSHOT ON
GO
...
SET TRANSACTION ISOLATION LEVEL READ COMMITED
GO[/font]
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
April 21, 2009 at 2:07 am
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
April 21, 2009 at 6:41 am
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
April 21, 2009 at 7:50 am
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
April 21, 2009 at 8:36 am
Oupps.. instead of 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 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
April 21, 2009 at 10:27 am
Changing the isolation level of a transaction to SNAPSHOT will cause an exception.
Repro:
-- step 1 start
create databaseSnapshotPromotion
go
alter databaseSnapshotPromotion
setallow_snapshot_isolationon
go
alter databaseSnapshotPromotion
setread_committed_snapshoton
go
useSnapshotPromotion
go
create tabledbo.test
(
cint
)
go
-- step 1 end
-- step 2 start
settransaction isolation level read committed
begin tran
print'Read committed using snapshots'
print@@trancount
insertdbo.test
(
c
)
select1 as c
go
-- step 2 end
-- step 3 start
settransaction isolation level snapshot
print'Snapshot'
print@@trancount
insertdbo.test
(
c
)
select2 as c
commit tran
print@@trancount
go
select*
fromdbo.test
-- step 3 end
-- step 4 start
usemaster
go
settransaction isolation level read committed
go
drop databaseSnapshotPromotion
go
-- step 4 end
Changing the isolation level from SNAPSHOT is supported.
E.g.:
-- step 1 start
create databaseSnapshotPromotion
go
alter databaseSnapshotPromotion
setallow_snapshot_isolationon
go
alter databaseSnapshotPromotion
setread_committed_snapshoton
go
useSnapshotPromotion
go
create tabledbo.test
(
cint
)
go
-- step 1 end
-- step 2 start
settransaction isolation level snapshot
begin tran
print'Snapshot'
print@@trancount
insertdbo.test
(
c
)
select1 as c
go
-- step 2 end
-- step 3 start
settransaction isolation level read committed
print'Read committed using snapshots'
print@@trancount
insertdbo.test
(
c
)
select2 as c
commit tran
print@@trancount
go
select*
fromdbo.test
-- step 3 end
-- step 4 start
usemaster
go
settransaction isolation level read committed
go
drop databaseSnapshotPromotion
go
-- step 4 end
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
April 21, 2009 at 10:41 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply