How Immediate Updating Works

SQL Replication


How Immediate Updating Works

When a publication is enabled to support immediate updating, a Subscriber can modify replicated data if the transaction can be performed by using the two-phase commit protocol (2PC) with the Publisher. The 2PC transaction back to the Publisher is completed automatically, so an application can be written as though it is updating just one site.

This approach does not have the large availability limitations of using 2PC with all participating sites because only the Publisher needs to be available. After the change is made at the Publisher under 2PC, it will eventually be published to all other Subscribers to the publication.

2PC is managed by Microsoft Distributed Transaction Coordinator (MS DTC). If the update can be performed using 2PC, the Publisher propagates those changes to all other Subscribers according to the Distribution Agent schedule (or at the time of the next snapshot refresh, if it is a snapshot publication). Because the Subscriber making the update already has the data changes reflected locally, the user can continue working with the updated data secure in the guarantee that data at the Publisher also reflects the change. There is no loss of ACID properties.

An application using immediate updating should be able to deal with a failure in the transaction, just as it would in a non-replication environment for issues such as a uniqueness violation. The most common failure is that data has been changed at the Publisher, and Subscribers need to refresh their copies. In many cases, the preferred choice might be to retry the update after a few seconds. If the transaction is successful, the Subscriber can work with the changed values immediately, and know that the update has been accepted at the Publisher without conflict and will eventually be propagated to every Subscriber of the publication. A Subscriber performing updates does not have full autonomy; however, because the Publisher must be available at the time of the update. Autonomy is higher than the full 2PC case where every site must be available for any site to perform changes.

Instead of using a timestamp column to track updates (as in SQL Server 7.0), a uniqueidentifier column, added automatically to any tables used in the publication, is used to track updates. The addition of this column requires INSERT statements to have column lists.

The uniqueidentifier column MSrepl_tran_version is used in place of timestamps to provide a reliable method of detecting conflicts even when an update is made offline (such as in the case of queued updating). Any server in the enterprise can assign a uniqueidentifier and it will not be duplicated. If an update occurs and the uniqueidentifier columns do not match, a conflict is detected. If the uniqueidentifier columns match, the update is completed.