Transactional Consistency

Transactional consistency, with respect to replication, means that the data at any participating site will be the same as it would be had all operations been performed at a single site. That is, the act of replicating the data does not in itself change the data in ways that would not occur if replication were not used. When working with distributed applications that modify data, there are three basic levels of transactional consistency:

Immediate Guaranteed Consistency

With immediate guaranteed consistency (called tight consistency in SQL Server version 6.x), all participating sites are guaranteed to have the same data values at the same time, and the data is in the state that would have been achieved had all the work been done at the publishing site. The only way to achieve immediate guaranteed consistency in a distributed update environment (in which updates can be made to the same data at any location) is with the use of a two-phase commit (2PC) between all participating sites. Each site must simultaneously commit every change, or no site can commit the change. Such a solution is obviously not feasible for large numbers of sites because of unforeseen conditions, such as network outages.

Latent Guaranteed Consistency

With latent guaranteed consistency (called loose consistency in SQL Server version 6.x), all participating sites are guaranteed to have the same data values that were achieved at the publishing site at some point in time. There can be a delay in the data values being reflected at the Subscriber sites, so that at any instant in time, the sites are not assured of having the same data values. If all data modification could be paused long enough to allow every site to catch up and have every change applied, all sites would have the same data values. However, having the same value is not sufficient for latent guaranteed consistency. In addition, the data must be in the state that would have been achieved had all the work been done at one site. The difference between immediate and latent guaranteed consistency is whether the values are consistent at the same instant in time. If the system were allowed to catch up, the values would be identical regardless of whether the system is based on immediate or latent guaranteed consistency.

Convergence

With convergence, all the sites may end up with the same values, but not necessarily the values that would have resulted had all the work been done at only one site. All sites may work freely in a disconnected manner, and when all the nodes have synchronized, all sites converge to the same values. Lotus Notes is an example of a convergence product used to build useful distributed applications, yet it provides neither atomic transactions nor any model of guaranteed consistency.

Database vendors that support atomic transactions at a single site promote "update anywhere" replication solutions. Most database vendors offer the ability to resolve conflicts by discarding or changing the effects of one transaction because of another competing transaction performed at another node. These solutions are promoted as providing complete site autonomy. However, it is not well promoted that the act of conflict resolution means there is no guaranteed transactional consistency.

Example of Transactional Consistency

Three sites are participating in a replication scenario. Site 3 submits five transactions: T1, T2, T3, T4, and T5 for synchronization with the other two sites. Transactions T1 and T2 have no conflicts and are accepted. But transaction T3 has a conflict, and the reconciliation mechanism discards the transaction. Transactions T4 and T5 must also be discarded because these transactions may have read, and depended on, data that transaction T3 had modified. Because transaction T3 was subsequently discarded, it logically never existed. T4 and T5 are also in doubt. If they had been accepted, the results may have differed from the result that would have been obtained had all updates been done at a single site, thereby breaking the rules of transactional consistency.

Even a compensating transaction (for example, a delete if an insert had been accepted) is not sufficient to guarantee transactional consistency. Subsequent transactions may have applied changes to the transient data that resulted from the transaction that was resolved away. To remain in a state in which transactional consistency is guaranteed, not only must transaction T3 be discarded, but all subsequent work performed at that site, including T4 and T5, must be discarded as well.