你能锁定一个 Oracle 序列吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5601805/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can you lock an Oracle sequence?
提问by JOTN
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?
是否可以锁定 Oracle 序列，以便在我完成脚本之前尝试在其上使用 nextval 的任何会话都会阻塞？
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.
如果有不同的方式，我会解释我在做什么。我正在准备一个模式来使用流进行双向复制。我想确保所有主键序列产生唯一值。我通过将序列增加 1 直到最后一位数字为 1 然后将增量更改为 100 来做到这一点。在另一台服务器上我这样做直到最后一位数字为 2 这样服务器 1 总是产生主键 XXXXX01 和服务器 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.
问题是这是一个 24x7 的数据库，我无法在调整序列时停止所有活动。如果我能在短时间内获得排他锁，我就可以可靠地做到这一点。
采纳答案by ik_zelf
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.
不，您不能锁定序列。您可以尝试重新创建处于所需状态的序列。它会在短时间内引起问题。
回答by Ronnis
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.
最简单的可能解决方案（在我看来）是对服务器 1 使用奇数，对服务器 2 使用偶数。这也消除了序列化的需要。
create sequence server1_seq increment by 2 start with 1;
create sequence server2_seq increment by 2 start with 2;
This would produce a series of values like:
这将产生一系列值，例如：
Server 1 Server 2
-------- --------
1 2
3 4
5 6
7 8
9 10
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 NOTuse 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.
话虽如此，如果您打算随着时间的推移添加更多服务器，请不要使用这种方法。您必须使所有数据库脱机并从头开始重建表。
回答by Jeffrey Kemp
I personally like the idea of using separate non-overlapping sequences as Ronnis has suggested.
我个人喜欢像 Ronnis 建议的那样使用单独的非重叠序列的想法。
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.
如果您能够向数据模型中添加一列，则您可以添加一个标识符，该标识符的设置取决于进程在哪个服务器上运行。
如果无法添加列，则可以将列数据类型更改为 VARCHAR2，并将其设置为组合；例如，在服务器 1 上，您会将“1-”连接到序列值，例如“1-103450”因此不会与服务器 2 上生成的“2-103450”发生冲突。
Either way, this doesn't suffer from any problems with adding more servers in the future.
无论哪种方式，将来添加更多服务器都不会遇到任何问题。
回答by Jeffrey Kemp
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.
如果您只想暂时停止会话从序列中获取值，您可以撤销对它的访问。当然，除非您的用户以序列所有者的身份登录。
REVOKE SELECT ON mysequence FROM username;
Note: it's not a lock per se; instead of waiting, they'll get an Oracle error (invalid identifier, I think).
注意：它本身不是锁；他们不会等待，而是收到 Oracle 错误（我认为标识符无效）。
回答by Florian_1990
It seems you actually can lock a sequence.
看来您实际上可以锁定一个序列。
SELECT SEQ_NAME.nextval FROM dual FOR UPDATE [NOWAIT];
Tested on Oracle 11 XE. Also have a look at https://www.experts-exchange.com/questions/20181432/lock-wait-on-sequence.htmland https://geraldonit.com/oracle/database/oracle-database-locktypes/.
在 Oracle 11 XE 上测试。另请查看https://www.experts-exchange.com/questions/20181432/lock-wait-on-sequence.html和https://geraldonit.com/oracle/database/oracle-database-locktypes/。
