Conflict Resolution

Resolving Conflicts Using LINQ to SQL

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).

/// 

 /// shows how linq to sql returns the same object

 /// regardless of different where clause.

 /// 

 public static void ObjectTrackingInLinqToSql()

 {

   NorthWindDataContext db = new NorthWindDataContext();

   db.Log = Console.Out;

   Customer c1 = db.Customers.Where(c => c.ContactName ==

     "Maria Anders").First();

   Customer c2 = db.Customers.Where(c => c.CustomerID ==

     "ALFKI").First();

   if (c1 == c2)

   {

     Console.WriteLine("c1 customer is same as

                       c2 customer");

   }

 }

Figure 1: Object tracking in LINQ to SQL.

 


Figure 2: Results from Figure 1 display showing both objects have the same reference.

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).

Change Tracking

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.

[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"));

       }

     }

   }

 }

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.

 

Raising Conflicts

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.

/// 

/// example illustrates how we can cause change conflicts.

/// 

public static void CausingConflicts()

{

 NorthWindDataContext context1 = new NorthWindDataContext();

 context1.Log = Console.Out;

 NorthWindDataContext context2 = new NorthWindDataContext();

 context2.Log = Console.Out;

 Supplier supcontext1 = context1.Suppliers.Single(s =>

   s.CompanyName == "Exotic Liquids");

 Supplier supcontext2 = context2.Suppliers.Single(s =>

   s.CompanyName == "Exotic Liquids");

 // change the contact title to Purchasing Manager 1

 supcontext1.ContactTitle += " context1";

 context1.SubmitChanges();

 supcontext2.ContactTitle = " context2";

 // will raise an exception since the original value no

 // longer matches what's in the database.

 context2.SubmitChanges();

}

Figure 4: Code showing an exception caused by change conflict.

 


Figure 5: Result of code (in Figure 4) showing 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).

[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 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).


Figure 7: Added IsVersion column for concurrency.

 

Resolving Conflicts

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:

select supplierid,companyname,Country from suppliers

 where country = France 

 


Figure 8: Result of a select query for suppliers in France.

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.

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 twosuppliers = db2.Suppliers.Where(s =>

     s.Country == "France").Skip(1).ToList();

   foreach (Supplier supp in twosuppliers)

 {

     supp.Country = "UK";

     Console.WriteLine("Modifying Country for supplier" +

       supp.CompanyName + " to UK");

 }

   db2.SubmitChanges();

   //now change the country for the customers from first

   //datacontext to USA and call submit changes.

   foreach (Supplier supplier in suppliers)

 {

     supplier.Country = "USA";

 }

 try

 {

     db.SubmitChanges(ConflictMode.FailOnFirstConflict);

 }

 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 9: Code illustrating batch conflicts.

 


Figure 10: Batch conflict results.

 

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).

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 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).


Figure 12: Example showing ContinueOnConflict.

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

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:

  • RefreshMode.KeepCurrentValues: Discards original values with what s defined in the database.
  • RefreshMode.KeepChanges: Doesn t touch what you modified, but the rest of the values are refreshed from the database.
  • RefreshMode.OverwriteCurrentValues: Refreshes all the column values from the database regardless of if they ve been modified. You basically lose all the changes you made.

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).

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 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.


Figure 14: Example showing use of Refresh to resolve conflicts.

 

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).

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);

 }

Figure 15: Resolving conflicts using ResolveAll.

 

try

 {

   db.SubmitChanges(ConflictMode.ContinueOnConflict);

 }

 catch (ChangeConflictException)

 {

   foreach (ObjectChangeConflict conflict

            in db.ChangeConflicts)

   {

     conflict.Resolve(RefreshMode.KeepCurrentValues);

   }

 }

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).

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);

     }

   }

 }

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.

 

Conclusion

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].

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish