如何在多线程应用程序中获取 DB2 序列值
我正在开发一个使用 DB2 作为其主数据库的多线程应用程序。过去，我们主要在需要自动生成的唯一标识符的表中使用标识列。为此，我们将在同一事务中运行以下 2 个查询：
INSERT INTO tbname (IDENTITY_COL, ...) VALUES (DEFAULT, ...);
SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1;
我们现在被迫切换到序列。我知道您可以在 INSERT 和 SELECT 语句中使用“NEXT VALUE FOR colname”，但我不知道如何在不冒多线程中出现竞争条件的风险的情况下使用相同的值来插入和选择应用。例如，如果我使用：
INSERT INTO tbname (SEQUENCE_COL, ...) VALUES (NEXT VALUE FOR SEQUENCE_COL, ...);
SELECT PREVIOUS VALUE FOR SEQUENCE_COL;
那么有可能在上述 INSERT 和 SELECT 之间运行另一个 INSERT，因此为我提供了不正确的值。如果我尝试：
SELECT NEXT VALUE FOR SEQUENCE_COL;
将值存储在变量中并将其传递到 INSERT：
INSERT INTO tbname (SEQUENCE_COL, ...) VALUES (variable_value, ...);
那么另一个线程可能获得相同的 NEXT VALUE 并尝试插入相同的值，从而导致 DB2 -803 错误。是否可以在多线程环境中使用 SEQUENCE 列，或者我是否需要努力保留 IDENTITY 列？
I am working on a multithreaded application that uses DB2 for its primary database. In the past we've mostly used Identity columns for tables where we needed an auto-generated unique identifier. To do that we would run the below 2 queries in the same transaction:
INSERT INTO tbname (IDENTITY_COL, ...) VALUES (DEFAULT, ...);
SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1;
We are now being pressured to switch to Sequence instead. I know you can use "NEXT VALUE FOR colname" in both INSERT and SELECT statements, but I can't figure out how to both INSERT and SELECT with the same value without risking a race condition in a multithreaded application. For example, if I use:
INSERT INTO tbname (SEQUENCE_COL, ...) VALUES (NEXT VALUE FOR SEQUENCE_COL, ...);
SELECT PREVIOUS VALUE FOR SEQUENCE_COL;
Then there's a possibility another INSERT was run between the above INSERT and SELECT, hence providing me the incorrect value. If I try:
SELECT NEXT VALUE FOR SEQUENCE_COL;
store the value in a variable and pass that in to the INSERT:
INSERT INTO tbname (SEQUENCE_COL, ...) VALUES (variable_value, ...);
Then there's a possibility another thread got the same NEXT VALUE and tries to insert the same value, resulting in a DB2 -803 error. Is it possible to use SEQUENCE columns in a multithreaded environment, or do I need to fight to keep my IDENTITY columns?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
除了 Michael Sharek（正确地）所说的之外：
您的假设那么有可能在上述 INSERT 和 SELECT 之间运行另一个 INSERT，因此为我提供了不正确的值”关于上述语句序列是不正确的 从
“下一个值”和“上一个值”是特定于连接的。
不同线程访问序列永远不会创建“竞争”条件。每个连接都有一个完全隔离的序列“环境”。
In addition to what Michael Sharek (correctly) said:
Your assumption Then there's a possibility another INSERT was run between the above INSERT and SELECT, hence providing me the incorrect value" regarding the above sequence of statements is incorrect.
The "next value" and "previous value" are connection specific.
Access to a sequence from different threads will never create a "race" condition. Each connection has a completely isolated "environment" for the sequence.
你的问题中有一个错误的假设。
这是不正确的。第二个线程将获得不同的 NEXTVAL，并且与第一个线程的值不同。
我还想补充一下我对这部分的看法：
我无法想象有什么充分的理由从身份转向序列。它们基本上是同一件事。
You've got a mistaken assumption in your question.
That's not correct. The second thread would get a different NEXTVAL and not the same value as the first thread.
I also want to add my opinion on this part:
I can't imagine there being a really good reason to switch to sequences from identity. They're basically the same thing.
除了其他正确答案之外，您还可以仅使用单个语句插入一行并返回插入的值，如下所示：
In addition to the other correct answers, you can also just use a single statement to insert a row and return inserted values as follows: