I am running this query,

, snapshot_isolation_state
, is_read_committed_snapshot_on
FROM sys.databases s

name        snapshot_isolation_state    is_read_committed_snapshot_on
master        1                               0
tempdb        0                               0
model         0                               0
msdb          1                               0
MyDB1         1                               1
MyDB2         1                               1

MyDB1 and MyDB2 is enabled snapshot but still all my Entity Framework queries are using,

select transaction_isolation_level from sys.dm_exec_requests

All sessions values are transaction_isolation_level=2 which means Read Committed. What can I do?

1 Answer 1


Since both MyDB1 and MyDB2 have READ_COMMITTED_SNAPSHOT ON, queries using the READ COMMITTED isolation level will use row versioning instead of locking for read consistency. This behavior is similar to SNAPSHOT isolation level except at the statement level rather than transaction level. No action is needed on your part if this is your desired behavior.

One cannot determine from the transaction_isolation_level value reported by sys.dm_exec_requests whether a READ COMMITTED isolation level uses row versioning or locking.

The ALLOW_SNAPSHOT_ISOLATION database option is unrelated to READ_COMMITTED_SNAPSHOT and may be toggled independently. ALLOW_SNAPSHOT_ISOLATION only needs to be set when you want to use the SNAPSHOT transaction isolation level with an explicit SET TRANSACTION ISOLATION LEVEL SNAPSHOT.

  • Now I am thinking to set TransactionScope with IsolationLevel.Snapshot as mentioned web.archive.org/web/20140325063216/http://blogs.msdn.com/b/… Do you have any idea about this? Aug 10 at 11:58
  • @user960567, it depends what you want to do. Using TransactionScope with IsolationLevel.Snapshot will start an explicit snapshot tranaction so all the queries executed within the transaction scope will reflect data at the time the transaction started.
    – Dan Guzman
    Aug 10 at 13:35
  • Thanks. Dunno for whatever reason setting IsolationLevel.Snapshot started crashing the process(w3wp/IIS) which makes me thinking its risky. I have reverted back for. Aug 11 at 4:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.