Concurrency conflicts occur when a user attempts to update
data that has been changed by another user since the last time a record was
read. The problem is a result of working in a disconnected scenario. There are
no locks placed on data during the read operation. Once you ve closed your
connection and start to modify your object, there is no guarantee that the data
will be the same in the database and that some other user has not modified that
record. This leads to optimistic concurrency. Traditionally it has been left to
the application developer to detect and resolve conflicts in the most
appropriate manner. WithLINQ
to
SQL
, Microsoft provides numerous ways to
notify the application about optimistic concurrency, and provides fairly non-trivial
ways to resolve those conflicts, as well.
The first time you make a request for an object using LINQ
to SQL, it logs the object in an identity management service based on its
primary key, so the next time you request the object, LINQ to SQL checks its
internal cache to see if the object has been retrieved; if the object exists in
its cache, it will return the same object instance to the client. This means
that if the data has changed in the database since your last retrieval, you won t
receive updated data, which leads to data mismatch between the version that is
returned to the client and the version that is stored in the database. An important point to consider is, depending on how you
set up your query, LINQ to SQL may actually go to the database and run the
query but give you the same instance of the object you originally had. If your
query uses a single operator and searches based on a primary key, LINQ to SQL
won t even hit the database and will simply return the object from its tracking
repository. Because of this specific behavior, you essentially get the same
object on which you ve made changes as compared to getting a fresh copy, which
could potentially have new data written by some other user. LINQ to SQL
delegates to DataContext the responsibility for managing the identity of the
objects retrieved from the database. From the first fetch of the object, DataContext
starts tracking the changes you are making to the object to the point you call SubmitChanges,
after which it discards all the tracking information about the object from its
identity management service. DataContext will throw a change conflict exception
when you call SubmitChanges if the original values it has in its identity
management service does not match the current value in the database. Let s take
a look at an example of object tracking (see Figures 1 and 2). Figure 1: Object tracking
in LINQ to SQL. In this example we first retrieve the customer using the contact
name. At this point, LINQ has started tracking the object using its primary
key. The second time around, when we query for the same customer using its
primary key, DataContext goes to the database, returns the record, then checks
its identity management to find out if it has been tracking this new object
based on its primary key. If it finds the object in its repository, it returns
the old object (which the user has modified). This is the reason you get the
modified version of the country (instead of Germany, which is what is defined
in the database). To fully understand how conflicts are resolved and raised
in LINQ to SQL, it is essential to understand how DataContext gets notified
when you change a particular property of an object. You can implement the INotifyPropertyChanging
and INotifyPropertyChanged interfaces on your domain entity and raise PropertyChanging
and PropertyChanged events in the setter of your property to notify the
identity management service that you are changing the property. If you don t
implement this interface, DataContext will manage two copies of the same
object, one with the original value and a second with the changed value. Based
on the changes notified to the context, LINQ to SQL will generate the
appropriate update statement, which would only include columns that have
changed. This option may be expensive in a situation when you are retrieving
lots of objects and DataContext must manage two copies of the same object. In
those scenarios it is preferable to implement the INotifyChanging and INotifyChanged
interfaces and simply notify the object tracking service that you have changed
the property instead of maintaining two copies of the object. If you are using the LINQ to SQL designer, you don t have
to worry about implementing those interfaces because the Object Relational (O/R)
Designer takes care of handing all the plumbing details for you. If all you re
doing is retrieving objects and you don t intend to send changes back to the
database, you have the option to completely disable object tracking by setting the
ObjectTrackingEnabled property to false on the DataContext. Let s look at some
generated code from the Category class to see how the O/R Designer takes care
of object tracking (see Figure 3). I ve taken out some additional code so we
can concentrate on the tracking feature. Figure 3: Category-generated
class implementing the Notify interfaces. Notice that we are implementing the INotifyPropertyChanging
and INotifyPropertyChanged interfaces, which allows us to notify the object
tracking service when we change a property. We declare two events: PropertyChanging
and PropertyChanged. The first time DataContext starts tracking the object it
will register with these events. The next time I change the property, I raise
the Changing and then a Changed event after the property is changed. In
addition, I am passing in the PropertyChanged event the property name that is
changed, which tells DataContext which property is being changed. So far we ve discussed how DataContext tracks the object;
we have not discussed what causes LINQ to SQL to throw a change conflict
exception, nor how it knows that the value in the database has changed since
the last retrieval. Figures 4 and 5 illustrate this concept with a simple
example. Figure 4: Code
showing an exception caused by change conflict. You ll get an exception when you run the code from Figure
4, because context1 has modified and updated the data in the database and
context2 s original values no longer match what s in the database. You may be
wondering how LINQ to SQL was able to figure out that data has changed in the
database when it sent the update. It s because of the UpdateCheck attribute
defined on the Supplier object. The UpdateCheck attribute determines how DataContext is
going to ensure optimistic concurrency. UpdateCheck is an enum that can have three
values: Never, Always, and WhenChanged. Basically, all the columns that are
marked as Always will participate as the Where clause using their original
values tracked by the identity management service. Typically, if one of the columns
is defined with an attribute of IsVersion that is set to true, then DataContext
will only use that column to perform optimistic concurrency by appending that
column in the Where clause of the update statement. If there is no column in
entity that is defined with the IsVersion attribute, then, by default, all
columns will participate in optimistic concurrency and they would default to
UpdateCheck.Always. You can turn off conflict detection by setting UpdateCheck
to Never on each member, which would force DataContext to put anything but the
primary key in the Where clause (which would cause the last record to always
win). So, when an update fails to affect any rows, LINQ to SQL throws a change
conflict exception. Notice the update SQL statement includes all the columns in
the Where clause because, by default, all the columns have their UpdateMode set
to Always (as there is no column marked as IsVersion). If we were to add a new
column to the suppliers table and set an attribute on the column as IsVersion, LINQ
to SQL will only use that column in the Where clause to ensure optimistic
concurrency (see Figure 6). Figure 6: Add a
new column and set the attribute to IsVersion and LINQ to SQL will only use that
column in the Where clause to ensure optimistic concurrency. Notice we added a new column named IsVersion and marked it
as IsVersion (see Figure 7). The result is the same and we get a change
conflict exception but this time our Where clause includes the IsVersion
column only (along with the primary key column that we expect). SubmitChanges has two overloads you can use to specify how
DataContext will react to change conflicts. The first, FailOnFirstConflict,
means that as soon as DataContext encounters a change conflict, it will throw
an exception and the entire transaction will be rolled back. The second option,
ContinueOnConflict, means DataContext will continue to process all the entities
while building a list of conflicted objects. Once it s done processing, it will
throw a change conflict exception and also roll back the entire transaction.
When you call SubmitChanges, DataContext will look for an existing transaction
it can join. If there is none, it will create a new transaction and all the
changes will be made in that transaction. So, then, it is up to the developer
to resolve the conflicts and resubmit the entire batch to the database again.
Let s look at FailOnFirstConflict first to see how we can resolve the conflict. The result of this simple query is shown in Figure 8: Looking at Figure 9, notice we are using two DataContexts.
Using the first DataContext we retrieve three suppliers from France, then we
print them (as shown in Figure 10). Using the second DataContext we retrieve
the last two suppliers from France and modify their country to USA. Figure 9: Code
illustrating batch conflicts. The first DataContext is not aware we made changes to the
country (to USA) using the second DataContext. Hence, its original value will
not match what is now defined in the country column in the database. Therefore,
when the first DataContext tries to change the country column to UK, it throws a
change conflict exception. In this case I m using an overloaded version of SubmitChanges
with ConflictMode set to FailOnFirstConflict. Calling SubmitChanges with no
parameter is the same as FailOnFirstConflict because that s what it defaults
to. We then use the DataContext s ChangeConflict property to access all the
objects in conflict. Because we are failing the entire batch on the first
failure, our ChangeConflict contains only the first failure even though there
were two suppliers in conflict. We are also retrieving three values of the country. As
mentioned earlier, LINQ to SQL uses a tracking service on the objects it
retrieves from the database. So the original value is basically the value that the
tracking service originally had when it retrieved the object the first time. After
we changed the country to USA, that became the current value and the value in
the database is what is defined in the database at the time SubmitChanges gets
called. It s important to understand that a single conflict could be caused by
more than one member of an entity conflicting with columns in the database.
However, it is still considered one conflict. Let s run the same example, but this time we ll change
ConflictMode to ContinueOnConflict (see Figure 11). Figure 11: Example
catching all exceptions by using ContinueOnConflict. I m not showing the entire code in Figure 12 because
everything is the same except the SubmitChanges is called with ContinueOnConflict.
Because of this, DataContext continues to process the rows; in the end, all the
conflicts are present inside the context s change conflict collection, as shown
in Figure 12. For the entire code, look at the ContinueOnConflict method in the
download accompanying this article (see end of article for download details). Earlier in this article we saw ways of catching a change
conflict exception by using overloaded versions of SubmitChanges. Now we ll
explore how we can resolve those conflicts we captured. Because a major part of
the code will be the same as earlier, I ll simply show you the code snippet
that s relevant to the context. The first method of conflict resolution we ll explore
is Refresh. Refresh is one of the methods available on the DataContext;
it allows you to refresh your existing objects with new changes from the
database. Refresh has three overloaded methods that allow you to refresh a single
entity or the entire collection. The first parameter of the Refresh method is
RefreshMode, an enum with three values: The way you can use the Refresh method is by preventing
change conflicts in the first place. Just before you call SubmitChanges you can
refresh your objects from the database. Therefore, when you call SubmitChanges,
DataContext will not throw any exception. Let s modify our existing example to
illustrate the behavior (see Figure 13). Figure 13: Refresh
objects from the database before you call SubmitChanges. When you call SubmitChanges,
DataContext will not throw an exception. From the result shown in Figure 14, notice that we didn t
get any exception because we refreshed our original values from the database
right before we called SubmitChanges. For the entire code see the
CallingRefresh method in the accompanying download. We ll now explore how to
resolve change conflicts when an exception is thrown. In Figure 15, we are telling DataContext to continue on
conflict so after all the objects have been processed, DataContext throws an
exception and we simply call ResolveAll on the change conflicts to resolve all
the conflicts, which in this case happens to be two conflicts. This option is
the most flexible in the sense that if you don t have a specific way of
resolving conflicts for each individual object that is in conflict, calling ResolveAll
solves most of the problems. If you like to handle each conflict object
differently, we can loop through the change conflicts and resolve each of them
separately by calling Resolve on ObjectChangeConflict (see Figure 16). Figure 15:
Resolving conflicts using ResolveAll. Figure 16: Resolving
conflicts using ObjectChangeConflict. In Figure 16 we loop through each change conflict and
resolve them separately. We also could have checked some property on an object,
then determined on a per-object basis which refresh mode we wanted to apply.
For the complete code of the example shown in Figure 16, look at the
ResolvingChangeConflictsPerObject method in the accompanying download.
DataContext also provides a more granular level in terms of solving concurrency
issues. Say, for example, a particular object comes under conflict because
there are properties on the object that don t match with what s defined in the
database. In a scenario like that where you want to resolve conflicts
differently for each column that is in conflict in the conflicted object we
can use the MemberChangeConflict property (see Figure 17). Figure 17:
Resolving conflicts at the member level. Notice that at this granular level we can determine
exactly which member was in conflict, as well as resolve the conflict at the
member level using a different RefreshMode. Not only can you use RefreshMode,
but there s also another overload that takes in the actual value so you can
basically specify an exact value that will be used as the current value. In this article we talked about how concurrency problems can
lead to conflict exception when we are trying to persist our objects in the database.
We also learned how LINQ to SQL uses the UpdateCheck attribute to ensure
optimistic concurrency in update statements, and how LINQ to SQL provides
different methods to resolve conflicts, from the entire batch to a single
object to resolving conflicts at the member level. Source code
accompanying this article is available for download. Zeeshan
Hirani is a senior developer at http://CheaperThanDirt.com. He specializes
mainly in ASP.NET, AJAX, and leveraging OR-mapping
solutions like LINQ in business applications. He has written several articles
for http://CodeProject.com and is an
active member in the user-group community in Dallas/Fort Worth. He is a
frequent blogger. You can find his blog at http://weblogs.asp.net/zeeshanhirani.
Contact him with any questions at mailto:[email protected]. ///
Figure 2: Results from Figure 1
display showing both objects have the same reference. Change Tracking
[Table(Name = "dbo.Categories")]
public partial class Category : INotifyPropertyChanging,
INotifyPropertyChanged
{
private static PropertyChangingEventArgs
emptyChangingEventArgs = new
PropertyChangingEventArgs(String.Empty);
public event PropertyChangingEventHandler PropertyChanging;
public event PropertyChangedEventHandler PropertyChanged;
private string _CategoryName;
[Column(Storage = "_CategoryName", DbType = "NVarChar(15)
NOT NULL", CanBeNull = false)]
public string CategoryName
{
get
{
return this._CategoryName;
}
set
{
if ((this._CategoryName != value))
{
PropertyChanging(this, emptyChangingEventArgs);
this._CategoryName = value;
PropertyChanged(this, new
PropertyChangedEventArgs("CategoryName"));
}
}
}
}
Raising Conflicts
///
Figure 5: Result of code (in Figure
4) showing exception caused by change conflict.[Column(Storage="_IsVersion", AutoSync=AutoSync.Always,
DbType="rowversion", IsDbGenerated=true, IsVersion=true,
UpdateCheck=UpdateCheck.Never)]
public System.Data.Linq.Binary IsVersion
{
get
{
return this._IsVersion;
}
set
{
if ((this._IsVersion != value))
{
this.OnIsVersionChanging(value);
this.SendPropertyChanging();
this._IsVersion = value;
this.SendPropertyChanged("IsVersion");
this.OnIsVersionChanged();
}
}
}
Figure 7: Added IsVersion column for
concurrency. Resolving Conflicts
select supplierid,companyname,Country from suppliers
where country = France
Figure 8: Result of a select query
for suppliers in France.public static void CausingConflictInaBatch()
{
NorthWindDataContext db = new NorthWindDataContext();
NorthWindDataContext db2 = new NorthWindDataContext();
//this will retrieve 3 records
var suppliers = db.Suppliers.Where(s => s.Country ==
"France").ToList();
foreach (Supplier supplier in suppliers)
{
Console.WriteLine("Company: " + supplier.CompanyName +
" Country: " + supplier.Country);
}
//using the second datacontext to modify the country of the
//second supplier from France to USA and causing conflict
//for the first context
List
Figure 10: Batch conflict results.try
{
db.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException)
{
foreach (ObjectChangeConflict conflict in
db.ChangeConflicts)
{
Supplier s = conflict.Object as Supplier;
Console.WriteLine("Conflicting supplier {0}",
s.CompanyName);
foreach (MemberChangeConflict member in
conflict.MemberConflicts)
{
Console.WriteLine("Orignal Supplier Country {0}",
member.OriginalValue);
Console.WriteLine("Changed Supplier Country {0}",
member.CurrentValue);
Console.WriteLine("Supplier Country in Database {0}",
member.DatabaseValue);
}
}
}
db.SubmitChanges();
Figure 12: Example showing ContinueOnConflict. Refresh
try
{
db.Refresh(RefreshMode.KeepCurrentValues, suppliers);
db.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException)
{
foreach (ObjectChangeConflict conflict in
db.ChangeConflicts)
{
Supplier s = conflict.Object as Supplier;
Console.WriteLine("Conflicting supplier {0}",
s.CompanyName);
foreach (MemberChangeConflict member in
conflict.MemberConflicts)
{
Console.WriteLine("Orignal Supplier Country {0}",
member.OriginalValue);
Console.WriteLine("Changed Supplier Country {0}",
member.CurrentValue);
Console.WriteLine("Supplier Country in Database {0}",
member.DatabaseValue);
}
}
}
Figure 14: Example showing use of Refresh
to resolve conflicts.foreach (Supplier supplier in suppliers)
{
supplier.Country = "USA";
Console.WriteLine("Modifying Supplier {0} country to
{1} using hte first context",
supplier.CompanyName, supplier.Country);
}
try
{
db.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException)
{
db.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues);
}
try
{
db.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException)
{
foreach (ObjectChangeConflict conflict
in db.ChangeConflicts)
{
conflict.Resolve(RefreshMode.KeepCurrentValues);
}
}
try
{
db.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException)
{
foreach (ObjectChangeConflict conflict in
db.ChangeConflicts)
{
foreach (MemberChangeConflict member in
conflict.MemberConflicts)
{
Console.WriteLine("Member in conflict
{0}",member.Member.Name);
member.Resolve(RefreshMode.KeepCurrentValues);
}
}
}
Conclusion
Conflict Resolution
Resolving Conflicts Using LINQ to SQL
0 comments
Hide comments