Optimistic Concurrency: Overview
LINQ to SQL supports optimistic concurrency control. The following table describes terms that apply to optimistic concurrency in LINQ to SQL documentation:
|concurrency||The situation in which two or more users at the same time try to update the same database row.|
|concurrency conflict||The situation in which two or more users at the same time try to submit conflicting values to one or more columns of a row.|
|concurrency control||The technique used to resolve concurrency conflicts.|
|optimistic concurrency control||The technique that first investigates whether other transactions have changed values in a row before permitting changes to be submitted.|
Contrast with pessimistic concurrency control, which locks the record to avoid concurrency conflicts.
Optimistic control is so termed because it considers the chances of one transaction interfering with another to be unlikely.
|conflict resolution||The process of refreshing a conflicting item by querying the database again and then reconciling differences.|
When an object is refreshed, the LINQ to SQL change tracker holds the following data:
- The values originally taken from the database and used for the update check.
- The new database values from the subsequent query.
LINQ to SQL then determines whether the object is in conflict (that is, whether one or more of its member values has changed). If the object is in conflict, LINQ to SQL next determines which of its members are in conflict.
Any member conflict that LINQ to SQL discovers is added to a conflict list.
In the LINQ to SQL object model, an optimistic concurrency conflict occurs when both of the following conditions are true:
The client tries to submit changes to the database.
One or more update-check values have been updated in the database since the client last read them.
Resolution of this conflict includes discovering which members of the object are in conflict, and then deciding what you want to do about it.
For example, in the following scenario, User1 starts to prepare an update by querying the database for a row. User1 receives a row with values of Alfreds, Maria, and Sales.
User1 wants to change the value of the Manager column to Alfred and the value of the Department column to Marketing. Before User1 can submit those changes, User2 has submitted changes to the database. So now the value of the Assistant column has been changed to Mary and the value of the Department column to Service.
When User1 now tries to submit changes, the submission fails and a ChangeConflictException exception is thrown. This result occurs because the database values for the Assistant column and the Department column are not those that were expected. Members representing the Assistant and Department columns are in conflict. The following table summarizes the situation.
You can resolve conflicts such as this in different ways. For more information, see How to: Manage Change Conflicts.
You can detect and resolve conflicts at any level of detail. At one extreme, you can resolve all conflicts in one of three ways (see RefreshMode) without additional consideration. At the other extreme, you can designate a specific action for each type of conflict on every member in conflict.
Specify or revise UpdateCheck options in your object model.
For more information, see How to: Specify Which Members are Tested for Concurrency Conflicts.
In the try/catch block of your call to SubmitChanges, specify at what point you want exceptions to be thrown.
For more information, see How to: Specify When Concurrency Exceptions are Thrown.
Determine how much conflict detail you want to retrieve, and include code in your try/catch block accordingly.
Include in your
catchcode how you want to resolve the various conflicts you discover.
For more information, see How to: Resolve Conflicts by Retaining Database Values, How to: Resolve Conflicts by Overwriting Database Values, and How to: Resolve Conflicts by Merging with Database Values.
Classes and features to support the resolution of conflicts in optimistic concurrency in LINQ to SQL include the following: