Conflict detection

Main conception:

To detect and resolve an update conflict for a row, the propagating site must send a certain amount of data about the new and old versions of the row to the receiving site.

More info here!

How it is done:

The receiving site detects an update conflict if there is any difference between the old values of the replicated row (the values before the modification) and the current values of the same row at the receiving site.

Let's take an example:

1. New Patient with name "A" is created on Parent instance
2. Both Child 1 and Child 2 pull new Patient with name "A"
3. Child 1 updates name of Patient to "B" and pushes to Parent
   
   1. Conflict detection checks if name saved on Parent is equal to old (before updating) name saved on Child 1
      1. if YES, then it means that Child 1 is updating original and up-to-date name on Parent = no conflict, update
         
      2. in NO, then it means that either Parent itself or Child 2 updated name = conflict, resolve somehow
   2. Name saved on Parent is "A", old name saved on Child 1 is "A", updated name on Child 1 is "B"
   3. "A" == "A", conflict detection answered YES, name value was updated
4. Child 2 updates name of Patient to "C" and pushes to Parent
   
   1. Conflict detection checks if name saved on Parent is equal to old (before updating) name saved on Child 2
      1. if YES, then it means that Child 2 is updating original and up-to-date name on Parent = no conflict, update
         
      2. in NO, then it means that either Parent itself or Child 1 updated name = conflict, resolve somehow
   2. Name saved on Parent is now "B", old name saved on Child 2 is "A", updated name on Child 2 is "C"
   3. "B" !== "A", conflict detection answers NO, there is a conflict 

Conflict resolution

If it is possible to apply, we should check out Oracle Database Advanced Replication Management API Reference.

Common Update Conflict Resolution Methods

• Latest Timestamp
• Overwrite

But out of those, only Latest Timestamp is useful with multiple master sites.

Latest Timestamp = most recent update wins

Additional Update Conflicts Resolution Methods

(those than offer convergence with multiple master sites)

• additive - for single numeric values
• maximum - takes the biggest value
• minimum - takes the smallest value
• priority group - enable you to assign a priority level to each possible value of a particular column

Conflict detection

We can easily apply Oracle example to GitHub.

Example 1:

Let's imagine a master branch (Parent) and two branches (Child 1 and Child 2) with Parent as upstream.

1. Both branches pull master
2. Child 1 pushes its changes to master
   
   1. Parent code is equal to Child 1 code before making any modifications (update) = no conflict
3. Child 2 pushes its changes to master
   
   1. Parent code was modified by Child 1's push, so it is NOT equal to Child 2 code before making modifications = conflict

Conflict resolution

Resolving Example 1 conflict:

Git will not let Child 2 push to master, but will notify:

Updates were rejected because the remote contains work that you do not have locally.

You will be asked to pull updated master and resolve conflicts manually.

Create UI interface for choosing which changes to accept?

Conflict detection

Microsoft's note:

To avoid potential data inconsistency, make sure that you avoid conflicts in a peer-to-peer topology, even with conflict detection enabled. To ensure that write operations for a particular row are performed at only one node, applications that access and change data must partition insert, update, and delete operations. This partitioning ensures that modifications to a given row that is originating at one node are synchronized with all other nodes in the topology before the row is modified by a different node. 

So we just sync change in one node with ALL the rest?

How SQL Server 2017 handles it:

In systems such as peer-to-peer replication, conflicts are not detected when changes are committed at individual peers. Instead, they are detected when those changes are replicated and applied at other peers. In peer-to-peer replication, conflicts are detected by the stored procedures that apply changes to each node, based on a hidden column in each published table. This hidden column stores an ID that combines an originator ID that you specify for each node and the version of the row.

Conflict resolution

Strategies

• Manual intervention
• Logging conflicts
• Master write server
• Last write wins
• Write partitioning

a. Manual intervention

Letting users/admin choose which change to accept.

b. Logging conflicts

As conflicts are logged, they can be written to a queue where conflict resolution software and kick into action and attempt to resolve the conflicts automatically.

c. Master write server

One instance writes to all child instances - rather impossible solution

d. Last write wins

Oracle's Last Timestamp

e. Write partitioning

Ensure that all writes for a single row will always happen on the same server.

- useless in our system