Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.