Conflict Prevention – Uniqueness Case v6.2

Since there is no automatic built-in resolution strategy for the uniqueness conflict, this section discusses strategies to avoid this problem that would be implemented by the DBA. This discussion is based on a realm of numeric values generated by a sequence such as for a unique primary key.

The following are possible strategies:

• Node specific sequence range. A sequence range is reserved for each primary node. For example, primary node A would have MINVALUE = 1 and MAXVALUE = 1000, primary node B would have MINVALUE = 1001 and MAXVALUE = 2000, and so on for other nodes. This ensures that a unique ID is always generated across all primary nodes.
• Start value variation. Each node is assigned a different start value. For example, primary node A would have a START value of 1, node B would have 2, and node C would have 3. An increment greater than or equal to the number of nodes guarantees unique IDs as shown in Table 6 4.
• Common sequence. All nodes share a common sequence object, however this has the major disadvantage of slowing down transaction processing due to network round-trips associated with each ID generation.
• MMR-ready sequence. This is a technique that enhances the use of sequences and provides a more flexible, reliable approach for a distributed, multiple database architecture as is inherent in a multi-master replication system. This approach is recommended over the previously listed sequence techniques. See Conflict Prevention with an MMR-Ready Sequence for information on an MMR-ready sequence.

Table 6-4: Sequence With Start Value Variation