I want to know if in a transactional push replication with updatable subscribers, a commit is only done when all subscribers have succeeded to make it, or if it only guarantees the correct commit in the publisher and the subscriber that generated the change in the database.
I've found this in msdn, but I don't know if the two-phase commit protocol is referring to all instances (publisher and subscribers, or just one subscriber and the publisher)
When data is updated at a Subscriber, it is first propagated to the
Publisher and then propagated to other Subscribers. If immediate
updating is used, the changes are propagated immediately using the
two-phase commit protocol. If queued updating is used, the changes are
stored in a queue; the queued transactions are then applied
asynchronously at the Publisher whenever network connectivity is
available. Because the updates are propagated asynchronously to the
Publisher, the same data may have been updated by the Publisher or by
another Subscriber and conflicts can occur when applying the updates.
Conflicts are detected and resolved according to a conflict resolution
policy that is set when creating the publication.
Best Answer
To paraphrase the quoted block, with immediate update the only guarantee is that the commit is immediately updated to the publisher. The changes will be sent to the other subscribers in due course.
Without immediate updating, your changes are only committed to the local subscriber, and queued for distribution back to the publisher, and following that, the other subscribers.
In both cases there is a period of time when a stale record is still at the other subscribers, and one of those subscribers may update the stale record and cause a conflict.