Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Entity Framework and transaction isolation level

I'm using Entity Framework 4.0. Now I need to restrict access to a table while I'm reading from it or writing to it. Probably that's about transaction isolation level.

How do I do that?

Update

here is what I have

using (var db = new MyDb()) {     using (TransactionScope scope = new TransactionScope())     {         var item = db.MyItems.Single(x => x.Id == 5);         item.Price = 12;         db.SaveChanges();         scope.Complete();      } } 

However, when I put a breakpoint at any line inside using (TransactionScope scope and when I'm stopping there and then I go to Sql Server Management Studio and doing a select query (or even update!) from a table that is using inside a transaction, I'm not getting an error for some reason. But why? It must not allow me to read a data while a transaction is executing.

like image 469
Alan Coromano Avatar asked Oct 09 '12 07:10

Alan Coromano


People also ask

Does Entity Framework support transactions?

Entity Framework internally maintains transactions when the SaveChanges() method is called. It means the Entity Framework maintains a transaction for the multiple entity insert, update and delete in a single SaveChanges() method.

What is the default isolation level in Entity Framework?

Accepted Answer. By default, a Transaction has an IsolationLevel of Serializable. Serializable is the highest level. It requires that the transaction completes before any other transaction is allowed to operate on the data.

When should you not use Entity Framework?

One of the biggest reasons not to use Entity Framework Core is that your application needs the fastest possible data access. Some applications do a lot of heavy data operations with very high-performance demands, but usually business applications don't have that high of a performance demand.

How does Entity Framework handle deadlock?

Also, we can use SQL View with an NOLOCK keyword in a query to prevent deadlock. To overcome this issue we have to implement the single solution of the whole project, which READ UNCOMMITTED data to display on a website. Entity framework uses SQL server transaction ISOLATION LEVEL by default which READ COMMITTED data.


1 Answers

By default, a Transaction has an IsolationLevel of Serializable. Serializable is the highest level. It requires that the transaction completes before any other transaction is allowed to operate on the data.

It has the following restrictions:

  • Statements cannot read data that has been modified but not yet committed by other transactions.
  • No other transactions can modify data that has been read by the current transaction until the current transaction completes.
  • Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

This is a great blog post that explains how to use Transactions with the Entity Framework: Entity Framework transaction scope examples

UPDATE

In Entity Framework 6 the default IsolationLevel is changed to READ_COMMITTED_SNAPSHOT for databases created using Code First, potentially allowing for more scalability and fewer deadlocks. See the future spec of EF 6

like image 136
Wouter de Kort Avatar answered Sep 17 '22 07:09

Wouter de Kort