What’s that Read_Committed_Snapshot Transaction Support for EF6 About Anyway?

One of the items listed in the EF6 specs is:

  • Default transaction isolation level is changed to READ_COMMITTED_SNAPSHOT for databases created using Code First, potentially allowing for more scalability and fewer deadlocks.

 

I’m no database wizard, so I have mostly just glazed over that. It’s one of those changes that most devs take for granted…some default setting changed, it’s a better default…okay, thanks… But I’m a bit of a plumber and don’t like to take much for granted. I wanted to see a before and after.

In all honesty, I initially misread this. If I did,I’m sure a few others have as well. I didn’t even know what this transaction level was…but I only know enough to be dangerous about database transaction levels anyway.

So I started by looking to see if EF6 was setting this transaction level in it’s internal code when it created connections to do queries or other commands.

WRONG!

I read more closely and started looking for the database creation code in EF, thinking that it sets this transaction level in order to execute the various commands to create the database and schema.

WRONG AGAIN! 🙂

So I started looking into dbtransactions and there is no such isolation level!

Here’s the documentation:

image

Where is READ COMMITTED SNAPSHOT?

Not there because I was WRONG AGAIN! 🙂

Finally I started noticing that READ_COMMITTED_SNAPSHOT is a SQL Server database setting, not an on the fly setting for a specific transaction. It is an ON/OFF switch. Your database either uses a snapshot or it does not. (Please let me know if I’m wrong about it being SQL Server only. It’s all I’m able to find.)

So it’s a parameter that needs to be set when creating a database. AHA! Up through EF5, EF was ignoring that setting when creating a database. But it turns out that it’s something of a “best practice” to have that setting ON.

What does it mean to have READ_COMMITTED_SNAPSHOT ON for SQL Server database?

Nick Beradi provides a nice explanation in his blog post, Deadlocked!: “read committed snapshot” Explained.

Basically what this does is create a snapshot or read-only database of your current results that is separate from your live database. So when you run a SELECT statement, to read your data, you are reading from a read-only copy of your database. When you change your database, it happens on the live database, and then a new copy or snapshot is created for reading against.

So now, as a plumber, I know how to show before and after. 🙂

Here I’m asking SQL Server what the setting is for my database named EFDataLayer.ThingieContext which was created by EF5:

image

The value is 0. Read_Committed_Snapshot is OFF.

Here’s another one, but this one was created by EF6:

image

The value is 1. Read_Committed_Snapshot is ON.

That’s the change. Most of us will never even notice it. But it will alleviate problems like this one, one of many in stackoverflow (and disregard the response, which is not marked as an answer for a reason 😉 ) http://stackoverflow.com/questions/17529064/using-read-committed-snapshot-with-ef-code-first-5

But I Don’t Want That ON by Default

You can always go to the database and call

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT OFF

I Just Want to Use Snapshot On-Demand

That’s what the transaction level support is for.

You can always create your own transaction and set the isolation level to SNAPSHOT for specific command execution.

Next Steps?

You have now reached the limit of my Snapshot “expertise”. For more, check out the MSDN doc on Snapshot Isolation in SQL Server or try googlebing or maybe this (#itsATrap!).

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!  

13 thoughts on “What’s that Read_Committed_Snapshot Transaction Support for EF6 About Anyway?

  1. I suspect that SQL Azure Databases using this mode by default (and not allowing it to be changed) may also play a factor. This way Code-first codebases being deployed to Azure will have the right settings out of the box to play nicely, but if you need something specific in a hosted or on-prem environment you still have the freedom to change to a different isolation level.

  2. "Please let me know if I’m wrong about it being SQL Server only. It’s all I’m able to find."

    Oracle and PostgreSQL behave in a way similar to READ_COMMITTED_SNAPSHOT by default, and it can’t be disabled.

  3. From MSDN "If a database has been enabled for snapshot isolation but is not configured for READ_COMMITTED_SNAPSHOT ON, …"

    Just wanted to point out (for reasons of clarity) the on-demand bit only works when the snapshot isolation is enabled.

    1. Thank, Johnny! I moved my blog and some of the urls with apostrophes are not matching up with how graffiti created them in the old blog. Both of the urls I linked to in the article had apostrophes! I added permanent redirects for them. I don’t know how I’ll track the rest down. I only found two links to my blog in the article. Did I miss any?

  4. I had db created with EF5 version and then i upgraded form EF5 to EF6(without re-creating db) i am seeing value IS_READ_COMMITTED_SNAPSHOT set to true , though i had not checked what was my value before upgrading?
    So it is safe to assume that if we upgrade to EF6 we don’t need to do anything explicit .

    Anyways again a informative article by you.

  5. Hi Anshul,
    I assumed the answer was no but I just tested to be 150% sure. Upgrading to EF6 won’t touch the database. (That I was already sure of.) But I ran another migration once the project was using EF6 and still it did not change the setting on the database. You must have done something somewhere to set it previously (??). FWIW, I was using SQL Server 2012 LocalDB for this test.

  6. We upgraded to EF 6 and had to roll back to EF 5 because this feature is turned on (we’re doing database-first approach). Certain stored procedures would get called that needed to lock the database for the duration of the transaction and it caused major issues for us. Is there a way to turn this feature off completely in EF 6 in code or some kind of setting?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.