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 = 1and
MAXVALUE = 1000, primary node B would have
MINVALUE = 1001and
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
|Sequence Clause||Primary node A||Primary node B||Primary node C|
|Generated IDs||1, 6, 11, 16, …||2, 7, 12, 17, …||3, 8, 13, 18, …|