Last Updated: April 26, 2022
·
323.1K
· seriousm

Why you shouldn't use Entity Framework with Transactions

EntityFramework

This is a .net ORM Mapper Framework from Microsoft to help you talking with your Database in an object oriented manner. Wikipedia

Database Transaction

A database transaction, by definition, must be atomic, consistent, isolated and durable. Database practitioners often refer to these properties of database transactions using the acronym ACID. Transactions in a database environment have two main purposes:

  1. To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure, when execution stops (completely or partially) and many operations upon a database remain uncompleted, with unclear status.
  2. To provide isolation between programs accessing a database concurrently. If this isolation is not provided, the program's outcome are possibly erroneous. Wikipedia

.NET Transactions

A .NET Transaction can be used in different ways by different frameworks to support transactions. The .NET Transaction itself is not connected with the database by any means. MSDN

.NET Transactions and the EntityFramework

If you are using the Entity Framework during an opened TransactionScope, EntityFramework will open a new Transaction right with the next command that will be sent to the Database (CRUD Operation).

Consider this code block:

using (var transaction = new System.Transactions.TransactionScope())
{
    // DBC = Database Command

    // create the database context
    var database = new DatabaseContext();

    // search for the user with id #1
    // DBC: BEGIN TRANSACTION
    // DBC: select * from [User] where Id = 1
    var userA = database.Users.Find(1);
    // DBC: select * from [User] where Id = 2
    var userB = database.Users.Find(2);
    userA.Name = "Admin";

    // DBC: update User set Name = "Admin" where Id = 1
    database.SaveChanges();

    userB.Age = 28;
    // DBC: update User set Age = 28 where Id = 2
    database.SaveChanges();

    // DBC: COMMIT TRANSACTION
    transaction.Complete();
}

https://gist.github.com/SeriousM/e6b30db2b21e7e602655#file-bad_example-cs

The database.SaveChanges() call sends your changes to the database and executes them but they are not really persisted because you are in the database transaction scope. transaction.Complete() actually finishes the database transaction and your data is saved.

That behavior is actually cool and very useful, right?

NO. Absolutely not. full stop.

Why not using .NET Transactions along with EntityFramework

The default isolation mode is read committed and fits perfectly to 99% of your needs, eg. reading data. When you want to save the changes you made to the database (Create, Update, Delete), EntityFramework is smart enough to create a transaction without your notice behind the scenes to wrap the changes. You can be sure that everything will be saved or every change will be discarded (Atomicity).

By using transactions in EntityFramework, you change that behavior and force every CRUD operation during a transaction scope to be executed in serializable isolation mode which is the highest and most blocking type. No process will be able to access the tables you have touched (even reading from it) during your transaction. That can lead to Deadlocks pretty fast and you want to avoid them at all costs!

That's how the code looks like without the explicit usage of transactions:

// DBC = Database Command

// create the database context
var database = new DatabaseContext();

// search for the user with id #1
// DBC: select * from [User] where Id = 1
var userA = database.Users.Find(1);
// DBC: select * from [User] where Id = 2
var userB = database.Users.Find(2);
userA.Name = "Admin";
userB.Age = 28;

// DBC: BEGIN TRANSACTION
// DBC: update User set Name = "Admin" where Id = 1
// DBC: update User set Age = 28 where Id = 2
// DBC: COMMIT TRANSACTION
database.SaveChanges();

https://gist.github.com/SeriousM/e6b30db2b21e7e602655#file-good_example-cs

Rule of Thumb: Save only once per task and don't use transactions.

Edit: thanks to @seimur - One thread should have access to one instance of DbContext which works best in web applications where every request acts as one thread. In windows applications every command or task should have one DbContext which is not shared. If you share the DbContext between threads you might run into issues like having reads sneaked into a foreign transaction.

21 Responses
Add your response

Very interesting. But, why just don't set the IsolationLevel?
http://msdn.microsoft.com/en-us/library/system.transactions.isolationlevel(v=vs.110).aspx

over 1 year ago ·

Do you mean during read?

over 1 year ago ·

Is there any major difference in Transaction and TransactionScope in EF?

over 1 year ago ·

@ganapa: The TransactionScope is used by ado.net which is the underlying provider for entity framework and is not directly bound to the connection. It can be used from outside without having any access to the connection itself.

The Transaction on the other hand is bound to the connection which makes is pretty difficult to manage in large enterprise applications.

over 1 year ago ·

Sorry, but I honestly think this is really dangerous advice. You should use transactions when you need to group read and write operations into a consistent, atomic operation, for example most http requests to an application server. In some circumstances, SaveChanges() might be enough to satisfy your requirements, but most non-trivial usages of EF require multiple calls to SaveChanges(). Requiring each logically atomic action to only call SaveChanges() a single time will eventually become impractical to maintain and makes it impossible to integrate with anything that uses SQL without EF. Many applications are (justifiably) architected to use many EF Contexts (which are designed to be lightweight) within a single transaction. Data integrity also often requires guarantees that the data that you've READ has not changed before you alter it, something that requires transactions for (or EF's Optimistic Concurrency, to a much lesser degree). In short: SaveChanges() is not a full replacement for transactions.

The serializable transaction level IS usually overkill (and the fact that it's the default is annoying), but it's trivial to change it. Finally, the circumstances where you would most likely see deadlock problems, on highly-concurrent read-write databases, is where it is most critical to have proper transactional semantics to maintain integrity. Deadlocks suck, but are easily avoided with good data models and proper use of transactions. It is also preferable to end up with a rare deadlock error than inconsistent data.

over 1 year ago ·

@avicherry: yes, it's true that sometimes you cannot avoid transactions, but the general advice is still to avoid transactions at all because it makes the whole application more complex. you mentioned multiple database context and I agree that they have to be saved at once in a transaction. but this can be done easily at a single place: spawn transaction, save dbContext1-3, complete transaction. and yes, if you really really need to know that the data you want to update exist before, you can use a transaction as well. but be warned, every succeeding read will be in a transaction as well which might not be necessary and will slow everything down or generate a deadlock.

over 1 year ago ·

Agreed. Transaction is unnecessary in Entity Framework. If you need to store complex object ( relational object), complex object should be built before adding.

over 1 year ago ·

It is very dangerous suggestion, at least when you have more than one running thread. Just think of inserting related records into tables. There has been saved the one record into the first table, and thread context has been switched. Second thread reads data. Oops, there is a problem. EntityFramework, at least version 5, by default uses IsolationLevel = IsolationLevel.Unspecified(it means the isolation level either taken from ambient transaction or, if no transaction exists, will be taken database default isolation level), which means select will be executed in transaction, but we do not now at which isolation level. Here we go, since DbContext use the same connection string , a connection we get is the same connection returned from connection pool, therefore we run into the ambient transaction and see partial data which has not been fixed in database yet.

over 1 year ago ·

@seimur: you're right, this doesn't work if you use one DbContext instance for multiple threads. My example was taken from a web application where every thread gets it own DbContext instance. I will update my post accordingly.
Btw, since every pro will identify such problems I don't see it as an issue of this article. The whole purpose of this is to inform the noobs about this problem and maybe avoid it.

over 1 year ago ·

The problem is not DbContext, the problem is an underlying database connection taken from Connection Pool

over 1 year ago ·

I never run into the problem that the connection pool was an issue. Every DbContext instance is associated with a single connection (standard settings). No matter what you do, the DbContext will reset the connection on dispose so that it can be used again by other consumers.

over 1 year ago ·

Thanks for the consideration. You might want to look at how EF does transactions. This appears to make this warning not valid if I read it right, since it says that EF is smart enough to take the highest transaction scope now. http://msdn.microsoft.com/en-us/data/dn456843.aspx

Also, TransactionScope isn't recommended anymore, but use the Async or Database.BeginTransaction() functions.

over 1 year ago ·

What avicherry said ++

The post is also quite deceiving at times.
At times you are saying - "don't drive cares because you can drive over someone and kill them".
You just need to learn to use them and use them in appropriate places, but having it as a rule of thumb is dumb to say the least.

In my latest app I have only 1 place where I use the transaction with EF, but I will use when I have to.

over 1 year ago ·

@pavdro: You're right! But sometimes it's better to warn people with very big signs instead telling them with a low voice that there is some danger ahead.

over 1 year ago ·

there´s a good article about transaction scope: http://www.codeproject.com/Articles/690136/All-About-TransactionScope

I dont know if the Isolation will change when using EF.. I need to take a look closer in this. I really hope not.

TransactionScope is very usefull whan you dont work unit of work.

over 1 year ago ·

EF does not change the isolation level, the problem with serializable comes from a sad decision on the default isolation level of TransactionScope. EF opens transactions in read commited mode, but if you wrap it in a TransactionScope, the transaction will be serializable by default and EF will use that.

Solution: simply create the TransactionScope specifying the isolation level = read commited

using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }))
{
    scope.Complete();
}

Sources:

https://msdn.microsoft.com/en-us/data/dn456843.aspx

"In either case, the isolation level of the transaction is whatever isolation level the database provider considers its default setting. By default, for instance, on SQL Server this is READ COMMITTED."

http://referencesource.microsoft.com/#System.Transactions/System/Transactions/TransactionManager.cs,4853e046f5438f90,references

internal static System.Transactions.IsolationLevel DefaultIsolationLevel
{
    get
    {
        if ( DiagnosticTrace.Verbose )
        {
            MethodEnteredTraceRecord.Trace( SR.GetString( SR.TraceSourceBase ),
                "TransactionManager.get_DefaultIsolationLevel"
                );
            MethodExitedTraceRecord.Trace( SR.GetString( SR.TraceSourceBase ),
                "TransactionManager.get_DefaultIsolationLevel"
                );
        }

        return IsolationLevel.Serializable;
    }
}
over 1 year ago ·
over 1 year ago ·

To avoid what you've just explained you can begin a transaction on the context. Now give the transaction which was created to a sql command. In this case both the queries are executed on the same transaction without fear that a transaction was created behinde the scenes.

over 1 year ago ·

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using System.Transactions;

namespace TransactionsExamples
{
class TransactionsExample
{
static void StartOwnTransactionWithinContext()
{
using (var context = new BloggingContext())
{
using (var dbContextTransaction = context.Database.BeginTransaction())
{
try
{
context.Database.ExecuteSqlCommand(
@"UPDATE Blogs SET Rating = 5" +
" WHERE Name LIKE '%Entity Framework%'"
);

                    var query = context.Posts.Where(p => p.Blog.Rating >= 5); 
                    foreach (var post in query) 
                    { 
                        post.Title += "[Cool Blog]"; 
                    } 

                    context.SaveChanges(); 

                    dbContextTransaction.Commit(); 
                } 
                catch (Exception) 
                { 
                    dbContextTransaction.Rollback(); 
                } 
            } 
        } 
    } 
} 

}

over 1 year ago ·

You got really stupid code. Read manual before use.
1) use .SaveChanges() only once.
2) use transaction associated with your context: using (var transaction = context.Database.BeginTransaction()) { ... }
Period.

over 1 year ago ·

I found this article researching a related topic. I have an Core 3.1 with EF web site that uses stored procs to Get/Insert/Update from tables. The procs are very simple except for a few which exec other procs to update some tables based on a field that was updated like for keeping statistics up to date. I told the DB guy, who doesn't know coding other than SQL that EF already wraps the call to the procs in a transaction, but he insists that we also use Begin Trans and End Trans in the proc. Is there any reason to do this? Also he wants to use TRY/CATCH in the procs, but I tried telling him I want the error returned from the proc so I can post a message to the screen if the proc fails. Any guidance on how an EF proc with only SP calls needs to write the procs for best practices?

over 1 year ago ·