Conflict Prevention with an MMR-Ready Sequence v6.2

To prevent uniqueness conflicts in a multi-master replication system, an MMR-ready sequence can be used to generate unique identifiers for each row of publication tables that do not have an inherent, unique identifier.

An MMR-ready sequence incorporates a function and a sequence to return BIGINT data type, integer values. These values combine a user-assigned, unique database identifier for each primary node with a sequence generated within that primary node.

A publication table requiring an MMR-ready sequence can be altered to include a BIGINT NOT NULL column with a default value returned by the function.

An MMR-ready sequence satisfies the following characteristics:

• Uniqueness. The combination of the unique, database identifier with the sequence ensures that each row in a given table will have a unique value across all primary nodes.
• Clustered index support. An MMR-ready sequence does not impair the usage of a clustered index to provide retrieval efficiency. MMR-ready sequence values are returned in a typical, ordered sequence – not as random values such as if the universally unique identifier (UUID) were used.
• Effective migration support. Tables already utilizing a sequence can be modified to use an MMR-ready sequence with minimal impact on existing primary keys and foreign keys.
• Reliability and maintainability. In summary, an MMR-ready sequence provides a reliable and maintainable method to avoid uniqueness conflicts.

The following sections provide the steps for creating an MMR-ready sequence followed by an example. The conversion process for existing sequences is described in Section Converting a Standard Sequence to an MMR-Ready Sequence.