Handle Race Conditions / Concurrency in Code First Entity Framework Applications

Preface

A common scenario in developing applications is the handling of concurrent database updates.

Given there is an application to manage customers, that is used by multiple users simultaneous.

User U1 reads the data of customer C1. While user U1 is looking at this data, user U2 reads the data of customer C1 too. Meanwhile, user U1’s phone is ringing. She picks it up. User U2 changes some of the customer’s data and saves it to the database. User U1 finishes the phone call, make some changes to the same customer, and saves it too.

Without handling the race condition or concurrency, al changes made by user U2 will be lost in this example. Handling it, the application should not save the changes made by user U1 and inform her that the customer’s data was changed in the meantime by another user.

In this post I will show an approach and sample code on how an application using the Entity Framework and the Code First approach can handle this.

Code Preparation

There is only one step required to make Entity Frame do all the work for you: Define a Version property in the model class (the name of the property does not matter at all), and decorate it with a Timestamp attribute.

/// <summary>
/// This column will be used by EF for race condition validation.
/// </summary>
[Timestamp]
public byte[] Version { get; set; }

Believe it or not – this is all you have to do (in case you are using the Code First approach).

Entity Framework will set the Version property to a value when the record is inserted into the database. Every time EF updates the record, it verifies that the value of the column has not changed. Doing an update, the version is increased.

Handling a Race Condition

EF throws a DbUpdateConcurrencyException in case a record was changed since the data was read.

So you have to catch this exception whenever your application updates or deletes database records and inform the user that the data was changed. In case there is no user to be informed, e.g. if this happens in a background process, you have to implement an appropriate error handling mechanism.

Using the Sample

To see EF Concurrency Handling in action, you can use the sample application.

There are two “clients” implemented, accessing both the same database record. Take these steps to provoke a concurrency exception:

  • Read the record for client 1 (press the Read Record button of the Client 1 group)
  • Read the record for client 2 (press the Read Record button of the Client 2 group)
  • Update the record for client 2 (press the Update Record button of the Client 2 group)
  • Try to update the record for client 1 (press the Update Record button of the Client 1 group)

The result should look like this:

Sample when Race Condition occurred

Some Sample Details

The sample code was created using Visual Studio 2013 Ultimate and Entity Framework 6.1. In case you use an older version of Visual Studio, you might have to create an empty solution and add the files to it.

The application expects a SQL Server instance (not SQL Server Express) installed on the local machine having the default server name (MSSQLSERVER). In case you do not have a SQL Server with this name running on your machine, you have to change the connection string in the app.config file before you can run the sample.

On the SQL Server, the application creates a database named EntityFrameworkRaceCondition. Please make sure to have the appropriate rights to create a database when you run the sample.

Links

Microsoft Data Developer Center Entity Framework

Entity Framework / Get Started / Code First to a New Database

Entity Framework Tutorial Update Entity Using DbContext

Wikipedia Race Condition

Sample Application

Create a SQL Azure Database using SSDT

To get into Microsoft Azure application development, I was playing around with SQL Server Data Tools (SSDT), trying to create a database on SQL Azure.

I started creating a simple database, having two tables with a few columns. As done before with other tools, I was using the extended properties to document the database ‘inline’. The target platform was my local SQL 2012 instance. Deployment went fine, updating after some changes as well.

So the SQL Azure database server was created quickly, and I tried to deploy that database from my local machine to SQL Azure. It failed – of course (I wouldn’t have written a post just to tell you I succeeded 😉 ).

The reason surprised me, but a solution was found very quickly.

The reason why publishing failed is, that, beside others, SQL Azure does not support extended properties.

The solution is posted by Bill Gibson on the SQL Server Data Tools Team Blog in his post Migrating a Database to SQL Azure using SSDT.

I created a Schema Compare file as suggested by Bill, and put a second SQL Server project into my solution, following his instructions – and it worked fine 🙂

Now I have two database projects in my solution. The first one with full featured extended properties to document the database by itself. This one is used to build and update the local SQL 2012 version of the database. And the second one, based on Schema Compare, to be able to convert the SQL 2012 version into a SQL Azure compatible one and build and update the instance on SQL Azure. Of course, one have to take care not to update the scripts of the SQL Azure version, but keep in mind that SQL 2012 is the master.

This works smooth and easy in my simple test environment, because I do not use such things as user defined types (CLR) or XML indexes. Both are also not supported by SQL Azure. And migrating an existing database which uses these feature is not just letting Schema Compare doing the work. In such a case, one has to do some re-work, or probably re-think the decision to move to SQL Azure.

Btw, I think it’s worth to mention that when excluding the Default objects from schema comparison, this does not mean that column default values created by the CREATE or ALTER TABLE statement will be ignored. It seems that only those defaults created by CREATE DEFAULT are ignored. To me this is OK, since the SQL Server books say that CREATE DEFAULT will be removed in future versions of SQL Server anyway.

machine.config Overrides TransactionScope / TransactionOptions.Timeout

You are wondering why your DB-transaction, startet by

using(TransactionScope scope = new TransactionScope(timeout))

runs into a timeout after 10 minutes?

Because the

<machineSettings maxTimeout="00:10:00"/>

in the system.transactions section of the machine.config has a default value of 10 minutes. And whatever is set as timeout of the TransactionScope by code – machine.config overrides it all.

So you have to change the machine.config, which does not contain a system.transaction section by default – which doesn’t mean the timeout default value does not exist.

And as long as you hadn’t set a timeout in the code, you don’t have to change any line. The new value of the machine.config is used when the process starts. No machine reboot is required.

Btw: You will find the machine config in %install path%/Config. Which is usually c:\windows\Microsoft .NET\Framework\V…\Config. On x64 machines, there is a Framework64 directory as well. My suggestion is to change all machine.config files to make sure every environment behaves in the same way.

The source of my knowledge: http://technet.microsoft.com/en-us/library/ms149852(VS.90).aspx

SQL Server Isolation Level Snapshot & DTC Promotion

SQL Server transaction isolation level Snapshot cannot be promoted by DTC.

In case you open a transaction scope in .NET code, using isolation level snapshot, then open a connection, leave that connection open, and try to open another connection, you will retrieve an exception saying that a snapshot transaction cannot be promoted.

Well, in this case, you didn’t wanted to promote that transaction. Using the snapshot isolation level, just make sure only one connection is open at a time, and no exception will occur 🙂

XML Data Row Truncation At 2,033 Chars When Using SqlDataReader

When you read Extensible Markup Language (XML) data from Microsoft SQL Server by using the SqlDataReader object, the XML in the first column of the first row is truncated at 2,033 characters. You expect all of the contents of the XML data to be contained in a single row and column.

This behavior occurs because, for XML results greater than 2,033 characters in length, SQL Server returns the XML in multiple rows of 2,033 characters each.

To resolve this problem, use the ExecuteXmlReader method to read FOR XML queries. For additional information about how to use ExecuteXmlReader with SQL Server FOR XML queries, click the article numbers below to view the articles in the Microsoft Knowledge Base:
316016  (http://support.microsoft.com/kb/316016/ ) How to use the ExecuteXmlReader method of the SqlCommand class in Visual Basic .NET

316701  (http://support.microsoft.com/kb/316701/ ) How to use the ExecuteXmlReader method of the SqlCommand class in Visual C# .NET

307224  (http://support.microsoft.com/kb/307224/ ) How to use XML in connected and disconnected ADO.NET application

or: Use SqlDataReader an iterate through all the lines.