Moving Forward with Replication, Part 2

The conclusion of our series on the complex replication issues inherent in mobile, sales force applications.

In last month's installment of this two-part series, I tried to provide a general introduction to the replication process, and offered some general guidelines for determining the appropriateness of replication for an application. In particular, I reviewe d the following replication topics.

• forms of replication: single-updater and multiple-updater systems and transaction replication vs. table replication
• general considerations in building replicated applications: data timeliness, richness of the table partitioning mechanism (distribution/determination), performance impact of changing the logging mechanism, and so on
• the issues associated with heterogeneous environments

Availability, Scalability, Synchronization, and Performance Issues

The sales force of a Fortune 500 company can easily consist of 800, 1000, or even 2000 field users. These salespeople typically connect to the main office either at the start of the work day or at the end of the work day or both. Although these sa lespeople may be spread across four time zones, it's logical to assume that 100-200 users/sites could try to connect at about the same time. What kind of database horsepower do you suppose would be required to support hundreds of direct database-to-datab ase connections through slow, modem-based connectivity, or through even slower wireless communication?

Considering these issues, my associates and I made a very conscious decision several years ago to move from a direct-connect architecture to a more batch-oriented model. In the batch model, replicated changes are not applied until after the "comms sessio n." Our operating definition of a comms session includes the scriptable set of activities that occurs prior to the actual communications connection (precomms), the actual communication session, and the scriptable set of activities that run after the comm unications line has been released (postcomms). This reduces the billable communication time to file-transfer time (that is, the time needed to transfer the transaction file physically), and greatly reduces the horsepower requirements of the server databa se engine because it does not have to process the transactions while the communications line is being held.

Batch-Oriented (Asynchronous) Synchronization

Of course, batch-oriented synchronization is not without its shortcomings. It can introduce quite a few nasty design issues, but it also provides the highest overall system availability and lowest system cost in terms of processing power and comms time.

Some Synchronization Dangers

First, if the replication process is table-based instead of transaction-based, you may pick up pieces of transactions based on the race condition that is established between the change table scanning process and whatever "external" activity may be adding entries to the change tables. ("Race condition" is a fairly common term in software engineering that refers to situations in which several processes want to access a particular resource, and the resultant behavior varies depending upon the sequence.) Th is could be especially problematic when a remote site is being completely restored. In this case, the table scan window may be open for an extended time period, increasing the likelihood of getting disjointed pieces of transactions.

If the partitioning scheme provides for reference to tables other than the base (replicated) tables, you must consider another risk. If the distribution (table partitioning) mechanism allows other tables to be referenced in order to determine that the pa rtitioning and database update activity is occurring in the background while replication is being performed, a "distribution list" may change, causing the same kind of data to be distributed differently at different times. For example, say there's a dist ribution list of Top3SalesPeople, which has entries {Mary,Peter,Glenn} at time T1, but, due to a reevaluation during the day, has {Mary,Peter,Foobar} at time T2. Maybe that's OK, maybe not. It's handy to have distribution "cycles" in order to feel confid ent that during a specific cycle, particular data was distributed with a particular distribution state.

Of course, these race conditions and distribution-determination dynamics can act in combination. In a table-based replication scheme, you can end up with the "pieces of transactions" scenario if the distribution list associated with data for tables A, B, and X changes between the time that the table A extract executes and the table X extract executes.

Complex Transaction Semantics

In some cases, you may not want the exact same functionality at all sites. I can point to delete handling as an example in my company's Sales Force Automation (SFA) applications. We frequently want to perform physical deletes on the remote units and logi cal or archival deletes at hub sites. For reporting purposes, it is often appropriate to retain the data physically at the server sites, while making it transparent to the daily application reference. The SFA applications also have knowledge of account s haring and account reassignment, whereby an account can be "deleted" from one user and "granted" to another. In this case, the operation looks like a physical delete on one remote site, an insert on another remote site, and an update to the AccountOwner field at the server site. In our replication system, the Open Data Delivery System, a proprietary database engine, uses an independent form of complex transaction processing to accomplish this. However, this process can also be performed with database pr ocedures (stored procedures). In this example, the interesting question relative to currently available replication products is:

If the result of changing the value of the "territory" field at a publisher site is "the account has been reassigned from territory A to territory B," how can you get the replication engine to send a delete to the losing territory and the necessary in sert information to the receiving territory?

The ability to generate transactions that didn't physically occur at a particular site into the replication tables for distribution to other sites can be very helpful. Therefore, you should be able to send to other systems those transactions or messages that result in actions (such as logical and physical deletes) that have a slightly different behavior at the originating site. What you really need here is a disconnected remote procedure call mechanism.

Cascaded Deletes

At first, it would seem that one way of regulating the depth of the cascade would be through the replication rules. However, it is not guaranteed that the replication rules provide granularity down to the operation type (insert, update, or delete). Furth ermore, can you define replication rules for lower-level tables, but have them applied only to deletes at the "root" of the delete, and not as part of a lower-level cascade? For instance, say an account has child tables Call and Sales, and Call has the c hild table, CallDetail. A cascaded delete on Account would also include Call, CallDetail, and Sales. A cascaded delete on Call would also include CallDetail. If, however, on an Account delete, you do not want Call, CallDetail, and Sales deletes to replic ate out to other sites, you can simply not replicate any Call, CallDetail, or Sales deletes. That way, the subscriber site, which presumably has some sort of cascaded delete procedure on Account, will receive only the Account delete -- not the deletes to Call, CallDetail, and Sales (these records will be gone by the time the cascaded delete completes). Therefore, it's important to know exactly how the replicator deals with cascaded deletes and other forms of complex transactions and database procedures. Unexpected output from the replicator can lead to extremely unexpected behavior at the receiving sites.

Error Notification and Handling

• What happens when a reject occurs? Will it suspend replication between the affected nodes?
• How will notification of the error condition be provided?
• Who will receive the notification?
• Where will the notification be delivered (subscriber, publisher, or both)?
• What tools are provided to correct the problem?

Therefore, you must understand the potential for data corruption, and weigh that against the impact of an unavailable or out-of-phase system. Then you must determine what options you have for detecting and correcting rejected transactions. Does your repl ication service provide the opportunity to allow the application to proceed?

You must also consider the following error-handling issues:

• How will notification of the error condition be provided? Who will be notified, and what tools are provided to correct the condition?
• On an error condition, what does the replication agent do to report the error, and how do you go about correcting and resubmitting the errant transaction?

We have also found it productive to handle rejected transactions at the publisher or hub nodes. Rejects from the mobile units are recorded in a set of database tables, which are replicated and returned to the publisher in the next comms cycle. Replicated transactions from the field units that are rejected at the hub are recorded in the same set of database tables. A reject-correction tool edits and resubmits the transactions. If the reject is a transaction from the field that was rejected at the hub, it will be reapplied locally. If a transaction was rejected at one of the field units, the corrected transaction is simply placed in the outgoing transaction queue for distribution during the next distribution cycle.

Handling Interruptions During Synchronization

• Is it important for the application to restart at the precise point of interruption?
• Is the application maintaining enough persistent-state information to be able to restart?
• If the interruption was a power failure, what mechanism will automatically restart the application?
• If the interruption was not as dramatic as a power failure, what mechanism will notify the user of the next steps?
• Are there other processes that should not run if the interrupted application has not yet completed?
• What mechanism is available to block the downstream applications?

Another more subtle, and potentially more damaging, scenario occurs if the change queue or change log is somehow corrupted. If it is wiped out, will it automatically be recreated? Is there a mechanism that can detect such corruptions? What backup mechani sm is provided? The answers to these questions will vary depending on the service provider. For a list of questions to ask your potential replication vendor, see the sidebar.

You must know precisely how the replication service will handle such interruptions. If the replicator is transaction-based instead of table-based, it will be much less of an issue. A table-based scheme incurs a much higher risk of transactionally inconsi stent data because pieces of a transaction in an unreplicated table will not be received until service resumes. Under such conditions, you must determine if the applications that use the replicated data should be allowed to proceed.

Sequence Generators

Obviously, the loss of centralized management of the sequence values can lead to problems. Therefore, my company has provided a sequence generator that operates at a level above the database engine, and has a built-in notion of a node or site identificat ion. The mechanism returns to the application a 32-bit integer value that is guaranteed to be unique for a given data object across the network of nodes. Thus, the sequence range is partitioned across the user node space.

Until database vendors supply a similar mechanism, you can take the following approaches:

• Add a userID/nodeID field to the sequence object to ensure that the value will be unique.
• If the database engine sequence generator provides a mechanism to start the sequence at a user-specified position, partition the range of the sequence across your perceived maximum user space and provide a mechanism to set the sequence value(s) to th e appropriate starting level at initial load time.

Data Collisions

The crux of the problem is that one or more users or nodes has updated the same row at about the same time. The fundamental dilemma here is timing. By definition, in replicated systems there will be some level of latency between the time that site A prov ides its updates to the publisher and when site B provides its updates.

In classical systems, the locking model enforces a "first in wins" policy. If a second user enters data five minutes later, thus avoiding the lockout condition, the last-in policy would be implemented. So which data is correct? There is an implicit, de f acto, almost cultural policy that last-in is "right." While this may be generally true, the locking mechanism is just the agreed-upon policy for resolving conflicts in single-engine systems.

In replicated systems, each node has its own database engine. Therefore, the collision scenario is delayed until the changes are applied at some integration point (such as the publisher site). If the data is shared (that is, it can be updated by multiple sites), a check must determine if changes have already been recorded against that row. If so, a collision may be signaled. Quite a variety of schemes have been proposed to detect collisions, ranging from row version numbers to checks against the change record. I believe the most common scheme is to check the target database change log for an entry with a key match.

Once a collision has been signaled, the replication service must activate some type of collision-handling mechanism. Some of these mechanisms are time based. However, a time-based mechanism does not make sense for any multisite distributed system. Think about it. Ask five of your friends what time it is. Odds are, you'll get five different answers. Won't that also be the case on five different computers? How about 1500 different battery-powered computers?

Let's assume that you could somehow guarantee that all the clocks are synchronized. On what time should you base the collision handling? Time of data entry or time of data transmission? What if Bob enters changes on Monday, but doesn't communicate them u ntil Friday? Is this time-based mechanism really going to try to roll back hundreds of transactions from hundreds of different sites? And what about all the transactions that followed and were potentially dependent upon the ones that will be rolled back?

The point is, time is a very relative concept in replicated systems, and it is not a concept upon which I would recommend basing any sort of collision-detection or resolution scheme. Furthermore, I would suggest that rollback mechanisms are not practical in such systems. My associates and I take the following approach in our SFA systems:

• The users of the system are best equipped to determine the "rightness" or "wrongness" of data.
• Occasionally, an ill-behaved user may enter wrong data into the system. Unfortunately, there is no universal way to prevent users from occasionally entering wrong data.
• If the wrong data is important data, the users will correct it.
• It is quite possible, perhaps even likely, to provide an automatic correction mechanism that will do more harm than good.
• It's best to provide a mechanism that ensures the consistency of the data values across the system.

In your systems, the publisher may be the data generator. If so, you want a mechanism that allows you to enforce that policy. Many of the replication services are now providing a selection of handling mechanisms, including user exits. The important thing is to understand what mechanisms the replication service provides for detection and handling, and determine if they will be adequate to enforce the policy appropriate for your application(s).

One final issue regarding collision detection and handling: Will the replication service stop replication between the affected nodes on a collision? This extreme approach ensures that no further transactions are processed that would be potentially incons istent or inappropriate relative to the collision. This may be the right thing to do, but can your application afford to stop completely until someone corrects the situation?

Moving Forward