January 2, 2013 at 8:24 am
Do i need to explicitly say SET TRANSACTION ISOLATION LEVEL SNAPSHOT; in each batch that i want to operate under Snapshot isolation?
I altered database
Set Allow_Snapshot_Isolation ON, but even when i disconnect all sessions and reconnect, DBCC USEROPTIONS, and system behaviour say im using Read Committed.
Im obviously missing something stupid here but just cant see what it is.
January 2, 2013 at 8:58 am
If you want snapshot by default, you need to set Read Committed Snapshot Isolation. http://msdn.microsoft.com/en-us/library/ms175095(v=SQL.105).aspx
Otherwise, allowing snapshot isolation just allows it. It doesn't set it as the default, and you will need to set it for each connection.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 3, 2013 at 2:21 pm
james marriot (1/2/2013)
Do i need to explicitly say SET TRANSACTION ISOLATION LEVEL SNAPSHOT; in each batch that i want to operate under Snapshot isolation?I altered database
Set Allow_Snapshot_Isolation ON, but even when i disconnect all sessions and reconnect, DBCC USEROPTIONS, and system behaviour say im using Read Committed.
Im obviously missing something stupid here but just cant see what it is.
Not to muddy the waters but yes, you must issue the SET every time if you want SNAPSHOT isolation. SNAPSHOT is its own isolation level, separate from READ COMMITTED which is the unsettable default.
The other option containing "snapshot", READ_COMMITTED_SNAPSHOT, is not an isolation level in and of itself. Rather it is an alternate "mode" or "implementation" of the READ COMMITTED isolation level. When READ_COMMITTED_SNAPSHOT is enabled row versioning occurs when in the READ COMMITTED isolation level, but that is very different from SNAPSHOT.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 3, 2013 at 3:12 pm
james marriot (1/2/2013)
Do i need to explicitly say SET TRANSACTION ISOLATION LEVEL SNAPSHOT; in each batch that i want to operate under Snapshot isolation?
Yes.
I altered database
Set Allow_Snapshot_Isolation ON, but even when i disconnect all sessions and reconnect, DBCC USEROPTIONS, and system behaviour say im using Read Committed.
The option is Allow_Snapshot_Isolation. Buy turning it on, you allow sessions to request snapshot isolation level. That's all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply