Mutual Updates and Optimistic Concurrency

In This Topic

LinqConnect Documentation
Mutual Updates and Optimistic Concurrency
[email protected]

It is typical for modern databases that multiple users can access (and modify) the same parts of data, and this often occurs almost simultaneously. Thus, the data you've loaded from the server and are going to update could have been already changed by someone else. In such a situation it is not clear what values should be stored in the database - your new values or the values that were stored in the database before the SubmitChanges method call.

There are two approaches to this problem: pessimistic and optimistic. Pessimistic locking suggests that the situation when users simultaneously modify the same records often occurs in this database. Pessimistic locking approach prevents simultaneous updates to records by locking records as soon as a user selects them for updating. Other users have to wait until the first user posts his updates to the database.

When such situation does not often occur, the optimistic concurrency approach is used. When using this approach no locking is performed. Instead when committing an update user should check if the updated record was changed by someone else in the database. This approach does not prevent such conflicts however it notifies user that such situation occurred before user posts update to the database and allows user to solve this conflict somehow.

This optimistic concurrency approach is used in LinqConnect, and it provides a number of tools to ease detection and solving of concurrency conflicts. This topic describes the detection of the concurrency conflicts in LinqConnect. To know more about processing them see the Error Processing topic.

Update Checks

LinqConnect uses the update check method to detect concurrency conflicts when submitting changes. All entity fields or part of them are marked for the check, and when updating an entity, not only entity key fields are checked, these fields are checked too. If at least one of the fields was changed, the update condition does not correspond to any of the records. Update command will modify no rows, and this situation will be processed by LinqConnect, which will report about the error. This method is used by default because it is suitable for all tables and does not require database modifications.

For example, let the Product entity have 'Update Check = always' for the InStock property, and 'Update Check = when changed' for the Price properties. To simplify the sample, suppose that overwrites of other property changes are not important, so let them have 'Update Check = never'. Then, the following code:

C#csharp	Copy Code
CrmDemoDataContext context = new CrmDemoDataContext() { Log = Console.Out }; Product firstProduct = context.Products.Where(p => p.ProductID == 8001).Single(); firstProduct.UnitName = "parcel"; firstProduct.UnitScale = 3; context.SubmitChanges();

C#csharp

Copy Code

CrmDemoDataContext context =
    new CrmDemoDataContext() { Log = Console.Out };
 
Product firstProduct = context.Products.Where(p => p.ProductID == 8001).Single();
firstProduct.UnitName = "parcel";
firstProduct.UnitScale = 3;
context.SubmitChanges();

Visual Basic	Copy Code
Dim context As New CrmDemoDataContext() With { _ .Log = Console.Out _ } Dim firstProduct As Product = context.Products.Where(Function(p) p.ProductID = 8001).[Single]() firstProduct.UnitName = "parcel" firstProduct.UnitScale = 3 context.SubmitChanges()

Visual Basic

Copy Code

Dim context As New CrmDemoDataContext() With { _
   .Log = Console.Out _
}
Dim firstProduct As Product = context.Products.Where(Function(p) p.ProductID = 8001).[Single]()
firstProduct.UnitName = "parcel"
firstProduct.UnitScale = 3
context.SubmitChanges()

will produce the following SQL:

UPDATE Products SET UnitName = :p1, UnitScale = :p2 WHERE ProductID = :key1 AND ((:nullchk1 = 1 AND InStock IS NULL) OR (InStock = :chk1))

And the following code:

C#csharp	Copy Code
CrmDemoDataContext context = new CrmDemoDataContext() { Log = Console.Out }; Product firstProduct = context.Products.Where(p => p.ProductID == 8001).Single(); firstProduct.UnitName = "parcel:"; firstProduct.UnitScale = 3; firstProduct.Price += 10; context.SubmitChanges();

C#csharp

Copy Code

CrmDemoDataContext context =
    new CrmDemoDataContext() { Log = Console.Out };
 
Product firstProduct = context.Products.Where(p => p.ProductID == 8001).Single();
firstProduct.UnitName = "parcel:";
firstProduct.UnitScale = 3;
firstProduct.Price += 10;
context.SubmitChanges();

Visual Basic	Copy Code
Dim context As New CrmDemoDataContext() With { _ .Log = Console.Out _ } Dim firstProduct As Product = context.Products.Where(Function(p) p.ProductID = 8001).[Single]() firstProduct.UnitName = "parcel" firstProduct.UnitScale = 3 firstProduct.Price += 10 context.SubmitChanges()

Visual Basic

Copy Code

Dim context As New CrmDemoDataContext() With { _
   .Log = Console.Out _
}
Dim firstProduct As Product = context.Products.Where(Function(p) p.ProductID = 8001).[Single]()
firstProduct.UnitName = "parcel"
firstProduct.UnitScale = 3
firstProduct.Price += 10
context.SubmitChanges()

will produce the following SQL:

UPDATE Products SET UnitName = :p1, UnitScale = :p2, Price = :p3 WHERE ProductID = :key1 AND ((:nullchk1 = 1 AND InStock IS NULL) OR (InStock = :chk1)) AND ((:nullchk2 = 1 AND Price IS NULL) OR (Price = :chk2))

The generated SQL consists of the following parts:

UPDATE Products SET UnitName = :p1, UnitScale = :p2, Price = :p3 - saving changes
WHERE ProductID = :key1 - primary key check. It is necessary to update exactly the same same entity we have worked on. This would be enough if concurrency conflict were impossible.
AND ((:nullchk1 = 1 AND InStock IS NULL) OR (InStock = :chk1)) AND ((:nullchk2 = 1 AND Price IS NULL) OR (Price = :chk2)) - update checks. If this record was modified in the database by someone else, the update command will affect no rows.

Note:
Besides the '<property> = :chkN' checks, there are also '(:nullchkN = 1 AND <property> IS NULL)' checks for nullable properties. Otherwise, if such property had the NULL value for some entities, we would have to use a different SQL statement with the '<property> IS NULL' check instead of '<property> = :chkN' one because this check would return null regardless of the server value.

Note that these two SQL statements differ: the Price field is checked only in the second statement. The InStock field is checked in both statements despite it has not been modified in both cases.

Note:
By default, Update check is set to 'always' for all properties, except for the fields that are problematical to compare. For example, update check is disabled for LOB properties because it is inapropriate to send a large parameter value to a server for each update. Float types with single precision are not checked too because a client computer may round the number in another way than the server and the comparison will return false even for equal numbers.

Note:

By default, Update check is set to 'always' for all properties, except for the fields that are problematical to compare. For example, update check is disabled for LOB properties because it is inapropriate to send a large parameter value to a server for each update. Float types with single precision are not checked too because a client computer may round the number in another way than the server and the comparison will return false even for equal numbers.

Version Properties

The main drawback of update checks is performance overhead. In general it is enough to check the primary key for updating records, but to ensure that there are no concurrency conflicts, the update check of all the fields is required. This is substantially slower because the SQL command gets bigger and includes more auxiliary parameters. It is especially noticeable when a table has dozens of fields, and we need to modify just few of them.

To address these issues, there is another way of detecting concurrency conflicts. It requires an additional column in a table. This column does not store any useful information and is used only to ensure that the updated data are up-to-date. It is updated automatically each time when the record is updated, so if the value of this field was modified, it means that the record was changed, and it is enough to check only the primary key and this field when updating a record.

Note:
Such feature can be implemented with a trigger, which updates this column somehow when a record is updated, on the database side. SQL Server and MySQL have special datatype that implement automatic update (MySQL, SQL Server)

Exapmple (for Oracle)

CREATE TABLE "TableWithVersion" (
  "Id" NUMBER(9) NOT NULL,
  "Name" VARCHAR2(20),
  "RowVersion" TIMESTAMP(6),
  CONSTRAINT "PK_TableWithVersion" PRIMARY KEY ("Id")
);

CREATE OR REPLACE TRIGGER "TableWithVersion_Trigger"
BEFORE UPDATE ON "TableWithVersion" FOR EACH ROW
BEGIN
      SELECT current_timestamp INTO :NEW."Version" FROM DUAL;
END;

INSERT INTO "TableWithVersion" ("Id", "Name") VALUES (1, 'New row');
/

When we create mapping for this table we must specify that the RowVersion property is version (for example, if we use attribute mapping, "IsVersion=true" parameter must be specified for this property in the Column attribute.

For other databases you may need to modify the DDL statements.

Now let's update this record.

C#csharp	Copy Code
CrmDemoDataContext context = new CrmDemoDataContext() { Log = Console.Out }; TableWithVersion twv = context.TableWithVersions.First(); twv.Name = "Updated row"; context.SubmitChanges();

Visual Basic	Copy Code
Dim context As New CrmDemoDataContext() With { _ .Log = Console.Out _ } Dim twv As TableWithVersion = context.TableWithVersions.First() twv.Name = "Updated row" context.SubmitChanges()

We will get the following SQL for this code.
UPDATE "TableWithVersion" SET "Name" = :p1 WHERE "Id" = :key1 AND ((:nullchk1 = 1 AND "Version" IS NULL) OR ("Version" = :chk1)) RETURNING "Version" INTO :ret1

As you can see, only the primary key and RowVersion column is checked (though we have not modified Update Check for the Name property and it should be 'always'. The RETURNING clause is used to get the new generated RowVersion value. This value will be used for concurrency checks if this entity will be updated again. Note that this is Oracle-specific SQL. Other commands may be used to retrieve this value for other databases.

Using version fields have its own drawbacks:

It requires modifying database, which is not always possible.
Suppose there is a not so important set of fields, that we don't need to be sure their changes are not overwritten. When using update checks, one can just set update check for these fields to 'never' and then update these fields without the need to process concurrency conflicts. With version column, change of any column value will cause the change of the version column value, and we will need either to process concurrency conflicts even if these unimportant fields will be changed, or to modify trigger and make it more complicated.

[email protected]