Sequence exhaustion

Replication software requires replicas to set the start value for any noncycling, replicated sequence above the maximum value that the primary allocated. If you force-promote the replica to primary with the OVERRIDE option, it cannot then allocate duplicate values. If a replica cannot set the start value above any value that the primary allocated for any single sequence, the set reaches sequence exhaustion. That is, the replica cannot update its sequence start value because the value exceeds the limits of the underlying sequence type. If replication continued, the two nodes might allocate duplicate “next values” for a sequence after the replica is promoted to primary. To prevent this, the new primary rolls back transactions against a replicated database when the available values of any non-cycling sequence are exhausted.

Although sequence exhaustion can occur in both replicated and non-replicated configurations, the exhausted condition can occur earlier in a replicated environment than complete exhaustion would occur in a stand-alone environment.

Consider the following best practices to minimize the occurrence of exhaustion for noncycling sequences, in both replicated and stand-alone environments:
  • Use large data types (int, or better yet, bigint) for the sequence type.
  • Start from the smallest possible value for increasing sequences and the largest possible value for decreasing sequences.
  • Use the smallest sequence increment or decrement.
  • In a sequence, do not overuse the next_X_values_for_sequence expression.
  • Decrease the cache size for a sequence with care. Decreasing the cache size increases the frequency with which SPUs must stop processing and obtain a new cache block from the host, potentially negatively impacting performance.

    Sequence cache values are flushed each time that the NPS® database stops (for example, when you issue the nzstop command). For replicated sequences, the values are also flushed each time that you promote a replication node to primary. These flushing operations cause up to <cache_size> unique values to be discarded (never allocated) for each sequence, potentially speeding sequence exhaustion.