DEALING WITH REPLICATION CONFLICTS

Replicated databases introduce a new problem: What happens when users at different replicas simultaneously make incompatible modifications to the data or design? In Access 2000, the events that cause synchronization conflicts and errors are both viewed simply as synchronization conflicts. A single mechanism is used to record and resolve them, making resolution of such problems easier. When a conflict occurs, a resolution algorithm determines a winner and a loser. This release of replication introduces an algorithm whereby replicas in a replica set are assigned priorities, and the highest priority replica wins in a synchronization conflict. Where priorities are equal, the replica with the lowest replica ID wins. The winning record is placed in the table in both replicas. The losing record is placed in a “conflict table” and replicated to both replicas. The conflict table is a replicated table named TableName_Conflict (where TableName is the name of the table where the conflict occurred). Access automatically invokes an application, the Microsoft Replication Conflict Viewer, to help you resolve entries in conflict tables.

Understanding Data Conflicts

Consider the simple case of two users simultaneously updating the same record (row) in a RowLevelTracked table, or the same cell (column) in the record in a ColumnLevelTracked table, in different replicas. (Of course, in a traditional, non-replicated database, the locking mechanism will prevent such a problem.) When the replicas try to synchronize, Access recognizes that there's a conflict. The “winner” is the record in the replica with the higher priority.

The one exception to this data-conflict rule is that a deletion always wins. For example, if one replica modifies a record and another replica deletes the record, after an exchange the record is deleted at both replicas—although the losing record is still placed in the conflict table and replicated to all replicas.

If a conflict has occurred, after synchronization is successfully complete, Access users are told that a conflict has occurred when they open the replica or choose Replication and then Resolve Conflicts from the Tools menu. They are asked whether they want to resolve the conflict immediately; if so, the Microsoft Replication Conflict Viewer is invoked, which displays the table with the winning and losing records side by side. Users follow the following steps:

Using the Access Conflict Resolver

Let's set up a sample database to use with the Access Conflict Resolver. In Access, create a very simple database called Db3.mdb, with one table and one field, such as

Table = 'Customers'

Field = 'Name', type Text

No primary key

Follow these steps:

After you resolve the conflict, the conflict record and the Customers_Conflict table are deleted.

Using an Alternative Conflict-Resolution Algorithm

You might be tempted to replace the Access Conflict Resolver with a conflict resolver that better meets your business needs. For example, you might want to resolve conflicts so that the most recent update automatically wins. You should consider a number of issues when doing this:

• The algorithm used to determine the “winner” and “loser” is hard-coded into Access and can't be modified by a developer. Therefore, you can't modify how conflicts are initially recorded. However, you can watch for conflicts at each replica and provide a conflict resolver to replace the one shipped with Access. This is what might be termed a deferred conflict resolver because it's not called at the time the conflict is first noticed by Access, but at some later time. The task of this resolver is to process the conflict record Access has created.

• A replacement conflict resolver must be a function written in VBA and identified as a database property. To register your new function, choose Database Properties from the File menu and select the Custom tab. Set the Name to ReplicationConflictFunction; set the Type to Text, and enter the name of your VBA function in the Value text box. Or use JRO to set the ConflictFunction property to the name of your custom function.

Care must be taken in selecting a conflict-resolution algorithm. A date/time base algorithm, for example, in which the most recent update wins, must take into account computers that may be in different time zones (such as Greenwich mean time in England and Pacific standard time in the United States), when the clocks in each computer aren't synchronized, or when a user resets his date/time for daylight saving time. If great care isn't taken, each scenario can lead to diverging data in replicas.

Your algorithm must deal with all conflict types (covered later in this chapter) that might occur during synchronizations.

Identifying Replicas with Conflicts

The algorithm in Listing 23.10 identifies tables in a replica that have outstanding conflicts. It takes the name of a replica and prints out any tables with conflicts.

Listing 23.10. Chap23.mdb: Finding the Tables with Replication Conflicts

Sub Conflicts(strReplica As String)

    Dim rep As New JRO.Replica
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    ' Set required connections
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" + _
        "Data source=" & strReplica & ";"
    rep.ActiveConnection = conn

    ' Find tables with conflicts
    Set rs = rep.ConflictTables
    rs.MoveFirst
    Do While rs.EOF = False
        Debug.Print rs(0) & " Table had a conflict!"
         rs.MoveNext
    Loop

    rep = Nothing
    conn = Nothing

End Sub
							

Using the Last-Update-Wins Algorithm

The following algorithm is designed to work with replicas that have a special Date/Time field, Date, in each row that records the time an update was made. It makes no allowances for the time zone of each replica (such as GMT or PST).

The pseudocode for this algorithm is

Open database
For each table
     If there is a conflict table
        For each conflict row GUID ==  base data row GUID
          If conflict has a later date/time stamp field
               replace base row with conflict row
               delete conflict table row
      Next conflict table row
Next table

Listing 23.11 shows the actual code.

Listing 23.11. Chap23.mdb: Last Update Wins Conflict Algorithm

Private Sub Resolve(strReplica As String)

Dim rep As New JRO.Replica
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset ' Create record set for conflict tables
Dim rstWin As New ADODB.Recordset ' Create record set for conflict winner
Dim rstConflict As New ADODB.Recordset ' Create record set for loser
Dim fld As ADODB.Field

    ' Open the database & tables
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data source=" & strReplica & ";"
    Set rep.ActiveConnection = conn
    Set cmd.ActiveConnection = conn

    ' Retrieve the Conflict Tables recordset
    Set rs = rep.ConflictTables
    rs.MoveFirst
    Do While rs.EOF = False
        ' Get the records from the conflict table
        cmd.CommandText = "Select * From " & _
            rs(1) & " Order By s_GUID"
        rstConflict.Open cmd, , adOpenKeyset, adLockOptimistic
        ' Get the records from the base table
        cmd.CommandText = "Select * from " & rs(0) _
            & " Order By s_GUID"
        rstWin.Open cmd, , adOpenKeyset, adLockOptimistic
        ' Process each record, starting at the first
        rstWin.MoveFirst
        rstConflict.MoveFirst
        While Not rstConflict.EOF
        ' If date on the conflict row > date on winning row
        ' then resubmit the conflict row
            If rstConflict("s_GUID") = rstWin("s_GUID") Then
                ' This code assume you have a Date column in your table
                ' which records the date/time when the record was edited.
                If rstConflict("Date") > rstWin("Date") Then

                    For Each fld In rstConflict.Fields
                        If fld.Name <> "ConflictRowGUID" And _
                            Left$(fld.Name, 2) <> "s_" Then
                                rstWin(fld.Name) = rstConflict(fld.Name)
                        End If
                    Next
                    rstWin.Update
                End If
                ' Remove conflicting record & clean up
                rstConflict.Delete
                rstConflict.MoveNext
            End If
            rstWin.MoveNext
        Wend
        rstWin.Close
        rstConflict.Close
    rs.MoveNext
    Loop    ' Next Table

    rep = Nothing
    conn = Nothing

End Sub
							

Understanding Various Replication Conflicts

The following summarizes the types of synchronization conflicts that can be encountered:

• Simultaneous Update. The most frequent conflict type is when two users simultaneously update data in the same record or field, depending on the tracking level set for the table. The losing record is logged to the appropriate conflict table.

• Update-Delete. Access 2000 replication has retained the same behavior from in Access 97 in that delete actions are always processed. This means that, no matter what the replica's priority is, the deleted record will always win in case of a conflict and be deleted. However, in Access 2000, the record that's about to be deleted is checked to see whether any updates were unknown before the delete occurs. If this is the case, the updated record is logged to the appropriate conflict table.

• Unique Key. These errors can occur when there's a unique key constraint on a field in a table. Two replicas enter a record that contain the same key value, even though only unique values are permitted.

• Table-Level Validation. A record contains a field value that doesn't meet a table-level validation rule. To prevent a TLV conflict, synchronize with all replicas immediately before applying a TLV rule, and then again immediately after.

• Referential Integrity. RI conflicts can occur when a relationship between tables is enforced and cascading updates and deletes aren't enabled. You can avoid this type of conflict through careful application design, such as if cascades for updates and deletes are always selected whenever referential integrity is enforced. There are three kinds of referential-integrity conflicts:

  On Delete	The primary key record is deleted in another replica, so the foreign record is rejected.
  On Update	The primary key record is updated in another replica, so the foreign record is rejected.
  Foreign Key	A foreign-key violation resulted from an invalid primary key record that was involved in another replication-conflict type.

  
  

• Locking. Locking errors are the easiest to understand and usually fix themselves without any user intervention required. The record change couldn't be applied during synchronization because another user locked the table involved. The synchronization fails, but no conflict is logged. The solution is to try the synchronization again when the table isn't locked.