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:

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:

The value is 0. Read_Committed_Snapshot is OFF.

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

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!).