How to obtain a DB2 Sequence Value in a Multithreaded Application

Question

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?

Answer 1

In addition to what Michael Sharek (correctly) said:

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.

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.

Answer 2

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 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

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:

We are now being pressured to switch to Sequence instead.

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

Answer 3

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, ...)
)