14

I'm aware of two different scenarios in which exceptions can be produced when working with an Entity Framework DbContext:

  1. Enumerating a query (could throw a EntityCommandExecutionException)
  2. Calling SaveChanges (could throw a DbUpdateException)

Within a single instance of DbContext, I'm wanting to catch these exceptions, try to recover if applicable, and then repeat the operation.

Specifically, if a call to SaveChanges throws an exception because of a deadlock, I would like to retry the call to SaveChanges. I already know how to detect this situation and perform the retry.

I saw this answer here, which indicates that an SQL connection shouldn't be used after a deadlock. This indicates that I should restart the entire DbContext and higher-level operation to recover from such exceptions.

What I'm not sure about is whether it's safe to continue using the DbContext after it has thrown an exception such as this. Will it enter an unusable state? Will it still work but not function correctly? Will SaveChanges no longer occur transactionally?

Community
  • 1
  • 1
Sam
  • 40,644
  • 36
  • 176
  • 219
  • There is an example on MSDN that calls SaveChanges a second time after fixing data here http://msdn.microsoft.com/en-us/library/bb896255.aspx – Colin Oct 02 '13 at 14:31
  • @Colin; thanks, that's a good point. A difference between that example and my example is mine is based on an error raised by the database server, whereas that example is based on an exception generated by Entity Framework in response to the number of rows affected. – Sam Oct 02 '13 at 22:41
  • 2
    Looks to me like you will be ok. This tells you how transactions work in EF5 and 6: http://msdn.com/data/dn456843 I suggest you do a unit test – Colin Oct 03 '13 at 03:42

1 Answers1

6

If you don't supply the DbContext with an already opened SQL connection, the DbContext will open and close the connection for you when you call SaveChanges. In that case there is no danger in keeping the DbContext around, except of course that the entities the DbContext holds on to might be in an invalid state (because this could be the reason that the SQL exception was thrown).

Here's an example of a DbContext that is suppied by an opened SQL connection and transaction:

using (var connection = new SqlConnection("my connection"))
{
    connection.Open();

    using (var transaction = connection.BeginTransaction())
    {
        using (var context = new DbContext(connection))
        {
            // Do useful stuff.

            context.SaveChanges();
        }

        transaction.Commit();
    }
}

If you supply the DbContext with a SqlConnection that runs in the context of a transaction, this answer holds.

Note that Entity Framework will not create a nested transaction. It simply checks whether the connection is "enlisted in user transaction". If SaveChanges already runs in a transaction, no transaction is started. Entity Framework however is unable to detect if the database has aborted the transaction because of a severe failure (such as a database deadlock). So if a first call to SaveChanges fails with something like a deadlock and you catch and recall SaveChanges, Entity Framework still thinks it is running inside a transaction.

This means that this second call is executed without a transaction and this means that when the operation fails halfway, the already executed statements will NOT be rolled back since there is no transaction to rollback.

The problem of the torn SaveChanges operation could have been prevented if Entity Framework used nested transactions, but it still wouldn't solve the general problem of consistency.

Entity Framework creates connections and transactions for us when we do not supply them explicitly. We only need/want to supply a connection and transaction explicitly when the call to SaveChanges is part of a bigger overall transaction. So even if EF created a nested transaction for us and committed this before returning from SaveChanges, we're in trouble if we call SaveChanges a second time, since this 'nested' transaction actually isn't nested at all. When EF commits this 'nested' transaction, it actually commits the only transaction there is, which means that the entire operation we needed to be atomic is torn; all changes done by SaveChanges are committed, while the operations that might came after this call didn't run. Obviously this is not a good place to be.

So moral of the story is that either you let Entity Framework handle connections and transactions for you and you can redo calls to SaveChanges without risk, or you handle transactions yourself and will have to fail fast when the database throws an exception; you shouldn't call SaveChanges again.

Community
  • 1
  • 1
Steven
  • 166,672
  • 24
  • 332
  • 435
  • Can you clarify what you mean by "supply the `DbContext` with a `SqlConnection` that runs in the context of a transaction". My understanding is that `SaveChanges` creates a transaction internally ( http://stackoverflow.com/a/6028691/150342 ). So I would have thought that calling it again could not mean that the database operation would be "executed in a transactionless context" – Colin Oct 01 '13 at 13:00
  • Aaaah...OK....now, does the call to `SaveChanges` result in a nested transaction? Then that will rollback if there is a deadlock. And then you can catch the exception and retry `SaveChanges` - new nested transaction - no need to worry about being in a transactionless context? – Colin Oct 01 '13 at 13:41
  • I tried to test this but I got an exception as soon as I called SaveChanges. In EF5 you cannot supply the DbContext with an already opened SQL connection so there is no way to begin a transaction. EF6 has introduced finer grained control http://msdn.com/data/dn456843 – Colin Oct 03 '13 at 03:35
  • The [DbContext](http://msdn.microsoft.com/en-us/library/system.data.entity.dbcontext%28v=vs.103%29.aspx) class contains a [constructor](http://msdn.microsoft.com/en-us/library/gg696604%28v=vs.103%29.aspx) that takes in an existing connection. Although I never tested this, I find it unlikely that this wouldn't work on `DbContext` while this does work on `ObjectContext`. – Steven Oct 03 '13 at 07:05
  • Unlikely, perhaps, but according to the page I linked to it is so - and here's more corroboration: http://stackoverflow.com/a/9776093/150342 and http://blogs.msdn.com/b/diego/archive/2012/01/26/exception-from-dbcontext-api-entityconnection-can-only-be-constructed-with-a-closed-dbconnection.aspx EF6 is the way to go if you want to use transactions instead of TransactionScope – Colin Oct 03 '13 at 07:50
  • @Colin: You seem to be right. The linked document says: "it threw an exception if it was passed a connection that was already open". I assumes that EF worked the same as LINQ to SQL that allowed this from the start. As the document says, for EF you need a TransactionScope. I think you'll be able to get the same behavior using a TransactionScope (which means: catching an exception might cause the transaction to be gone, but the connection to stay open), but you'll have to check this. – Steven Oct 03 '13 at 08:07
  • So will it work fine if I pass in a closed connection but let Entity Framework handle the transactions? – Sam Oct 06 '13 at 23:03
  • @Sam it depends on your definition of 'work fine'. Your DbContext will obviously not be part of a bigger transaction. I'm not sure where passing in a closed connection is useful for. – Steven Oct 07 '13 at 05:39
  • By 'work fine', I mean 'allow me to run queries or commit changes even if a DB error such as a deadlock has occurred in the `DbContext`'. – Sam Oct 07 '13 at 05:42
  • 1
    I think passing in a closed connection is useful for preventing a transaction from being escalated to a distributed transaction, which occurs when the `DbContext` starts a second connection during a transaction. – Sam Oct 07 '13 at 05:43
  • Note that you typically don't have to pass in the same DbConnection to prevent escalating (when working with SQL Server 2008 and up) as can be read [here](http://pieterderycke.wordpress.com/2012/01/22/transactionscope-transaction-escalation-behavior/). – Steven Oct 08 '13 at 15:19