LINQ to SQL - Concurrency Conflicts

Hi,

When one database connection attempts to update a piece of data that has been changed by another database connection since the record was read by the first database connection, a concurrency

conflict occurs. That is to say that if process one reads the data, followed by process two reading the same data, and process two updates that same data before process one can, a concurrency conflict occurs

when process one attempts to update the data.

There are two basic approaches for handling concurrency conflicts, Optimistic and Pessimistic.

Optimistic Concurrency

-          This approach is optimistic that most of the time, a concurrency conflict will not happen. Therefore, no locks will be placed on the data during a read of the database.

-          If there is a conflict when attempting to update that same data, we will address the conflict then.

-          Optimistic concurrency conflict handling is more complicated than pessimistic concurrency conflict handling, but works better for most modern day applications with very large scale quantities of users.

-          LINQ to SQL takes the optimistic concurrency conflict handling approach.

Conflict Detection

-          The first step is detecting the conflict.

-          LINQ to SQL has two approaches it uses to detect concurrency conflicts.

o         IsVersion Column

o        UpdateCheck property

§         Never

§         Always (Default)

§         WhenChanged

-          If the IsVersion Column attribute property is specified on an entity class property and its value is true, then the value of that entity class property, and that property alone, will be used to determine if a concurrency conflict occurred.

-          If no entity class property has an IsVersion attribute property set to true, LINQ to SQL allows you to control which entity class properties participate in concurrency conflict detection with the Column attribute UpdateCheck property specified on an entity class’s mapped property.

UpdateCheck

-          If UpdateCheck = UpdateCheck.Never -  that entity class property will not participate in concurrency conflict detection.

-          If UpdateCheck = UpdateCheck.Always, that entity class property will always participate in the concurrency conflict detection regardless of whether the property’s value has changed since initially being retrieved and cached by the DataContext.

-          If UpdateCheck = UpdateCheck.WhenChanged, that entity class property will participate in the update check only if its value has been changed since being loaded into the DataContext object’s cache

-          To understand how conflict detection technically works, it may help you to understand how it is currently implemented.

-          When you call the SubmitChanges method, the change processor generates the necessary SQL statements to persist all changes in the entity objects to the database.

-          When it needs to update a record, instead of merely supplying the record’s primary key in the where clause to find the appropriate record to update, it specifies the primary key, as well as potentially all columns participating in conflict detection.

-          If UpdateCheck = UpdateCheck.Always - Then that property’s mapped column and its original value will always be specified in the where clause.

-          If UpdateCheck = UpdateCheck.WhenChanged, then only if the entity object’s current value for a property has been changed from its original value will that property’s mapped column, and its original value be specified in the where clause.

-          If UpdateCheck = UpdateCheck.Never, then that entity class property’s mapped column will not be specified in the where clause.

-          For example, assume that the Customer entity object specifies the UpdateCheck property as below.

o        For CompanyName as UpdateCheck.Always,

o        ContactName as UpdateCheck.WhenChanged, and

o        ContactTitle as UpdateCheck.Never.

      If all three of those entity class properties were modified in the entity object for a customer, the generated SQL statement would look like this:

           

	Update Customers Set CompanyName = 'Art Sanders Park', ContactName = 'Samuel Arthur Sanders', ContactTitle = 'President' Where CompanyName = 'Lonesome Pine Restaurant' AND ContactName = 'Fran Wilson' AND CustomerID = 'LONEP'

-          When that SQL statement is executed, if any of the entity class properties’ values specified in the where clause do not match what is in the database, the record will not be found, so it will not get updated.This is how concurrency conflicts are detected. If a conflict occurs, a ChangeConflictException is thrown.

SubmitChanges()

-          As mentioned above - the concurrency conflict detection occurs when the SubmitChanges method is called.

-          Here you have the ability.

o        ConflictMode.FailOnFirstConflict  - Shouldd the process of saving the changes should abort on the first conflict that occurs. (Default)

o        ConflictMode. ContinueOnConflict  - Should it attempt all changes, collecting the conflicts.

-          Regardless of the ConflictMode you specify, if an ambient transaction is not in scope when the SubmitChanges method is called, a transaction will be created for all database changes attempting to be made during the invocation of the SubmitChanges method.

-          If an ambient transaction is in scope, the DataContext will enlist in the ambient transaction. If an exception is thrown during the SubmitChanges method call, the transaction will be rolled back. This means that even the unconflicted entity objects whose changes were successfully persisted to the database will be Rolled Back.

Conflict Resolution

-          Once you have detected the concurrency conflict by catching the ChangeConflictException, the next step is most likely to resolve any conflicts.

-          LINQ to SQL makes this by providing a ResolveAll and two Resolve methods.

1.       DataContext.ChangeConflicts.ResolveAll()

2.       ObjectChangeConflict.Resolve()

3.      MemberChangeConflict.Resolve()

-          These resolve methods determine how the conflict is resolved by specifying a RefreshMode.

o        RefreshMode.KeepChanges,

o        RefreshMode.KeepCurrentValues, and

o        RefreshMode.OverwriteCurrentValues.

-          The RefreshMode.KeepChanges –

o        Load the changes from the database into the entity class properties’ current value for any column changed since the data was initially loaded, unless the current user has also changed the property, in which case that value will be kept.

o        The order of priority of retaining the data, from lowest to highest, is; original entity class property values, reloaded changed database column values, current user’s changed entity class property values.

-          The RefreshMode.KeepCurrentValues  -

o        Keep the current user’s original entity class property values and changes, and to disregard any changes made to the database since the data was initially loaded.

o        The order of priority of retaining the data, from lowest to highest, is; original entity class property values, current user’s changed entity class property values.

-          The RefreshMode.OverwriteCurrentValues

o        Load the changes from the database for any columns changed since the data was initially loaded, and to disregard the current user’s entity class property changes.

o        The order of priority of retaining the data, from lowest to highest, is original entity class property values then reloaded changed column values.

Below discuss the three Resolve methods –

-               In the below examples – the date of customer with CustomerID == "LAZYK" is as-

o        ContactName = 'John Steel',

o        ContactTitle = 'Marketing Manager'

	DataContext.ChangeConflicts.ResolveAll()
	public void my()         {             Northwind db = new Northwind(@"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind");             Customer cust = db.Customers.Where(c => c.CustomerID == "LAZYK").SingleOrDefault();             ExecuteStatementInDb(String.Format(@"update Customers set ContactName = 'Samuel Arthur Sanders' where CustomerID = 'LAZYK'"));             cust.ContactTitle = "President";             try             {                 db.SubmitChanges(ConflictMode.ContinueOnConflict);             }             catch (ChangeConflictException)             {                 db.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);                 try                 {                     db.SubmitChanges(ConflictMode.ContinueOnConflict);                     cust = db.Customers.Where(c => c.CustomerID == "LAZYK").SingleOrDefault();                     Console.WriteLine("ContactName = {0} : ContactTitle = {1}", cust.ContactName, cust.ContactTitle);                 }                 catch (ChangeConflictException)                 {                     Console.WriteLine("Conflict again, aborting.");                 }             }         }
	Output – -          Executing SQL statement against database with ADO.NET ... -          Database updated. -          ContactName = Samuel Arthur Sanders : ContactTitle = President -          Executing SQL statement against database with ADO.NET ... -          Database updated. Note - Neither the ADO.NET change nor my LINQ to SQL change were lost.

           

           

	ObjectChangeConflict.Resolve()
	public void my2()         {             Northwind db = new Northwind(@"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind");             Customer cust = db.Customers.Where(c => c.CustomerID == "LAZYK").SingleOrDefault();             ExecuteStatementInDb(String.Format(@"update Customers set ContactName = 'Samuel Arthur Sanders' where CustomerID = 'LAZYK'"));             cust.ContactTitle = "President";             try             {                 db.SubmitChanges(ConflictMode.ContinueOnConflict);             }             catch (ChangeConflictException)             {                 foreach (ObjectChangeConflict conflict in db.ChangeConflicts)                 {                     Console.WriteLine("Conflict occurred in customer {0}.",  ((Customer)conflict.Object).CustomerID);                     Console.WriteLine("Calling Resolve ...");                     conflict.Resolve(RefreshMode.KeepChanges);                     Console.WriteLine("Conflict resolved.{0}", System.Environment.NewLine);                 }                 try                 {                     db.SubmitChanges(ConflictMode.ContinueOnConflict);                     cust = db.Customers.Where(c => c.CustomerID == "LAZYK").SingleOrDefault();                     Console.WriteLine("ContactName = {0} : ContactTitle = {1}", cust.ContactName, cust.ContactTitle);                 }                 catch (ChangeConflictException)                 {                     Console.WriteLine("Conflict again, aborting.");                 }             }         }
	Output – -          Executing SQL statement against database with ADO.NET ... -          Database updated. -          Conflict occurred in customer LAZYK. -          Calling Resolve ... -          Conflict resolved. -          ContactName = Samuel Arthur Sanders : ContactTitle = President -          Executing SQL statement against database with ADO.NET ... -          Database updated.

           

	MemberChangeConflict.Resolve()
	public void my3()         {             Northwind db = new Northwind(@"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind");             Customer cust = db.Customers.Where(c => c.CustomerID == "LAZYK").SingleOrDefault();             ExecuteStatementInDb(String.Format(@"update Customers set ContactName = 'Samuel Arthur Sanders',ContactTitle = 'CEO'                                                                         where CustomerID = 'LAZYK'"));             cust.ContactName = "Viola Sanders";             cust.ContactTitle = "President";             try             {                 db.SubmitChanges(ConflictMode.ContinueOnConflict);             }             catch (ChangeConflictException)             {                 foreach (ObjectChangeConflict conflict in db.ChangeConflicts)                 {                     Console.WriteLine("Conflict occurred in customer {0}.",   ((Customer)conflict.Object).CustomerID);                     foreach (MemberChangeConflict memberConflict in conflict.MemberConflicts)                     {                         Console.WriteLine("Calling Resolve for {0} ...",                         memberConflict.Member.Name);                         if (memberConflict.Member.Name.Equals("ContactName"))                         {                             memberConflict.Resolve(RefreshMode.OverwriteCurrentValues);                         }                         else                         {                             memberConflict.Resolve(RefreshMode.KeepChanges);                         }                         Console.WriteLine("Conflict resolved.{0}", System.Environment.NewLine);                     }                 }                 try                 {                     db.SubmitChanges(ConflictMode.ContinueOnConflict);                     cust = db.Customers.Where(c => c.CustomerID == "LAZYK").SingleOrDefault();                     Console.WriteLine("ContactName = {0} : ContactTitle = {1}",  cust.ContactName, cust.ContactTitle);                 }                 catch (ChangeConflictException)                 {                     Console.WriteLine("Conflict again, aborting.");                 }             }
	Output – -          Executing SQL statement against database with ADO.NET ... -          Database updated. -          Conflict occurred in customer LAZYK. -          Calling Resolve for ContactName ... -          Conflict resolved. -          Calling Resolve for ContactTitle ... -          Conflict resolved. -          ContactName = Samuel Arthur Sanders : ContactTitle = President -          Executing SQL statement against database with ADO.NET ... -          Database updated.

           

Resolving Conflicts

-          There are three approaches to resolving conflicts:

o        Easiest,

o        Easy, And

o        Manual

-          The Easiest approach is to merely call the ResolveAll method on the DataContext.ChangeConflicts collection, passing a RefreshMode and an optional bool specifying whether to automatically resolve deleted records.

-          Automatically resolving deleted records means to mark the corresponding deleted entity object as being successfully deleted, even though it wasn’t because of the concurrency conflict, so that the next time the SubmitChanges method is called, the DataContext will not attempt to delete the deleted entity object’s matching database record again. In essence, we are telling LINQ to SQL to pretend like it was successfully deleted because someone else deleted it first, and that is alright.

-          The Easy approach is to enumerate through each ObjectChangeConflict in the DataContext. ChangeConflicts collection and call the Resolve method on each ObjectChangeConflict.

-          If, however, you need some special handling, you always have the option to handle the resolution yourself by enumerating through the DataContext object’s ChangeConflicts collection, then enumerating through each ObjectChangeConflict object’s MemberConflicts collection, calling the Resolve method on each MemberChangeConflict object in that collection.

-          Even with Manual resolution, methods are provided to make this easy.

Pessimistic Concurrency

-          With the pessimistic concurrency approach, there are no actual conflicts to resolve, because the Database Is Locked By Your Transaction, so no one else can be modifying it behind your back.

-          To test this, we’ll create a TransactionScope object (T1) and obtain an entity object for customer LAZYK.

-          Then,  create another TransactionScope object (T2) with a TransactionScopeOption.RequiresNew.

-          This is required so the ADO.NET code does not participate in the ambient transaction created by the previously created TransactionScope object.

-          After that, I will attempt to update that same record in the database using ADO.NET. Since there is already an open transaction locking the database,the ADO.NET update statement will be blocked and eventually timeout.

-          Next, I will update the entity object’s ContactName, call the SubmitChanges method, query the customer again to display the ContactName to prove it was updated by LINQ to SQL, and complete the transaction.

	public void my()         {             Northwind db = new Northwind(@"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind");             using (System.Transactions.TransactionScope T1 = new System.Transactions.TransactionScope())             {                 Customer cust =   db.Customers.Where(c => c.CustomerID == "LAZYK").SingleOrDefault();                 try                 {                     Console.WriteLine("Let's try to update LAZYK's ContactName with ADO.NET.");                     Console.WriteLine(" Please be patient, we have to wait for timeout ...");                     using (System.Transactions.TransactionScope t2 = new System.Transactions.TransactionScope(                     System.Transactions.TransactionScopeOption.RequiresNew))                     {                         ExecuteStatementInDb(String.Format(@"update Customers set ContactName = 'Samuel Arthur Sanders'where CustomerID = 'LAZYK'"));                         t2.Complete();                     }                    Console.WriteLine("LAZYK's ContactName updated.{0}", System.Environment.NewLine);                 }                 catch (Exception ex)                 {                     Console.WriteLine("Exception occurred trying to update LAZYK with ADO.NET:{0} {1}{0}",                     System.Environment.NewLine, ex.Message);                 }             }             cust.ContactName = "Viola Sanders";             db.SubmitChanges();             cust = db.Customers.Where(c => c.CustomerID == "LAZYK").SingleOrDefault();             Console.WriteLine("Customer Contact Name: {0}", cust.ContactName);             T1.Complete();         }
	Output – -          Let's try to update LAZYK's ContactName with ADO.NET. -          Please be patient, we have to wait for timeout ... -          Executing SQL statement against database with ADO.NET ... -          Exception occurred trying to update LAZYK with ADO.NET: -          Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. -          The statement has been terminated. -          Customer Contact Name: Viola Sanders -          Executing SQL statement against database with ADO.NET ... -          Database updated.