How can the world change? All Entity Framework transactions have the same isolation level.

.net entity-framework

Question

I am evaluating EF for my next new apps.

How can I globally change the IsolationLevel of all EF transactions in an application? Ex: Suppose I want to use "Read Committed Snapshot".

While it is OK to specify the IsolationLevel when I explicitely need a TransactionScope anyway (see code below) it would be ugly having to encapsulate every EF save operation in a TransactionScope.

 'OK
    Using tsc As New TransactionScope(TransactionScopeOption.RequiresNew, TransactionOption.ReadCommitted)
        UpdateShoppingCart
        EnqueueNewOrder
        SendConfirmationEmail
        tsc.Complete
    End Using

    'Is this really the only way to avoid Serializable?
    Using tsc As New TransactionScope(TransactionScopeOption.RequiresNew, TransactionOption.ReadCommitted)
      _ctx.SaveChanges()
      tsc.Complete
    End Using

    Class TransactionOption
        Public Shared ReadOnly ReadCommitted As New TransactionOptions() With {
            .IsolationLevel = IsolationLevel.ReadCommitted,
            .Timeout = TransactionManager.DefaultTimeout
            }
    End Class

I assume mixing IsolationLevles is not a good idea. Am I wrong with that?

With Serializable and SQL Server (in contrast to Oracle) inserting a simple innocent looking read may cause a conversion lock deadlock.

From the EF FAQ: "It is recommended that you use READ COMMITTED transactions, and use READ COMMITTED SNAPSHOT ISOLATION if you need to have readers not block writers and writers not block readers."

I do not understand why EF defaults to Serializable and makes it so difficult to change the default isolation level - with SQL Server (in contrast to Oracle's multi-versioning) defaulting to a pessimistic concurrency model. A configuration option should be very easy to implement - or am I missing something here?

1
7
2/26/2011 11:26:22 AM

Accepted Answer

I'm almost sure that default EF transaction isolation level is based on used database provider. SaveChanges executes this code:

    ... 
    try
    {
        this.EnsureConnection();
        flag = true;
        Transaction current = Transaction.Current;
        bool flag2 = false;
        if (connection.CurrentTransaction == null)
        {
            flag2 = null == this._lastTransaction;
        }
        using (DbTransaction transaction = null)
        {
            if (flag2)
            {
                transaction = connection.BeginTransaction();
            }
            objectStateEntriesCount = this._adapter.Update(this.ObjectStateManager);
            if (transaction != null)
            {
                transaction.Commit();
            }
        }
    }
    ...

As you can see BeginTransaction is called without IsolationLevel specified. Under the hood it creates provider specific transaction with IsolationLevel.Unspecified. Unspecified isolation level should result in default isolation level for database server / driver. In SQL Server default isolation level is READ COMMITED so I expect it should use it but I haven't tested it yet.

If you want to globally change isolation level you can override SaveChanges in your class derived from ObjectContext and wrap base.SaveChanges() in custom TransactionScope.

9
2/26/2011 2:22:03 PM

Popular Answer

There may be a limitation in VB but in C# you would do it like this:

 TransactionOptions transactionOptions = GetTransactionOptions();
 new TransactionScope(TransactionScopeOption.RequiresNew, transactionOptions) 

Note that the second parameter is transactionOptions, this can be created as follows:

 public TransactionOptions GetTransactionOptions()
    {
        TransactionOptions transactionOptions = new TransactionOptions();
        transactionOptions.IsolationLevel = IsolationLevel.ReadCommitted;
        return transactionOptions;
    }

By putting the above code in a Factory method, and calling that factory method whenever you need transaction options, you can have one place to change all transaction scopes in your solution.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow