Entity Framework 6 Not Using Snapshot Isolation Level

Question

I'm trying to get EF 6's default transaction isolation level to use snapshot isolation, but it's not working and always using Readcommitted. Here's what I've done so far:

I've enabled SQL Server snapshot isolation on my SQL Server 2014 database by executing the following commands:

ALTER DATABASE MyDb
SET READ_COMMITTED_SNAPSHOT ON
GO
ALTER DATABASE MyDb
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

I have a DbContext implementation and have written the following Linqpad script to output the isolation level that's being used, and it's always showing "Readcommitted" which is bad. Here's the Linqpad script:

void Main()
{
    this.Database.SqlQuery<string>(@"SELECT CASE transaction_isolation_level 
    WHEN 0 THEN 'Unspecified' 
    WHEN 1 THEN 'ReadUncomitted' 
    WHEN 2 THEN 'Readcomitted' 
    WHEN 3 THEN 'Repeatable' 
    WHEN 4 THEN 'Serializable' 
    WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
    FROM sys.dm_exec_sessions 
    where session_id = @@SPID
    ").Single().Dump();
}

I've seen people explicitly setting the transaction isolation level by newing up transactions, but I'd like to set snapshot isolation as the default transaction type, like maybe in the OnModelCreating override in my DbContext. Is that possible? Or do you always have to do this in explicit transactions? We're using Unity IoC to inject our DbContext for us, and we're not declaring any explicit transactions anywhere in our code currently...

Are you using EF code-first? Can you pass sql connection to your DbContext constructor? — Evk, Mar 30 '17 at 15:09
Yes, using EF code-first. Linqpad is configured to instantiate DbContext instance "Via the parameterless constructor" — Andy, Mar 30 '17 at 21:09
You are using only linqpad? If not - how you create context in real application? — Evk, Mar 30 '17 at 21:13
I'm using Unity IoC like the following: container.RegisterType(); — Andy, Mar 31 '17 at 12:45
I moved the Linqpad script with the SQL "select case" into my application, and it still shows "Readcommitted" — Andy, Mar 31 '17 at 13:02
Snapshot isolation can be achieved through the following, but I'm looking for a global default if such a thing exists: using (var trans = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.Snapshot })) { // query goes here } — Andy, Apr 01 '17 at 18:44
Yes I understand what you mean and even spent some time trying to find a way, but unfortunately with no results. — Evk, Apr 01 '17 at 19:01
@Andy, You should never register a DbContext into IoC if you cannot make sure the live time is PerWebRequest / Transient (see https://entityframeworkcore.com/knowledge-base/50477116/which-lifetime-manager-do-i-register-my-dbcontext-into-unity-container-when-writing-a-wpf-application-). For Unity checkout https://github.com/unitycontainer/unity/wiki/Unity-Lifetime-Managers — Daniel Müller, May 12 '20 at 12:47

score 4 · Answer 1 · answered Oct 15 '19 at 08:20

When you use SET READ_COMMITTED_SNAPSHOT ON, all transactions running under READ COMMITTED will make use of SQL Server's row versioning. READ COMMITTED is the default isolation level in SQL Server. This means that unless your application specifies a different isolation level (like older versions of Entity Framwork did), using SET READ_COMMITTED_SNAPSHOT ON will instantly let your applications use row versioning. In other words, READ COMITTED is the isolation level you want to use when using SET READ_COMMITTED_SNAPSHOT ON.
When you use SET ALLOW_SNAPSHOT_ISOLATION ON, transactions running against your database are now allowed to use SET TRANSACTION ISOLATION LEVEL SNAPSHOT. Transactions running under SNAPSHOT will also make use of SQL Server's row versioning. You will have to explicitly set the isolation level to SNAPSHOT in your application, though.

You should now understand that you don't have to use both SET READ_COMMITTED_SNAPSHOT ON and SET ALLOW_SNAPSHOT_ISOLATION ON, because they are both different methods to make transactions use SQL Server's row versioning.

Using only SET READ_COMMITTED_SNAPSHOT ON will instantly change the way your application works, but you won't have to change your code.
Using only SET ALLOW_SNAPSHOT_ISOLATION ON will give you better control over what transactions make use SQL Server's row versioning, won't instantly change the way your applications work, but you will have to change your code to make use of it.

Before making a decision, you should know there are differences between SET READ_COMMITTED_SNAPSHOT ON vs. using the SNAPSHOT isolation level combined with SET ALLOW_SNAPSHOT_ISOLATION ON. While these are both methods that let your transactions make use of SQL Server's row versioning, there are big behavioral differences: Read committed Snapshot VS Snapshot Isolation Level

Entity Framework 6 Not Using Snapshot Isolation Level

1 Answers1