SQL 如何在多线程应用程序中获取 DB2 序列值

How to obtain a DB2 Sequence Value in a Multithreaded Application

提问by Mike Carey

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:

我正在开发一个使用 DB2 作为其主数据库的多线程应用程序。过去，我们主要将 Identity 列用于需要自动生成的唯一标识符的表。为此，我们将在同一事务中运行以下 2 个查询：

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:

我们现在被迫改用 Sequence。我知道您可以在 INSERT 和 SELECT 语句中使用“NEXT VALUE FOR colname”，但我无法弄清楚如何在多线程应用程序中不冒竞争条件的情况下使用相同的值同时插入和选择。例如，如果我使用：

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:

然后有可能在上述 INSERT 和 SELECT 之间运行另一个 INSERT，因此提供了不正确的值。如果我尝试：

SELECT NEXT VALUE FOR SEQUENCE_COL;

store the value in a variable and pass that in to the INSERT:

将值存储在变量中并将其传递给 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?

然后有可能另一个线程获得相同的 NEXT VALUE 并尝试插入相同的值，从而导致 DB2 -803 错误。是否可以在多线程环境中使用 SEQUENCE 列，或者我是否需要努力保留我的 IDENTITY 列？

采纳答案by a_horse_with_no_name

In addition to what Michael Sharek (correctly) said:

除了 Michael Sharek（正确）所说的：

INSERT INTO tbname (SEQUENCE_COL, ...) VALUES (NEXT VALUE FOR SEQUENCE_COL, ...);
SELECT PREVIOUS VALUE FOR SEQUENCE_COL;

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.

您的假设然后有可能在上述 INSERT 和 SELECT 之间运行另一个 INSERT，因此为我提供了不正确的值“关于上述语句序列是不正确的。

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.

从不同线程访问序列永远不会产生“竞争”条件。每个连接都有一个完全隔离的序列“环境”。

回答by Michael Sharek

You've got a mistaken assumption in your question.

你的问题有一个错误的假设。

If I try:

如果我尝试：

SELECT NEXT VALUE FOR SEQUENCE_COL;

store the value in a variable and pass that in to the INSERT:

将值存储在变量中并将其传递给 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

然后有可能另一个线程获得相同的 NEXT VALUE 并尝试插入相同的值

That's not correct. The second thread would get a different NEXTVAL and not the same value as the first thread.

那不正确。第二个线程将获得不同的 NEXTVAL 并且与第一个线程的值不同。

I also want to add my opinion on this part:

我还想补充一下我对这部分的看法：

We are now being pressured to switch to Sequence instead.

我们现在被迫改用 Sequence。

I can't imagine there being a really good reason to switch to sequences from identity. They're basically the same thing.

我无法想象有一个很好的理由从身份切换到序列。它们基本上是一样的。

回答by ahu

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:

除了其他正确答案之外，您还可以仅使用单个语句插入一行并返回插入的值，如下所示：

SELECT SEQUENCE_COL FROM NEW TABLE (
  INSERT INTO tbname (SEQUENCE_COL, ...) VALUES (NEXT VALUE FOR MY_SEQUENCE, ...)
)