Can entity framework select block the table?

Question

I heard that SQL Server SELECT statements causing blocking.

So I have MVC application with EF and SQL Server 2008 and it shares DB with another application which is very frequently writes some data. And MVC application generates some real-time reports based that data which comes from another application.

So given that scenario is it possible that while generating a report it will block some tables where another application will try to write data?

I tried to make some manual inserts and updates while report is generated and it handled fine. Am I misunderstood something?

Answer 1

This is one of the reasons why in Entity Framework 6 for Sql Server a default in database creation has changed:

EF is now aligned with a “best practice” for SQL Server databases, which is to configure the database’s READ_COMMITTED_SNAPSHOT setting to ON. This means that, by default, the database will create a snapshot of itself every time a change is made. Queries will be performed on the snapshot while updates are performed on the actual database.

So with databases created by EF 5 and lower, READ_COMMITTED_SNAPSHOT is OFF which means that

the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation.

Of course you can always change the setting yourself:

ALTER DATABASE MyDb SET READ_COMMITTED_SNAPSHOT ON