locked
Peer-to-peer replication not staying in sync RRS feed

  • Question

  • We have set up a simple test of peer-to-peer replication with three nodes, A, B, and C.   Node A has the peer originator ID set to 100, node B is set to 90, and node C is set to 80.  Allow peer-to-peer conflict detection is set to true and continue replication after conflict detection is also set to true.  From these settings I would assume if node A and node B updated the same row at the same time, the values of node A would prevail.  But this isn't happening.

    I have a table called HoursData that has three columns.  The first column is SysID which is a uniqueidentifier and the primary key.  The second column is named Hours and is numeric(8,2).  The third column is named  UpdatedBy and is varchar(30).  There is a single row in the table that contains this data:

    SysID: E66AA9B0-7B73-4884-BFCE-7BDF688770BF     Hours: 10     UpdatedBy:  Smith

    If I run this query on node A:
      Update HoursData Set Hours = 20, UpdatedBy = 'Smith' Where SysID = E66AA9B0-7B73-4884-BFCE-7BDF688770BF    

    And at the same time run this query on node B:
      Update HoursData Set Hours = 30, UpdatedBy = 'Jones' Where SysID = E66AA9B0-7B73-4884-BFCE-7BDF688770BF    

    I find that the following data is shown for each node:

    Node A:  SysID: E66AA9B0-7B73-4884-BFCE-7BDF688770BF     Hours: 20     UpdatedBy:  Smith
    Node B:  SysID: E66AA9B0-7B73-4884-BFCE-7BDF688770BF     Hours: 20     UpdatedBy:  Jones

    As expected the hours for node B changed from 30 to 20 because node A has a higher peer originator ID.  However the UpdatedBy column did not revert to Smith.  The data is out of sync.  From what I can tell the issue is that the value for the UpdatedBy column did not change on node A.  This is not what I would expect to occur.  If I run the following on both servers at the same time"

    Node A:
      Begin Transaction
        Update HoursData set UpdatedBy = ''
        Update HoursData Set Hours = 20, UpdatedBy = 'Smith' Where SysID = E66AA9B0-7B73-4884-BFCE-7BDF688770BF    
      Commit Transaction

    Node B:
      Begin Transaction
        Update HoursData set UpdatedBy = ''
        Update HoursData Set Hours = 30, UpdatedBy = 'Jones' Where SysID = E66AA9B0-7B73-4884-BFCE-7BDF688770BF    
      Commit Transaction

    The data looks like this as I would expect it to:

    Node A:  SysID: E66AA9B0-7B73-4884-BFCE-7BDF688770BF     Hours: 20     UpdatedBy:  Smith
    Node B:  SysID: E66AA9B0-7B73-4884-BFCE-7BDF688770BF     Hours: 20     UpdatedBy:  Smith

    Is this the way peer-to-peer replication is designed to work where if a column value remains the same it doesn't change to the value from the higher originator ID?  This doesn't make much sense to me.

    Thursday, July 23, 2020 3:32 PM

Answers

  • I have discovered how to fix this. The replication uses stored procedure to transfer the data.   This can be found at

    https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-articles-specify-how-changes-are-propagated?view=sql-server-ver15

    By default updates use the SCALL syntax which states:  Stored procedures handling UPDATE statements will be passed the updated values only for those columns that have changed, followed by the original values for the primary key columns, followed by a bitmask (binary(n)) parameter that indicates the changed columns.

    This is exactly what is happening.  Only the changed values are replicated.  Changing to the CALL Syntax for updates does the following:

    Stored procedures handling UPDATE statements will be passed the updated values for all columns defined in the article, followed by the original values for the primary key columns (no attempt is made to determine which columns were changed.):

    Once I made this change the replication worked as I needed it to.

    • Marked as answer by lehrsj24 Saturday, July 25, 2020 12:42 PM
    Saturday, July 25, 2020 12:42 PM

All replies

  • Hi lehrsj24,

    The Peer originator ID is important to resolve conflicts in Peer-to-peer replication: the node with the highest number wins conflicts.

    It is not clear whether your situation is the expected behavior, please test multiple times to confirm.

    Please also refer to the article: Peer-to-Peer - Conflict Detection in Peer-to-Peer Replication.

    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. If an application requires sophisticated conflict detection and resolution capabilities, use merge replication.

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 24, 2020 7:41 AM
  • We have tested this many, many times.  We were testing this in an application that was being developed with many tables and it consistently did what was mentioned on various tables.  When two nodes update the same row at the same time there was a conflict as expected.  Data from the higher node was written back to the lower node number, except when data at the higher node hadn't changed.  In the example above it was the "UpdatedBy" column.  If node A, the higher node, had not changed the value on the row that was updated, and node B did change the value, then Node A retained the original value and node B retain its change, thus making the data different.  This was on any column that wasn't changed by node A. 

    I under stand about the recommendations for peer-to-peer - i.e. only update on one node.  However that is not what we want to do.  We are perfectly happy with the data from the higher ranked node taking precedence. The issue is that it's not working as expected in that all data in the higher node is not being replicated back to the lower node.  I cannot believe that is how it is supposed to work.  It makes the switch "continue replication after conflict detection" useless. 

    Is this the expected behavior or is this a bug?

    Thanks

    Friday, July 24, 2020 12:56 PM
  • I have discovered how to fix this. The replication uses stored procedure to transfer the data.   This can be found at

    https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-articles-specify-how-changes-are-propagated?view=sql-server-ver15

    By default updates use the SCALL syntax which states:  Stored procedures handling UPDATE statements will be passed the updated values only for those columns that have changed, followed by the original values for the primary key columns, followed by a bitmask (binary(n)) parameter that indicates the changed columns.

    This is exactly what is happening.  Only the changed values are replicated.  Changing to the CALL Syntax for updates does the following:

    Stored procedures handling UPDATE statements will be passed the updated values for all columns defined in the article, followed by the original values for the primary key columns (no attempt is made to determine which columns were changed.):

    Once I made this change the replication worked as I needed it to.

    • Marked as answer by lehrsj24 Saturday, July 25, 2020 12:42 PM
    Saturday, July 25, 2020 12:42 PM
  • Hi lehrsj24,

    Thanks for your reply.

    I am glad to hear that you have solved your problem. Thanks for your contribution again.

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 27, 2020 12:59 AM