Oracle Sequence nextval是来回跳转的数字 [英] Oracle Sequence nextval is jumping number back and forth

问题描述

我创建了一个新的表和一个新的序列，我有两个C＃web服务试图使用相同的查询使用 mySequence.nextval 检查它多次，他们都使用 mySequence.nextval ）。

I created a new table and a new sequence, I have two C# web services trying to insert records into this table using same query utilizing mySequence.nextval (and yes I checked it many times, they both use mySequence.nextval).

两个Web服务正在向表中插入行，但是 mySequence.nextval

The two web services are inserting rows to the table, but the mySequence.nextval is returning numbers out of sequence

以下是记录的创建方式，显示 PrimaryKey ，其值从 mySequence .nextval

Here is how the records were created, showing PrimaryKey which gets its value from mySequence.nextval

1 21 22 23 2 3 24 25 4 27 28 5

1 21 22 23 2 3 24 25 4 27 28 5

到目前为止没有重复，但为什么 mySequence.nextval 来回跳转？我应该担心它

So far no duplicates but why is mySequence.nextval jumping back and forth? and should I worry about it

更新：
使用cache_size = 20创建序列

Update: The sequence is created with cache_size = 20

推荐答案

我会注意到您的数据库正在运行RAC（Real Application Clusters）。假设是这种情况，并且创建具有所有默认设置的序列，这是预期的行为。

I will wager that your database is running RAC (Real Application Clusters). Assuming that is the case and that you create the sequence with all the default settings, that's the expected behavior.

默认设置是缓存20个值。默认情况下，RAC集群中的每个节点都将有一个单独的缓存。假设你有一个有两个节点A和B的集群，第一次在A上请求一个 nextval ，A将缓存值1-20并返回值1。如果在B上发出对 nextval 的下一个请求，则B将缓存值21-40并返回值21.从那里，您获得的值将取决于节点。

The default setting is to cache 20 values. Each node in the RAC cluster, by default, will have a separate cache. Assuming that you have a cluster with two nodes A and B, the first time a nextval is requested on A, A will cache values 1-20 and return a value of 1. If the next request for a nextval is made on B, B will cache values 21-40 and return a value of 21. From there, the value you get will depend on the node that your connection happens to be running on.

一般来说，这不应该是一个问题。序列生成唯一的数字。数字通常不需要是连续的。如果你真的需要顺序返回值，因为你正在做一些顺序生成的值来确定第一或最后一行，你可以使用 ORDER 子句，以强制按顺序返回值。然而，这在RAC数据库中具有负面性能影响，因为它增加了节点之间需要进行以同步所返回的值的通信量。如果您需要确定第一个或最后一行，通常最好添加日期或时间戳记列到表和顺序，而不是假设主键是按顺序生成的。

Generally, this shouldn't be a problem. Sequences generate unique numbers. The numbers generally need not be consecutive. If you really need values to be returned sequentially because you are doing something like ordering by the sequence-generated value to determine the "first" or "last" row, you can use the ORDER clause when you create the sequence to force values to be returned in order. That has a negative performance implication in a RAC database, however, because it increases the amount of communication that needs to go on between the nodes to synchronize the values being returned. If you need to determine the "first" or "last" row, it's generally better to add a date or a timestamp column to the table and order by that rather than assuming that the primary key is generated sequentially.

这篇关于Oracle Sequence nextval是来回跳转的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！