peer to peer replication
SP_OPO_TRUSTED_SOURCE = DB A
SP_OCT_REDUCED_KEY=0 (so all columns/values are caught in the WHERE condition, pre image, new values)
SP_OPO_REDUCED_KEY=0 (so all columns are used in where condition at post, pre image , new values)
!HostPriority (DB A is the trusted host)
starting position, is in SYNC:
DB A, Table1:
ID=1
COL1=AB
COL2=XY
DB B, Table1:
ID=1
COL1=AB
COL2=XY
next:
There is update on the table at the same time same record with different update statments:
DB A:
UPDATE Table1 SET COL1='AAA' WHERE ID = 1
DB B:
UPDATE Table1 SET COL2='BBB' WHERE ID = 1
The end result is OOS:
Table 1 on DB A:
ID=1
COL1=AAA
COL2=XY
Table 1 on DB B:
ID=1
COL1=AAA
COL2=BBB
due to SHAREPLEX POST STATEMENT on DB B LOOKs LIKE:
UPDATE TABLE 1 SET COL1=AAA WHERE ID=1 AND COL1=AB AND COL2=XY
THIS WILL BE NO DATA FOUND SO CONFLICT RESOLUTION IS INVOKED:
WILL RESOLVE IT BY HOST PRIORITY IN FOLLOWING WAY:
UPDATE TABLE 1 SET COL1=AAA WHERE ID=1
the core of the problem is that only updated colums appear in the "SET" clause of replicated statement. so if 2 dbs are updated at the same time , updating different columns, we end up having out of sync records after successful conflict resolution.
What am i missing here ? thanks.
