6

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?

Community
  • 1
  • 1
Vladimirs
  • 8,232
  • 4
  • 43
  • 79
  • 1
    Irrespective of Entity framework, SQL Server would require [ISOLATION LEVEL](http://technet.microsoft.com/en-us/library/ms189122(v=sql.105).aspx) 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 Mar 11 '14 at 15:02
  • @Habib I am not concerned about reads on EF side, I am concerned about writes in another application – Vladimirs Mar 11 '14 at 15:07
  • possible duplicate of [Entity Framework with NOLOCK](http://stackoverflow.com/questions/926656/entity-framework-with-nolock) –  Mar 11 '14 at 15:09
  • 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 Mar 11 '14 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 Mar 11 '14 at 15:22
  • "Choosing a transaction isolation level does not affect the locks acquired to protect data modifications." - from your link – Vladimirs Mar 11 '14 at 15:25

2 Answers2

6

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
Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
0

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.

Slava
  • 1,065
  • 5
  • 11