What's the difference between Entity Framework (6) transactions with single and multiple SaveChanges() calls

c# entity-framework-6 transactions

Question

I want to know what are the practical differences of executing a transaction in the same database context between these 3 ways:

1) Multiple operations with one single SaveChanges(), without explicitly using a sql transaction

using (TestDbContext db = new TestDbContext())
{
    // first operation
    // second operation
    db.SaveChanges();
}

2) Multiple operations with one single SaveChanges(), using a sql transaction

using (TestDbContext db = new TestDbContext())
using (DbContextTransaction trans = db.Database.BeginTransaction())
{
     // operation 1
     // operation 2
     db.SaveChanges();    
     trans.commit();
}

3) Multiple operations with multiple SaveChanges(), using a sql transaction

using (TestDbContext db = new TestDbContext())
using (DbContextTransaction trans = db.BeginTransaction())
{
     // operation 1
     db.SaveChanges();    
     // operation 2
     db.SaveChanges();

     trans.commit();
}

In (2) and (3), if commit() is supposed to actually execute requested sql queries to database, is it really different, say, save changes for each operation or save changes for all operation at once?

And if (1) can also allow multiple operations to be safely executed in the same database context so what's the main use of manually starting a transaction? I'd say we can manually provide try/catch block to roll back the transaction if something bad happens, but AFAIK, SaveChanges() also covers it, automatically, at least with SQLServer.

** UPDATED: Another thing is: Should I make db context and transaction variables class-level or these should be local to containing methods only?

1
5
12/14/2015 5:16:35 PM

Accepted Answer

If you do not start a transaction, it is implicit. Meaning, all SaveChanges() you perform will be available in the database immediately after the call.

If you start a transaction, SaveChanges() still performs the updates, but the data is not available to other connections until a commit is called.

You can test this yourself by setting break points, creating new objects, adding them to the context, and performing a SaveChanges(). You will see the ID property will have a value after that call, but there will be no corresponding row in the database until you perform a commit on the transaction.

As far as your second question goes, it really depends on concurrency needs, what your class is doing and how much data you're working with. It's not so much a scoping issue as it is a code execution issue.

Contexts are not thread safe, so as long as you only have one thread in your application access the context, you can make it at a broader scope. But then, if other instances of the application are accessing the data, you're going to have to make sure you refresh the data to the latest model. You also should consider that the more of the model you have loaded into memory, the slower saves are going to be over time.

I tend to create my contexts as close to the operations that are to be performed as possible, and dispose them soon after.

3
12/14/2015 6:21:58 PM

Popular Answer

Your question doesn't really seem to be about entity framework at all, and is more regarding sql transactions. A sql transaction is a single 'atomic' change. That is to say that either all the changes are committed, or none are committed.

You don't really have an example which covers the scenario, but if you added another example like:

using (TestDbContext db = new TestDbContext())
{
     // operation 1
     db.SaveChanges();    
     // operation 2
     db.SaveChanges();
}

...in this example, if your first operation saved successfully, but the second operation failed, you could have a situation where data committed at the first step is potentially invalid.

That's why you would use a sql transaction, to wrap both SaveChanges into a single operation that means either all data is committed, or none is committed.



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