Conflict prevention – uniqueness case v7

Since there's no automatic built-in resolution strategy for the uniqueness conflict, you can use strategies to avoid this problem. These strategies are 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 has MINVALUE = 1 and MAXVALUE = 1000, primary node B has MINVALUE = 1001 and MAXVALUE = 2000, and so on for other nodes. This mechanism 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 has a START value of 1, node B has a value of 2, and node C has a value of 3. An increment greater than or equal to the number of nodes guarantees unique IDs, as shown in the table that follows.
• Common sequence. All nodes share a common sequence object. However this approach has the major disadvantage of slowing down transaction processing due to network round trips associated with each ID generation.
• MMR-ready sequence. This technique 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 other sequence techniques. See Conflict prevention with an MMR-ready sequence for more information.