Master-Master Replication

?

Data Column: select for more on pattern organization

Application Column: select for more on pattern organization

Deployment Column: select for more on pattern organization

Infrastructure Column: select for more on pattern organization

Architecture Row: select for more on pattern organization

Data Architecture: select for more on pattern organization

Application Architecture: select for more on pattern organization

Deployment Architecture: select for more on pattern organization

Infrastructure Architecture: select for more on pattern organization

Design Row: select for more on pattern organization

Data Design: select for more on pattern organization

Application Design: select for more on pattern organization

Deployment Design: select for more on pattern organization

Infrastructure Design: select for more on pattern organization

Implementation Row

Data Implementation: select for more on pattern organization

Application Implementation: select for more on pattern organization

Deployment Implementation: select for more on pattern organization

Infrastructure Implementation: select for more on pattern organization

?

Complete List of patterns & practices

Complete List of patterns & practices

Complete List of patterns & practices

Complete List of patterns & practices

Version 1.0.0

GotDotNet community for collaboration on this pattern

Complete List of patterns & practices

Context

You are about to design a replication between a source and a target. Your requirements are:

·  Replication set is updateable at either end.

·  Updates need to be transmitted to the other party.

·  Conflicts need to be detected and resolved.

Problem

How do you design a replication to transmit data from the source to the target and from the target to the source when the common replication set is updateable on both sides during the replication interval?

Forces

Any of the following compelling forces would justify using the solution described in this pattern:

·  Need for updateable copies when not connected. The application at the target has to be able to update data even if the source database is not reachable.

·  Optimistic concurrency control. You have chosen to allow updates to a replication set without attempting distributed data updates to the corresponding replication set to keep it consistent (for example, because the computers are not permanently connected to each other). This is called optimistic concurrency control because it assumes that conflicts will arise but that these conflicts will be kept to a minimum. For this reason, conflict detection and resolution is necessary.

·  If you cannot afford the risk of conflicts, you may choose to use the Pessimistic Concurrency Control pattern. (Both OptimisticConcurrency Control and Pessimistic Concurrency Control are patterns described in Patterns of Enterprise Application Architecture [Fowler03].)

The following enabling forces facilitate the adoption of the solution, and their absence may hinder such a move:

·  Tolerance of latency. The applications on both source and target can cope with the fact that changes by other applications may not be visible immediately.

·  Network efficiency. Network characteristics, such as reliability, bandwidth, and network latency (responsiveness), allow the participating databases to exchange replication data with sufficient speed. The expected rate of transmissions will not saturate the network connection.

·  Low likelihood of conflicts. If the copies of the same item are updated on both source and target within the same transmission interval, the conflict has to be resolved, which results in one update overruling the other. Performing this conflict resolution consumes processing resources. If this additional workload is likely to be a problem for the target, then to use this pattern the likelihood of such conflicts should be fairly low.

Solution

Copy data from the source to the target and detect and resolve any update conflicts that have occurred since the last replication (due to changes to the same data on the source and target).

The solution consists of a replication building block with two replication links between the source and the target in opposite directions. Both replication links transmit the same replication set in both directions (see Figure 1). Such a pair of replication links is referred to as related links in the more detailed patterns.

Figure 1: Master-Master Replication with related links

Before describing the elements of the replication building block in detail, some definitions and background are presented.

Replication Unit

A replication unit is the smallest amount of data that can be discretely recognized in the transmission set. The replication unit can be one of the following:

·  Complete replication set

·  Table of the replication set

·  Transaction

·  Row

·  Column

For Master-Master Replication, the replication unit is almost always an individual row. In rare cases, it can also be a single column in a row, or it can be a complete transaction that has been executed on the source as well. The replication unit should not be the complete replication set or complete tables for a master-master replication.

Conflicts

During a transmission, special care has to be taken before the acquired and manipulated data is written to the target. Because the target can also be written to by applications, the data on the target may have been changed since the last transmission. A conflict occurs when data of a replication unit is also updated on the target since the last transmission. Before actually writing the replication unit to the target, conflicts must be detected and resolved.

Conflicts belong to one of the following categories:

·  Update conflicts occur when the target data to be updated or deleted was updated after the last transmission.

·  Delete conflicts occur when the target data to be updated or deleted was deleted after the last transmission.

·  Uniqueness conflicts occur when the target data to be inserted or updated causes a violation of a uniqueness constraint, such as a duplicate key or a violation of a unique secondary index.

·  Business conflicts occur when the data can be transmitted to the target, but the overall integrity of the target database would be violated by the written data.

The following is an example of a business conflict:

The database contains information about citizens, such as name, address, age, and driver's licenses. The business constraint states that all holders of driver's licenses must be at least 18 years old. Originally, both databases contain a record for John Smith, 20 years old, with no driver's license. Before the next transmission, both databases are updated by applications. In the source database, John is assigned a driver's license, which is acceptable because he is over 18 years old. In the target database his age is changed to 15 years, which is acceptable because he has no driver's license. Hence, both individual transactions meet the business constraint. After exchanging the updates, both databases would hold an entry for John Smith, 15 years old, with a driver's license. This violates the business constraint.

Conflict Detection

Before the transmitted data is actually written to the target, the corresponding data in the target has to be checked for potential conflicts. This conflict detection is always done on a row or column level, even if the replication unit is a complete transaction.

After a conflict has been detected, it must be resolved before writing the transmitted data.

Conflict Resolution

Methods for resolving conflicts may be simple or complex, depending on the business needs. Table 1 lists possible conflict resolution methods.

Table 1: Conflict Resolution Methods

Conflict

Method

Description

Update, delete

Priority based

Every database has a priority assigned to it. The replication unit from the database with the higher priority prevails.

Update

Value based

Rules defined on the values decide the winning replication unit.For example, if the data contains a timestamp, the more recent timestamp prevails.

Update

Merge

The values of both source and target are merged by operations such as: min, maxsum, average

Update, delete

Overwrite

The data in the target database is overwritten in every case. Unlike the priority-based method, this method does not require conflict detection.

All

Manual

After a conflict is detected, the replication is suspended until the conflict is resolved manually.

Uniqueness

Discard

If you try to insert data that exists in the target database, the new data is ignored.

Uniqueness

Append sequence

If you try to insert data that exists in the target database, the key value is changed to a new value. Such data must be merged manually later.

 

 

 

In general, business conflicts cannot be resolved automatically. Instead, there are two ways to handle such conflicts:

·  The data violating the business constraints are accepted temporarily in the target database. Usually, such data is marked during the transmission and must be cleansed manually.

·  If temporary violations of business constraints are not acceptable, the replication must be suspended until the conflict is resolved manually.

Elements of Master-Master Replication

This section presents the elements of a Master-Master Replication solution in more detail.

Source and Target

Both source and target are databases containing a replication set. In general, the schemas of the source replication set and the target replication set may differ slightly, as long as the manipulate service can convert the data as described in the architectural replication pattern. However, for Master-Master Replication, the schemas are usually identical.

Hint: You should avoid using referential integrity in a Master-Master Replication scenario because it can give you major problems. You must ensure that the replication data is written to the databases without side effects, such as double updates due to referential integrity. This is very complex when you have referential integrity on both masters because of the two-way data exchange for the same set of data.

To ensure integrity, the source and target databases must not fire any follow-up operations, such as triggers or cascade deletes, during the replication (if the transmission from the source includes those changes as part of its change information). However, if you must have referential integrity constraints or triggers for other updates, then you have to prevent the transmission from triggering these operations. You can achieve this by using a dedicated user or role for the transmission and implementing the triggered operations so that they do not perform follow-up operations for the given user or role.

Acquire

Master-Master Replication does not overwrite the data in the target but tolerates changes in both databases. Thus, the transmission will send only the data changes, instead of the complete content of the replication set. That is, it performs an incremental replication, either as described in the Master-Slave Transactional Incremental Replicationpattern, or an incremental replication on a row level. However, Master-Slave Snapshot Replication is not suitable for a master-master replication. Hence, the Acquire service will read only the changes to the replication set at the source.

Manipulate

The Manipulate service receives the data from Acquire. It can manipulate the data by performing operations on the fields of the current row and assign the result to an output field.

Typical uses of these expressions are:

·  Data type conversions

·  Concatenation of fields, for example first name and last name, into a single name field

·  Splitting fields, for example extracting first name and last name from a name field

Because there is another replication link in the opposite direction, all manipulations of the data must be reversible; for example, the opposite replication link must perform complimentary manipulations of this service.

Write

The Write service receives the data to be written from the Manipulate service. Before actually writing a replication unit, it must check whether the corresponding target data has also changed since the last transmission. Therefore Write reads the appropriate data and performs the conflict detection. If a conflict has been detected, an appropriate conflict resolution method must be called, which either returns the winner or a new replication unit is written instead of the original one. If the changes from the source are accepted by the conflict resolution, or if a modified replication unit has been returned, this replication unit is to be written to the target. However, if the conflict resolution rejects the replication unit from the source, it must be discarded and not written to the target.

Example: Synchronizing Laptops and a Central Database

For example, you have a central database with customer data. Sales forces have all or parts of the database contents on their laptops to permit access to the data while disconnected from the network.

Both the central database and laptop databases can update customer data, such as addresses. When the sales force is back in the office, they synchronize the changes in their databases with any changes made to the central database since they were last connected.

Figure 2: Central database and laptop databases

Master-Master Replication allows the exchange of changes in both directions. An appropriate conflict resolution must be defined, for example the most recent change should overrule any older changes.

A similar scenario is described in more detail in the pattern Master-Master Row-Level Synchronization.

Resulting Context

As described in the previous section, the main problem in a master-master replication is the handling of conflicts. The complexity of the conflict resolution depends, among other things, on the replication unit.

If the replication units are complete transactions, it may be hard to resolve the conflicts automatically because non-trivial transactions might affect different tables in a manner such that no definite winner can be determined. In most cases, you will be forced to use the overwrite method or a manual resolution.

Hint: Use a replication unit of rows, if possible. Use columns only when absolutely necessary.

The replication refresh policy for a master-master replication will be an incremental replication because you have to be able to identify the changes at both source and target.

This pattern inherits the benefits and liabilities from Data Replicationand has the following additional benefit and liabilities:

Benefits

·  Backup.Master-Master Replication can be used as a means of backup. If one of the databases crashes, only changes made to this specific database since the last transmission will be lost.

Liabilities

·  Consistent conflict resolution methods. The conflict resolution methods of both replication links must agree on the same result. For example, if you are using a priority-based conflict resolution where the priority for the source is 1 and for the target is 2, the value of the source will prevail. This same priority schema must be applied to each replication link.

·  Surveillance of conflict resolution. The conflict resolution method should log its actions. This log must be checked regularly to ensure that the conflict resolution works correctly and the frequency of conflicts does not increase dramatically.

Next Considerations

During a more detailed design, the parameters of the replication have to be specified, for example:

·  Transmission frequency. What is the appropriate timing of the transmission for the requirements?

·  Initiator. Should the source push the transmission or should the target pull the transmission?

Related Patterns

For more information, see the following related patterns:

Patterns That May Have Led You Here

·  Move Copy of Data. This pattern is the root pattern of this cluster; it presents the overall architecture to maintain redundant data by asynchronous writing of copies of data eventually after the data has been updated.

·  Data Replication. This pattern presents the architecture of a replication.

Patterns That You Can Use Next

·  Master-Master Row-Level Synchronization. This patternpresents a design for Master-Master Replication, which detects and resolves any conflicts at the row level.

Other Patterns of Interest

·  Master-Slave Replication. This pattern presents an alternative to Master-Master Replication, where applications must not write to the target; otherwise, such updates will be overwritten by a later transmission.

·  Master-Slave Snapshot Replication. This pattern presents a design for transmitting a complete replication set. This can be used to equalize both databases as a starting point before establishing a master-master replication.

Acknowledgments

[Fowler03] Fowler, Martin. Patterns of Enterprise Application Architecture. Addison-Wesley, 2003