How to globally change the IsolationLevel of all Entity Framework transactions

Question

I am evaluating EF for my new applications.

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

While it is normal to indicate IsolationLevel when I explicitly need a TransactionScope anyway (see code below) it would be ugly to encapsulate every EF save operation in 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 guess mixing IsolationLevles is not a good idea. Am I really wrong?

With Serializable and SQL Server (unlike Oracle), inserting a simple innocent looking read can cause conversion lock locks.

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

I don’t understand why EF uses Serializable by default and makes it difficult to change the default isolation level - with SQL Server (as opposed to tiered Oracle usage), by default for the pessimistic concurrency model. The configuration option should be very easy to implement - or am I missing something here?

Answer 1

I am pretty sure that the default transaction isolation level for EF is based on the database provider used. 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 . Under the hood, it creates a transaction of a specific provider using IsolationLevel.Unspecified . An undefined isolation level should result in a default isolation level for the database server / driver. In SQL Server, the default isolation level is READ COMMITED , so I expect it to use it, but I haven't tested it yet.

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

Answer 2

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

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

Please note that the second parameter is transactionOptions, it can be created as follows:

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

By putting the above code in the Factory method and calling this Factory method whenever you need transaction parameters, you can have one place to change all transaction areas in your solution.