Entity Framework and transaction isolation level

Question

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.

Duplicate of http://stackoverflow.com/questions/12459338/locking-tables-in-entity-framework? — James Gaunt, Oct 09 '12 at 07:50

score 44 · Accepted Answer · edited Nov 19 '18 at 08:59

44

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

edited Nov 19 '18 at 08:59

Hakan Fıstık

16,800
14
110
131

answered Oct 09 '12 at 07:51

Wouter de Kort

39,090
12
84
103

<> So don't I have to do anything else? And why did you say "any other transaction"? It might happen that there will be not `other transaction` but just a query such as `select * from my_table`? – Alan Coromano Oct 09 '12 at 07:57
2

Serializable is the highest transaction level. It allows volatile reads but it doesn't allow modifications. I don't know if this totally covers your scenario since you also want to disallow reading. If you run your commannds trough the Entity Framework it will always use a transaction. If you execute your Sql query by hand you will have to manually put it in a TransactionScope. – Wouter de Kort Oct 09 '12 at 08:14
I'm not using sql queries manually in a C# code. So I don't have to use `TransactionScope`, do I? – Alan Coromano Oct 09 '12 at 08:20
EF will use a default transaction scope. You will only need to use a custom TransactionScope if you want custom settings. – Wouter de Kort Oct 09 '12 at 08:37
@WouterdeKort How to set custom transaction scope and TransactionScopeAsyncFlowOption in Entity Framework – 3 rules Oct 14 '16 at 09:24
I think Wouter above explains why your not seeing an error. However this article on TransactionScope implies you may still have concurrency problems should two threads try to modify the same entity: http://www.ladislavmrnka.com/2012/09/entity-framework-and-pessimistic-concurrency/ – andrew pate Jun 26 '17 at 20:30

Entity Framework and transaction isolation level

1 Answers1

UPDATE

Linked