可以锁定 Oracle 序列吗？
是否可以锁定 Oracle 序列，以便任何尝试在其上使用 nextval 的会话都会阻塞，直到我完成脚本为止？
我会解释我在做什么，以防有不同的方式。我正在准备一个架构来使用流进行双向复制。我想确保所有主键序列都产生唯一的值。为此，我将序列递增 1，直到最后一位数字为 1，然后将增量更改为 100。在另一台服务器上，我执行相同的操作，直到最后一位数字为 2。这样，服务器 1 始终生成主键 XXXXX01 和服务器 2 XXXXXX02。
问题是这是一个 24x7 的数据库，在调整序列时我无法停止所有活动。如果我能在短时间内获得独占锁，我就能可靠地做到这一点。
Is it possible to lock an Oracle sequence so any session trying to use nextval on it blocks until I'm done with my script?
I'll explain what I'm doing in case there's a different way. I'm preparing a schema to do bidirectional replication with streams. I want to make sure all the primary key sequences produce unique values. I do this by incrementing the sequence by 1 until the last digit is 1 and then change the increment to 100. On the other server I do the same until the last digit is 2. That way server 1 always produces primary keys XXXXX01 and server 2 XXXXX02.
The problem is this is a 24x7 database, and I can't stop all activity while I'm adjusting the sequences. If I can get an exclusive lock for a short time I can do it reliably.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
最简单的解决方案（在我看来）是对服务器 1 使用奇数，对服务器 2 使用偶数。这也消除了序列化的需要。
这将产生一系列值，例如：
该解决方案可以通过使用更大的增量（以及每个服务器的不同起始值）进一步扩展以处理两个以上的服务器。
话虽如此，如果您打算随着时间的推移添加更多服务器，请不要使用此方法。您必须使所有数据库脱机并从头开始重建表。
The simplest possible solution (in my opinion) is to use odd numbers for server 1 and even numbers for server 2. This also removes the need for serialization.
This would produce a series of values like:
This solution can further be extended to handle more than two servers by using larger increments (and different starting values for each server).
Having said that, do NOT use this approach if you intend to add more servers over time. You would have to take all databases offline and rebuild the tables from scratch.
不，您不能锁定序列。您可以尝试重新创建所需状态的序列。这会在短时间内造成问题。
No, you cannot lock a sequence. You could try to re-create the sequences in the required status. It will cause a problem for a short while.
我个人喜欢罗尼斯建议的使用单独的非重叠序列的想法。
另一个需要考虑的选项是复合主键。
如果您能够向数据模型添加列，则可以添加一个标识符，该标识符的设置取决于运行进程的服务器。
如果无法添加列，可以将列数据类型更改为 VARCHAR2，并将其设置为复合列；例如，在服务器 1 上，您可以将“1-”连接到序列值，例如“1-103450”因此不会与服务器 2 上生成的“2-103450”冲突。
无论哪种方式，这都不会遇到任何问题将来添加更多服务器。
I personally like the idea of using separate non-overlapping sequences as Ronnis has suggested.
Another option to consider is a composite primary key.
If you're able to add a column to the data model, you add an identifier that is set differently depending on which server the process is running on.
If you cannot add a column, you could instead change the column data type to VARCHAR2, and set it as a composite; e.g. on server 1, you would concatenate '1-' to the sequence value, e.g. '1-103450' would therefore not conflict with '2-103450' generated on server 2.
Either way, this doesn't suffer from any problems with adding more servers in the future.
如果您只是想暂时阻止会话从序列中获取值，您可以撤销对它的访问。当然，除非您的用户以序列所有者的身份登录。
注意：它本身不是锁；他们不会等待，而是会收到 Oracle 错误（我认为是无效标识符）。
If you just want to temporarily stop sessions from getting a value from the sequence, you could just revoke access to it. Unless, of course, your users are logging in as the owner of the sequence.
Note: it's not a lock per se; instead of waiting, they'll get an Oracle error (invalid identifier, I think).
看起来你实际上可以锁定一个序列。
在 Oracle 11 XE 上测试。另请查看 https://www.experts -exchange.com/questions/20181432/lock-wait-on-sequence.html 和 https://geraldonit.com/oracle/database/oracle-database-locktypes/。
It seems you actually can lock a sequence.
Tested on Oracle 11 XE. Also have a look at https://www.experts-exchange.com/questions/20181432/lock-wait-on-sequence.html and https://geraldonit.com/oracle/database/oracle-database-locktypes/.