Resolving update conflicts with Change Data Capture

Another use of the new SQL Server 2008 feature Change Data Capture (CDC) is to resolve conflicts between multiple updates.

Consider a large online store where multiple business users update product information. Suppose product specialists use a big form to enter the data. Suppose also that one user can only update unit name or size. Another user can change product category. Let's see how we can use CDC to accurately track the changes.

Assume the following table holds the products:

In the Management Studio I opened table properties, selected Change Tracking, and enabled both "Change Tracking" and "Track Columns Updated". Note that a primary key is required for change tracking. This
command can accomplish the same from a script:
ALTER TABLE Products ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

When a user opens a Web form and intends to change the UnitName, application records the current version of the row that contains product data. We can use one of the new SQL 2008 functions to determine the current version of the database row:
DECLARE @prevVersion integer
SELECT @prevVersion = SYS_CHANGE_VERSION
FROM CHANGETABLE(VERSION Products, (ProductID), (1)) CT
The query selects the current version of a product with ID of 1 and saves it in variable @prevVersion.

The user makes desired changes and wants to save the data. To update the row in the database the application runs the following query:
UPDATE Products
SET Name='CDC Power', UnitName='book and CD', Category='Books'
FROM CHANGETABLE(VERSION Products, (ProductID), (1)) CT
JOIN Products P ON P.ProductID = CT.ProductID
WHERE CT.SYS_CHANGE_VERSION = @prevVersion

The WHERE clause condition ensures that the data in the database has not changed since the application last retrieved it.

Suppose that at the same time the first user is making the change, another user is changing category for the same product using the same query. The update executed by the first user increments the current version of the row, so the above query updates no rows for the second user. If the application did not check the version, the second update statement could override UnitName with obsolete data.

Now consider changing the application, so that the query for the second user updates the Category column only. It can execute the following query:
UPDATE Products
SET Category='Programming Guides'
FROM CHANGETABLE(CHANGES Products, 1) CT
JOIN Products P ON P.ProductID = CT.ProductID
WHERE P.ProductID = 1
AND CT.SYS_CHANGE_VERSION = @prevVersion
AND CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('Products'), 'Category', 'ColumnId'), CT.SYS_CHANGE_COLUMNS) = 0

The CHANGE_TRACKING_IS_COLUMN_IN_MASK function tells us whether the Category column has been updated since the last check. Since that is the only column the query updates, we can ignore changes to other columns. Now the query would succeed even if both users initially looked at the same version of the row. By checking which columns have actually changed, the conflicting updates will more likely succeed.