EF Queries Using Read Commited Though snapshot_isolation_state and is_read_committed_snapshot_on are ON

Question

I am running this query,

SELECT name
, 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?

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.