If two users are updating the same record at the same time, a conflict will occur. There are normally three different ways to handle this conflict. The first method is to let the last update win, so no controlling mechanism is needed. The second one is to use a pessimistic lock, in which case, before updating a record, a user will first lock the record, and then process and update the record. At the same time, all other users will have to wait for the lock to be released in order to start the updating process.
The third and most common mechanism in an enterprise product is the optimistic locking. A user doesn't lock a record for update when the data is retrieved, but when the application is ready to commit the changes, it will first check to see if any other user has updated the same record since that data was retrieved. If nobody else has changed the same record, the update will be committed. If any other user has changed the same record, the update will fail, and the user has to decide what to do with the conflict. Some possible options include overwriting the previous changes, discarding their own changes, or refreshing the record and then reapplying (merging) the changes.
LINQ to SQL supports optimistic concurrency control in two ways. Next, we will explain both of them.
The first way is to use the Update Check property. At design time, this property can be set for a column to be one of these three values:
AlwaysNeverWhenChanged
For a column, there are three values to remember: the original value before update, the current value to be updated, and the database value when the change is submitted. For example, consider the case where you fetch a product record from the database with a UnitPrice of 25.00, and update it to 26.00. After you fetched this product, but before you submit your changes back to database, somebody else may have updated this product's price to 27.00. In this example, the original value of the price is 25.00, the current value to update is 26.00, and the database value when the change is submitted is 27.00.
When the change is submitted to the database, the original value and the database value are compared. If they are different, a conflict is detected.
Now, let us look at these three settings. The first setting of the property Update Check is Always, which means that the column will always be used for conflict detecting. Whenever a record is being changed, this column will always be checked to see if it has been updated by other users. If it has been, it raises a conflict. This is the default setting of this property. So by default, all columns will be used for conflict detecting.
The second setting, Never, means that column will never be used for conflict checking. When a change is submitted to the database, the application will not check the status of this column. So even if this column has been updated by other users, it won't raise an error.
The third setting, WhenChanged, is in between the two previous settings. It will be used for conflict detecting, but only if the current process has changed its value. If the current process hasn't changed its value, the application won't care if some other processes have updated its value.
To show how to use these three settings, we can write the following code:
// first user
Console.WriteLine("First User ...");
Product product = (from p in db.Products
where p.ProductID == 2
select p).First();
Console.WriteLine("Original price: {0}", product.UnitPrice);
product.UnitPrice = 26;
Console.WriteLine("Current price to update: {0}", product.UnitPrice);
// process more products
// second user
Console.WriteLine("Second User ...");
NorthwindDataContext db2 = new NorthwindDataContext();
Product product2 = (from p in db2.Products
where p.ProductID == 2
select p).First();
Console.WriteLine("Original price: {0}", product2.UnitPrice);
product2.UnitPrice = 26;
Console.WriteLine("Current price to update: {0}", product2.UnitPrice);
db2.SubmitChanges();
db2.Dispose();
// first user is ready to submit changes
Console.WriteLine("First User ...");
try
{
db.SubmitChanges();
}
catch (ChangeConflictException)
{
Console.WriteLine("Conflict is detected");
foreach (ObjectChangeConflict occ in db.ChangeConflicts)
{
MetaTable metatable = db.Mapping.GetTable(occ.Object.GetType());
Product entityInConflict = (Product)occ.Object;
Console.WriteLine("Table name: {0}", metatable.TableName);
Console.Write("Product ID: ");
Console.WriteLine(entityInConflict.ProductID);
foreach (MemberChangeConflict mcc in occ.MemberConflicts)
{
object currVal = mcc.CurrentValue;
object origVal = mcc.OriginalValue;
object databaseVal = mcc.DatabaseValue;
MemberInfo mi = mcc.Member;
Console.WriteLine("Member: {0}", mi.Name);
Console.WriteLine("current value: {0}", currVal);
Console.WriteLine("original value: {0}", origVal);
Console.WriteLine("database value: {0}", databaseVal);
}
}
}
In this example, we first retrieved product 2 and updated its price from 19.00 to 26.00. Then, we simulated another user to retrieving the same product, and also updated its price to 26.00. The second user submitted the changes first with no error, but when the first user tried to submit the changes, a conflict was detected because at that time the original value of 19.00 was different from the database value of 26.00. We can also use ChangeConflicts of the DataContext to get the list of conflicts.
Now, add the following using statements first:
using System.Data.Linq.Mapping; using System.Reflection;
Run the program. You will get an output as shown in the following image:
Now, open Northwind.dbml, click on the UnitPrice member of the Product class, change its Update Check property to Never, and run the program again. You won't see the exception this time, because this column is not used for conflict detecting. The output is as follows (you will need to change its price back to 19.00 before you re-run the program):
The second and a more efficient way provide conflict control is by using a version column. If you add a column of type Timestamp, or ROWVERSION, when you drag this table to the OR/M designer pane, this column will be marked as IsVersion = True.
Version numbers are incremented, and timestamp columns are updated every time the associated row is updated. Before the update, if there is a column with IsVersion=true, LINQ to SQL will first check this column to make sure that this record has not been updated by any of the other users. This column will also be synchronized immediately after the data row is updated. The new values are visible after SubmitChanges finishes.
When there is a column marked IsVersion=true, LINQ to SQL will use only this column for conflict detecting. All other columns' Update Property will be ignored, even if they have been set to Always or WhenChanged.
Now, let us try this in the Products table. First, we need to add a new column called LastUpdateVersion, which is of type timestamp. You can add it within Visual Studio 2008 in the Server Explorer by right-clicking on the table Products, and selecting Open Table Definition, as shown in the following image:
You can also open SQL Server Management Studio, and add the column from there.
After saving the changes, drag the Products table from the Server Explorer to the Northwind.dbml design pane, and keep the name Product1. This table now has a version controlling column, LastUpdateVersion, with properties as shown in the Properties dialog box image.
Note that its Update Check Property is set to Never. Actually, all other members' Update Check properties have been set to Never, because for this class, only the LastUpdateVersion column will be used for conflict detecting.
Open the Northwind.designer.cs file, and you will see that the column LastUpdateVersion has the following attributes:
[Column(Storage="_LastUpdateVersion", AutoSync=AutoSync.Always, DbType="rowversion NOT NULL", CanBeNull=false, IsDbGenerated=true, IsVersion=true, UpdateCheck=UpdateCheck.Never)] public System.Data.Linq.Binary LastUpdateVersion
We can write similar code to test this new version controlling mechanism:
// first user
Console.WriteLine("First User ...");
Product product = (from p in db.Products
where p.ProductID == 3
select p).First();
Console.WriteLine("Original unit in stock: {0}", product.UnitsInStock);
product.UnitsInStock = 26;
Console.WriteLine("Current unit in stock to update: {0}", product.UnitsInStock);
// process more products
// second user
Console.WriteLine("Second User ...");
NorthwindDataContext db2 = new NorthwindDataContext();
Product product2 = (from p in db2.Products
where p.ProductID == 3
select p).First();
Console.WriteLine("Original unit in stock: {0}", product2.UnitsInStock);
product2.UnitsInStock = 27;
Console.WriteLine("Current unit in stock to update: {0}", product2.UnitsInStock);
db2.SubmitChanges();
db2.Dispose();
// first user is ready to submit changes
Console.WriteLine("First User ...");
try
{
db.SubmitChanges();
}
catch (ChangeConflictException)
{
Console.WriteLine("Conflict is detected");
foreach (ObjectChangeConflict occ in db.ChangeConflicts)
{
MetaTable metatable = db.Mapping.GetTable(occ.Object.GetType());
Product entityInConflict = (Product)occ.Object;
Console.WriteLine("Table name: {0}", metatable.TableName);
Console.Write("Product ID: ");
Console.WriteLine(entityInConflict.ProductID);
foreach (MemberChangeConflict mcc in occ.MemberConflicts)
{
object currVal = mcc.CurrentValue;
object origVal = mcc.OriginalValue;
object databaseVal = mcc.DatabaseValue;
MemberInfo mi = mcc.Member;
Console.WriteLine("Member: {0}", mi.Name);
Console.WriteLine("current value: {0}", currVal);
Console.WriteLine("original value: {0}", origVal);
Console.WriteLine("database value: {0}", databaseVal);
}
}
}
