Is it possible for an EF Core dbcontext transaction to fail after a successful savechange?

user_10461387 0
Viewed 1293

I have a transaction where I want to save to SQL Server database some data then do some actions that I cannot redo like sending email, publish a message... etc.

And I wonder if it is possible for an EF Core transaction with a SQL Server Provider to fail event if the context saved successfully the data (it can be one or multiple calls to SaveChanges)

using (var tran = _context.Database.BeginTransaction())
{
    try
    {
        Behaviour behaviour = new Behaviour(request.Kind, request.Label, request.Verb, request.Units, request.Cycles);
        _context.BehaviourSet.Add(behaviour);

        // A- other code here that adds entities to the context 

        // B- Save everything...
        await _context.SaveChangesAsync(cancellationToken);

        // C- Create and publish events to a store or some other non-transactional actions

        // D- I wonder if this transaction can fail even if the SaveChanges is successful
        tran.Commit(); 
    }
    catch (Exception e)
    {
        tran.Rollback();
        // E- logging and other stuff
    }
}
entity-framework-core sql-server c#
2 Answers

While it is possible for for the network connection to break, or the SQL Server's server to crash at any instant, you can use this pattern with a little bit of care. Just reduce the probability of it happening below a certain threshold, and have an operational contingency plan in case it does. No system is 100% reliable, after all.

First, keep the time between the SaveChanges() and Commit() reasonably short to lessen the probability of an environmental failure.

Second, don't use InMemory Tables. They have a special optimistic concurrency model where write/write conflicts are checked on commit. That is the only scenario where SQL Server enforces constraints on commit rather then when the operation is performed.

Third, make sure your SQL Server has plenty of space for the log file, as running out of log space could cause you to not be able to commit (which requires a log write).

Fourth, have an operational procedure in place to handle the rare occurrence of a failure to commit.

user_72977000

Yes, it is possible.

  • Without transaction any SaveChanges is it's own transaction. As such, after Save changes the transaction HAS been commited.

  • With a tx active, one save changes is not committing anything, so the next one may throw an exception forcing a rollback of the whole transaction. If the transaction has ONLY one savechanges, the tx can STILL fail because the commit and the savechagnes do not happen immediately after one another. It may look immediate for you, but it is quite some time for the computers, and it is possible, i.e. that the computer has a failure RIGHT in the moment between the two lines. As a result, the transaction will not commit and you get an exception.

user_2854650
Related