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?

Irrespective of Entity framework, SQL Server would require ISOLATION LEVEL to be set, Something I have in my SPs as SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for reading records from a table which also has heavy write. I am not sure how the same can be achieve with Entity framework... — Habib
– Habib, Commented Mar 11, 2014 at 15:02
@Habib I am not concerned about reads on EF side, I am concerned about writes in another application — Vladimirs
– Vladimirs, Commented Mar 11, 2014 at 15:07
A SELECT can cause a block, EF would generate a SELECT statement as well for SQL Server, and if your reads are taking too much time, then sure blocking can occur. — Habib
– Habib, Commented Mar 11, 2014 at 15:11
@Habib I don't mind if SELECT will be blocked. I want avoid blocking UPDATE/INSERT. It looks like READ UNCOMMITTED works to avoid SELECT block but not INSERT/UPDATE — Vladimirs
– Vladimirs, Commented Mar 11, 2014 at 15:22

Gert Arnold · Accepted Answer · 2014-03-11 21:17:59Z

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

Slava · Accepted Answer · 2014-03-11 19:23:39Z

READ UNCOMMITTED should help, because it doesn't issue shared locks on data that are being retrieved. So, it doesn't bother your other application that intensively updates the data. Another option is to use SNAPSHOT isolation leven on your long-running SELECT. This approach preserves data integrity for selected data, but for a cost of higher CPU, and more intense tempdb usage.

Collectives™ on Stack Overflow

Can entity framework select block the table?

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related