How does LINQ to SQL concurrency detect conflicts

By admin - Last updated: Friday, February 6, 2009 - Save & Share - Leave a Comment

LINQ to SQL utlises optimistic concurrency control (OCC) to determine the conflicts.

If you would like to learn more about optimistic vs pessimistic locking, then the following information will come in useful for you.

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,

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

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

fruit.UnitPrice = 4.5m;//note 4.5
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,

UPDATE Fruit
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,

UPDATE Fruit
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,

UPDATE Fruit
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,

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

[Column(Storage = “_Description”, DbType = “NVarChar(1000)”, UpdateCheck = UpdateCheck.Never)]
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.




Posted in LINQ • Tags: , Top Of Page

Write a comment


Captcha: 8 − two =