This article explains how to implement optimistic concurrency checking in the Entity Framework 4.0, using a SQL Server Timestamp column. But you could have derived that from its title.
What is a Timestamp?
Despite its name, the SQL Server Timestamp data type has nothing to do with time. DateTime2 on the other hand, is DateTime too [sorry, I couldn't resist that]. A timestamp is just an eight-bytes binary number that indicates the relative sequence in which data modifications took place in a database. The value in a column of the type Timestamp is always provided by SQL Server: it is calculated when a row is inserted, and augmented with each update to the row, to a ever increasing value that is unique in the whole database. The Timestamp data type was initially conceived for assisting in recovery operations, and you also use it to synchronize distributed databases: based on the timestamp you can detect the order in which data was added or updated, and replay a sequence of modifications. But the most common usage of timestamp is optimistic concurrency checking: when updating a row you can compare its current timestamp with the one you fetched originally. If the values are different, you know that someone else updated the row behind your back. And you know this without holding any locks on the server while you were busy, so its a very scalable solution. This type of concurrency checking is called 'optimistic': you assume that in most cases you will be able to successfully update without the need for conflict resolution.
A sample of the geeky kind
Let's create a table with such a column (the scripts-folder in the provided solution at the end of this article contains a full script):
CREATE TABLE [dbo].[FerengiRule](
[ID] [int] NOT NULL,
[Text] [nvarchar](100) NOT NULL,
[Source] [nvarchar](100) NOT NULL,
[Timestamp] [timestamp] NOT NULL,
CONSTRAINT [PK_FerengiRule] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
Populate it with your favorite 'Ferengi Rules of Acquisition'. You find all of these here. In a WPF solution, create an entity model, and add the table to it. You see that the timestamp column has
- Fixed as value for the Concurrency Mode property, so the column will appear in the WHERE-clause of any insert, update, or delete query, and
- Computed as value for the StoreGeneratedPattern property, so a new value is expected from the server after insert or update.
Next, build an application with a fancy transparent startup screen, that allows you to open multiple edit windows on the same data. The startup screen could look like this:
The mainwindow of the application contains just an editable grid on the table's contents. It allows you to
- set the concurrency resolution mode,
- upload local modifications to the database,
- get the current server data, and last but not least
- restore the table to its original contents (that's an extremely useful feature in this type of application).
Here's how the window looks like:
Visualizing a Timestamp value
Just like GUIDs and technical keys, you should avoid showing timestamp values on the user interface. This demo is an exceptional to this general rule, so I built a TimestampToDouble converter to translate the eight-byte binary number to something more readable. I don't guarantee a readable output on an old active database where the timestamp value is very high, but it works fine for a demo on a fresh database:
public class SqlTimestampToDoubleConverter: IValueConverter
{
public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
{
if (value == null)
{
return null;
}
byte[] bytes = value as byte[];
double result = 0;
double inter = 0;
for (int i = 0; i < bytes.Length; i++)
{
inter = System.Convert.ToDouble(bytes[i]);
inter = inter * Math.Pow(2, ((7 - i) * 8));
result += inter;
}
return result;
}
public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
{
throw new NotImplementedException();
}
}
Concurrency
Generated SQL Queries
If you run your SQL Profiler while modifying data through via the main window, you'll see that the Entity Framework query's WHERE-clause contains the timestamp, and that after the update the new value is fetched for you:
If there's no row to be updated, then someone else must have modified (or deleted) it. You can test that very easily with the sample application by opening multiple edit windows and playing around with the data.
Client side conflict resolution
When the entity framework discovers a concurrency violation when saving your changes, it appropriately throws an OptimisticConcurrencyException. It's then time for you to solve the conflict. In most cases, that means fetching the current values, sending these to the GUI, and let the end user decide what should happen. The data access layer code for inserts and updates will look like this:
foreach (var rule in this.rules)
{
try
{
switch (rule.ChangeTracker.State)
{
case ObjectState.Added:
entities.FerengiRules.AddObject(rule);
entities.SaveChanges();
break;
case ObjectState.Modified:
entities.FerengiRules.ApplyChanges(rule);
entities.SaveChanges();
break;
default:
break;
}
}
catch (OptimisticConcurrencyException)
{
// Return Current Values
// ...
}
}
Server side conflict resolution
The Entity Framework also provides automated conflict resolution strategies that might be useful in some scenarios (although to be honest: I can't think of any). There's a Refresh method that you can use to decide whether it's the client version or the server (store) version that should be persisted when there's a conflict. Here's how the catch-block could look like:
catch (OptimisticConcurrencyException)
{
switch (this.conflictResolution)
{
case ConflictResolution.Default:
throw;
case ConflictResolution.ServerWins:
entities.Refresh(RefreshMode.StoreWins, rule);
entities.SaveChanges();
break;
case ConflictResolution.ClientWins:
entities.Refresh(RefreshMode.ClientWins, rule);
entities.SaveChanges();
break;
default:
break;
}
}
Source Code
Oops ... almost forgot: here's the source code of the whole thing: U2UConsult.EF40.OptimisticConcurrency.Sample.zip (470,96 kb)
Enjoy!