One reason that SQL Server can update subscription data conflicts

Source: One reason SQL Server can update subscription data conflicts

Why are updatable subscriptions conflicting?

Updatable subscriptions, when the upgrade adds a field, typically increases in the publisher's publication database, and after the table is added, the publication is automatically synchronized to the subscription database (replication schema change =true). However, if DML operations are performed at the subscription database at this time, the data will not be synchronized to the publishing table, and the difference in the subscription table will not be synchronized to the publication again if the DML operation has not been performed.

Replication Configuration Environment:

Updatable Subscription transactional replication

Publication and subscription conflicts are subject to subscription

Using queued updating

In the subscription operation

Conflict test Results (the following: When the data is inconsistent, the re-operation of the subscription causes a conflict, and the conflict policy is resolved automatically):

Update subscription data at this time when the publication data does not exist and the subscription data is present: (Queued update conflict)

when the publication and subscription primary keys are the same, msrepl_tran_version when different, the subscription data is updated at this time: (Queued update conflict)

The results of the above two cases:

In the publish conflict, the conflicting table record values are up to date, the subscription data is updated or inserted into the publishing table.

When the publication data does not exist and the subscription data exists, the subscription data is deleted at this time: (Queued update conflict)

when the publication and subscription primary keys are the same, msrepl_tran_version when different, the subscription data is deleted at this time: (Queued update conflict)

The results of the above two cases:

The delete operation was synchronized to the publication when the conflict occurred.

Conflict Winner:

This line no longer exists in "[dbo]. [Testtab] "in. [[dbo]. [Testtab]]. The value of the unique ID in [Qcfttabrowid] is "8d335a44-36a0-432c-bba4-4979df3c804e".

Conflict loser:

The above error occurred when attempting to delete this data at this location, possibly because this delete operation violates one or more constraints. If you ignore this conflict, it should be resolved in other ways. You can log the details of this conflict and then send the log entries to the system administrator.

How does a schema prevent conflicts?

To make schema changes on a table in a publication that supports updating subscriptions, you must stop all activities on the table at the Publisher and Subscribers, and you must propagate the pending data changes to all nodes before you can make schema changes. This ensures that incomplete transactions do not conflict with pending schema changes. After the schema changes propagate to all nodes, you can resume the activity on the published table. How to Stop a replication topology (Replication Transact-SQL Programming)

I tested a solution: SQL Server updatable subscription data online schema change (add field) scenario

Reference: Updatable subscriptions for transactional replication

One reason that SQL Server can update subscription data conflicts