Tuesday, August 23, 2011

Managing Transactions with Entity Framework 4

The Entity Framework already supports native database transactions. When using the ObjectContext.SaveChanges method, it already operates within a database transaction. If any dirty ObjectStateEntry object cannot be persisted, then ObjectContext.SaveChanges will roll back the transaction and throw an exception. So, if you are working with only one object context then you have already built-in support for database transactions when using the ObjectContext.SaveChanges method.

However, there are other complex scenarios where you want to update a record in a database and add an entry to a message queue; or add, update, or delete records in different databases. In this situation, if you need these operations to be ACID, then you will need a distributed transaction that spans multiple databases and systems. A distributed transaction is a kind of transaction that requires the enlistment of additional resource managers.

If you have a complex persistence scenario as mentioned above, then you can use the TransactionScope class. This class uses the Microsoft Distributed Transaction Coordinator (MSDTC or DTC) which is a Windows service that coordinate transactions over different resource managers across multiple computers. Using MSDTC is an expensive task, however TransactionScope only uses MSDTC if it is necessary. If multiple instances of ObjectContext connect to the same database, then it uses a standard database transaction (SqlTransaction, OleDbTransaction). If there are multiple databases to be connected, then the transaction is promoted to MSDTC. If you are using SQL Server, transaction promotions in SQL Server 2008 happens less frequently than in SQL Server 2005, so SQL Server 2008 is more efficient in this matter. You can use the SQL Profiler tool to monitor how many transactions are promoted to MSDTC.

When using TransactionScope with multiple instances of ObjectContext, you should not use the parameterless ObjectContext.SaveChanges() method. Instead, you should use the ObjectContext.SaveChanges(SaveOptions) method and pass SaveOptions.DetectChangesBeforeSave. The reason is to maintain the ObjectContext state if something fails, otherwise the ObjectContext will be in a bad state, i.e., thinking that the changes were successfully committed when they were actually rolled back. When all operations within the transaction are successfully completed, then you should call the TransactionScope.Complete method, and after that it is safe to call ObjectContext.AcceptAllChanges().

Below you can see an example based on this post, but using the SaveChanges(SaveOptions) method instead of the obsolete SaveOptions(Boolean):

using (TransactionScope scope = new TransactionScope())
{
    // Save changes but maintain context1 current state.
    context1.SaveChanges(SaveOptions.DetectChangesBeforeSave);

    // Save changes but maintain context2 current state.
    context2.SaveChanges(SaveOptions.DetectChangesBeforeSave);

    // Commit succeeded since we got here, then completes the transaction.
    scope.Complete();

    // Now it is safe to update context state.
    context1.AcceptAllChanges();
    context2.AcceptAllChanges();
}

That is, the example above updates two databases within a distributed transaction and ObjectContext instances are only updated if the transaction is successfully committed. For an example of a transaction fail-retry scenario and using a database and a message queue, see the following link: http://msdn.microsoft.com/en-us/library/bb738523.aspx

3 comments:

Mike said...

Thankyou Ive been trying to work this out.

Anonymous said...

Good stuff Luis, thanks for sharing

Stefan McCallum said...

Simple and concise.

Thank you Luis.

Spring Boot Configuration Properties Localization

Spring Boot allows to externalize application configuration by using properties files or YAML files. Spring Profiles  provide a way to segr...