CODEWITHSUNDEEP
c#sqlasp.net-mvcentity-frameworkentity-framework-6
Gab
Gab

Reputation: 531

Entity Framework timeouts when transaction is running

I have a long request, which is importing data into the system using transaction.The problem is that when single transaction is running, in that period other requests to the tables that it is using are blocked and are running forever and ending with Timeout Exceptions

The wait operation timed out

How can I allow to read data normally, even if transaction is running there.

P.S. I don't think that it is a SQL pessimistic data lock, because in transaction period I'm able to select data from tables using SQL Management Studio.

Upvotes: 0

Views: 1549

Answers (1)

Richard
Richard

Reputation: 30618

You can enable Snapshot Isolation in SQL server, which in conjunction to setting the READ_COMMITTED_SNAPSHOT setting will change the behaviour you are seeing.

While a long-running transaction is in progress, instead of other tables being locked, they will see the rows in that table from before the transaction started.

To enable this:

ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON

This is actually the default behaviour in Entity Framework 6 if your database was created by Code-First migrations.

Upvotes: 1

Related Questions