Identifying Possible Conflicts

[Previous] [Next]

I've yet to see a list of the different types of optimistic updating conflicts, so I've compiled one of my own. I've also included a sample on the CD entitled "Detecting Conflicts" that allows you to cause and log conflicts. It's described in more detail in Appendix B. For now, let's take a brief look at the different types of conflicts.

Simple Conflicts

The conflict I described earlier in the chapter is an example of a simple conflict. You want to modify a field or fields in a particular record, but after you retrieve the record and before you modify it, another user changes at least one of the fields you want to modify. You can trap for a conflict and determine whether it represents a simple conflict by resynchronizing the conflicting record and comparing the Value, OriginalValue, and UnderlyingValue properties of each Field object. If the OriginalValue property of at least one Field object is set to a value that differs from both its Value and UnderlyingValue properties, you have a simple conflict.

The Status property on a record that represents a simple conflict will return adRecModified + adRecConcurrencyViolation when the conflict occurs.

Cross Conflicts

I invented the term "cross conflict" to suit this scenario: you want to modify a field or fields in a record in your database, but your update fails because another user has modified that record—but the other user did not modify any of the fields that you want to modify.

If you base your updates on timestamp values, this type of conflict is certainly possible. This scenario can make it difficult for you to determine how the other user modified that record because the other user might have modified a field that doesn't even appear in the results of your query. Resynchronizing the record in your Recordset will simply retrieve the same data in all fields, except the timestamp field, that you already had. Instead of resynchronizing on your original query you could retrieve the contents of the entire record into a new Recordset object, but you'd be unable to determine what field changed because that data did not appear in your original Recordset. Realistically, all you can do is inform your user that another user has modified that record in the database. Whether the user can continue will depend on the requirements of your application.

You can also generate a cross conflict if you use all the fields in your recordset in the WHERE clause of the action queries generated by the ADO Cursor Engine. This will occur if your table lacks a primary key (but you won't try to update such a table using the ADO Cursor Engine, right?) or if you set the Update Criteria dynamic property on the Recordset object to adCriteriaAllCols.

In this situation, you can determine that you've encountered a cross conflict by resynchronizing the record and comparing the OriginalValue and UnderlyingValue properties on each Field object to locate which field or fields were modified by another user. Then compare the Value and OriginalValue properties on that Field object or objects to determine whether they were also modified in the local Recordset object.

For cross conflicts, as for simple conflicts, the Status property will report adRecModified + adRecConcurrencyViolation.

Modifying a Deleted Record

You can't modify a record that no longer exists in your database. Until you resynchronize the record you attempted to modify, this scenario will look like a simple conflict or a cross conflict. In other words, after the update attempt fails, the Status property on the Recordset object will return adRecModified + adRecConcurrencyViolation.

You'll be able to determine why the update attempt failed after you've resynchronized the record. If you attempt to examine the UnderlyingValue property on any of the Field objects, you'll receive the following trappable error:

 "A given HROW referred to a hard- or soft-deleted row." 

At this point, you can let the user know why the update attempt failed and, if possible, allow him or her to continue as appropriate.

Deleting a Modified Record

This type of conflict is similar to both the modifying a deleted record conflict and the cross conflict. You can treat this conflict just like a cross conflict in terms of trying to determine how the record you want to delete has been changed. After the deletion attempt fails, this conflict will result in a Recordset Status value of adRecDBDeleted.

If your table has no primary key (bad idea), or if you set the Update Criteria dynamic property on the Recordset object to adCriteriaAllCols, the ADO Cursor Engine will use the value of all fields in your Recordset in the WHERE clause of the action query to delete the record from the database. If another user modifies one or more of those fields in the database between the time you initially retrieve the record and the time you try to delete the record from the database, your attempt to delete that record will fail. Resynchronize the record, and compare the OriginalValue and UnderlyingValue properties on each Field object in the Recordset to determine what information in the record has changed. Then inform the user of the problem, and continue as appropriate.

If you set the Update Criteria dynamic property on the Recordset to adCriteriaTimestamp, any change made to the record you want to delete will cause a conflict. However, as with a cross conflict, you won't be able to determine how the record changed if another user has changed a field that does not appear in the results of your query.

Deleting a Deleted Record

Attempting to delete a record that another user has deleted from the database will fail. After the deletion attempt fails, the Recordset will contain a Status value of adRecDeleted + adRecConcurrencyViolation.

As with trying to modify a deleted record, resynchronizing the record and then checking the UnderlyingValue property on any Field object in the Recordset will generate the trappable error letting you know that the data corresponds to a deleted record.

How your application reacts to this error will depend on what's appropriate for that application. Even though ADO reports a conflict, you might decide that the user doesn't need to know that another user actually deleted the record from the database—since the result is the same no matter who deleted the record. Of course, you might want your application to let the user know that the attempt to delete the record from the database failed and why this failure occurred.