Consider setting the READ_COMMITTED_SNAPSHOT option to ON in new SQL Server databases


This database-level option changes the implementation of the default transaction isolation level (read committed) to use snapshots instead of shared locks, potentially allowing for more scalability and fewer deadlocks. It looks like a "best practice" we could just implement by default when we control creation of the database. Some more explanations here: http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx.

This could apply to Code First, Migrations, and Model First.

Here is an interesting article about READ COMMITTED SNAPSHOT: http://coderjournal.com/2008/08/deadlocked-read-committed-snapshot-explained/.

I have also learned that SQL Server Azure set this on by default.
Closed Jan 21 at 6:05 PM by BriceLambson


jlerman wrote Sep 9, 2013 at 1:48 PM

if for some reason someone did notwant that setting, would they have to change it manually in the db after the fact? Or create a migration script for it? I don't happen to see any other way.

BriceLambson wrote Jan 20 at 7:18 PM

Fixed in changeset 3860e9758a233beb0b261c2325a46b348caa32af