How does LINQ to SQL concurrency detect conflicts
LINQ to SQL utlises optimistic concurrency control (OCC) to determine the conflicts.
OCC compares the loaded record to existing record before performing an update operation on the row so that the underlying conflicts could be detected.
The followings are steps to replicate the scenario,
Table Fruit
| Id | Name | UnitPrice | Description |
|---|---|---|---|
| 1 | Apple | 2.5 | very long text |
Step 1
Populate the fruit object from the database, and the object’s properties are,
{
Id=1, Name=“Apple”, UnitPrice=2.5m, Description=“Very long text”
};
Step 2 – Prepare Conflicts
Modify the price at database level manually to emulate the other user has altered the record after it is read.
SET UnitPrice = 3.5 –note 3.5
WHERE Id = 1
Step 3 – Conflicts Occured
Modify the price of the populated object and submit the changes, this should generate a conflict on “UnitPrice” since the record in the database has been updated by someone else.
db.SubmitChanges();
The DataContext uses the value of row affected after SQL “UPDATE”, in this case the SQL generated by LINQ would be something similar to,
SET UnitPrice = 4.5
WHERE Id = 1 AND UnitPrice=2.5 AND Description=“Very long text” – long where clause
——
(0 row(s) affected)
There isn’t any row that matches the where clause simply because of UnitPrice is actually 3.5 in the database. This indicates that the record was definitely modified after the object was loaded.
Thus, it throws ChangeConflictException
Resolve Conflicts – RefreshMode.OverwriteCurrentValues
Overwrite values in the fruit object by the values from the database, it repopulates fruit object.
Resolve Conflicts – RefreshMode.KeepCurrentValues
Overwrite values in the database by the values from fruit object. It is the opposite of above RefreshMode.OverwriteCurrentValues.
SQL by LINQ is,
SET UnitPrice = 4.5
WHERE Id = 1 AND UnitPrice=3.5 – altered value for generating conflict
AND Description=“Very long text” – it uses existing value from the database
Resolve Conflicts – RefreshMode.KeepChanges
Overwrite values in the database by the changed values within fruit object. in this case, only UnitPrice has been changed, therefore, the UnitPrice at database level will be 4.5 and leave the rest of records as it is currently in the database.
Again, we prepare another conflict on Description and changed it to “very short text” at database level and then the SQL by LINQ that resolve conflicts will look like,
SET UnitPrice = 4.5
WHERE Id = 1 AND UnitPrice=3.5 – altered value for generating conflict
AND Description=“very short text” – it uses existing value from the database
What if we set fruit.Description = “no more” at code level, there will be one conflict plus one change on Description the SQL for that will be,
SET UnitPrice = 4.5, Description=“no more” – keep the changes
WHERE Id = 1 AND UnitPrice=3.5 – altered value for generating conflict
AND Description=“very short text” – it uses existing value from the database
That is pretty much about how the conflicts resolved and the SQL to achieve it in LINQ. Obviously, each time it compiles SQL with heavy WHERE clause, in this example Description mostly likely contains large text data, and the comparison made on each SQL will degrade the overall performance. “UpdateCheck = UpdateCheck.Never” will never make the property appear in the WHERE clause, on the other hand, you lost version check on that column.
e.g.
public string Description{get;set;}
An other simple way of detecting conflicts is to use timestamp type column in the table (it is unique within the same database) and if any of column(s) in a row get updated, the timestamp value will be changed automatically. Instead of applying UpdateCheck on all the properties, we can just apply it on timestamp to detect if the row has been altered since it was loaded. But by this approach, you have to work out the changed properties by yourself if interested.