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

.net entity-framework

Question

I'm assessing EF for my upcoming new applications.

How can the IsolationLevel of every EF transaction in an application be changed globally? For instance, let's say I wish to utilize "Read Committed Snapshot".

While it is OK to declare the IsolationLevel when a TransactionScope is explicitly required (see the code below), it would be unsightly to have to enclose each EF save activity 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

Mixing IsolationLevles is probably not a smart idea. Am I mistaken about that?

In SQL Server and Serializable (unlike Oracle), adding a straightforward read might result in a conversion lock stalemate.

"If you need to have readers not block writers and writers not block readers, it is advised that you employ READ COMMITTED transactions and READ COMMITTED SNAPSHOT ISOLATION." is taken from the EF FAQ.

I don't understand why SQL Server (in contrast to Oracle's multi-versioning) defaults to a pessimistic concurrency model and why EF makes it so difficult to adjust the default isolation level. A configuration option ought to be relatively simple to use, unless I'm missing something.

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

Accepted Answer

I'm fairly certain that the database provider chosen determines the default EF transaction isolation level.SaveChanges carries out 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 may be seenBeginTransaction is known as withoutIsolationLevel specified. It generates provider-specific transactions internally, usingIsolationLevel.Unspecified If no isolation level is given, the database server or driver shall operate at the default level of isolation. The default isolation level in SQL Server isREAD COMMITED Thus, I anticipate that it will utilize it, but I haven't tried it yet.

You may override the isolation level if you wish to adjust it worldwide.SaveChanges inside your class came fromObjectContext then wrapbase.SaveChanges() in practiceTransactionScope .

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

Popular Answer

There could be a restriction in VB, but in C# you would go as follows:

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

TransactionOptions, which is the second argument, may be generated as follows:

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

You may alter all of your solution's transaction scopes in one place by placing the aforementioned code in a Factory method and invoking it anytime you require transaction options.



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