«

On the following page:

http://www-128.ibm.com/developerwork...rticle/dm-0407...

IBM discusses a database "surrogate key" generation function, along
with a
listing in Java (Appendix A at the end of the web page). However it
seems the function is not thread-safe!

Suppose for a certain Table X, the SURROGATEKEYVALUE is currently set
to 100, and INCREMENT is set to 1. If 2 threads, A and B, enter
getSurrogateKey at nearly the same time It is possible for the
following sequence to occur:

1. Thread A executes:
updateKeyStmt.execute();
As a result, SURROGATEKEYVALUE is now set to 101.

2. Thread B executes:
updateKeyStmt.execute();
As a result, SURROGATEKEYVALUE is now set to 102.

3. Thread A executes:
ResultSet rs = getKeyStmt.executeQuery();

This will return 102.

4. Thread B executes:
ResultSet rs = getKeyStmt.executeQuery();

This will also return 102 !

Am I missing something here?

On Feb 13, 10:11 am, "Larry" <larry.grant...@gmail.com> wrote:
> On the following page:
>
> http://www-128.ibm.com/developerwork...rticle/dm-0407...
>
> IBM discusses a database "surrogate key" generation function, along
> with a
> listing in Java (Appendix A at the end of the web page). However it
> seems the function is not thread-safe!
>
> Suppose for a certain Table X, the SURROGATEKEYVALUE is currently set
> to 100, and INCREMENT is set to 1. If 2 threads, A and B, enter
> getSurrogateKey at nearly the same time It is possible for the
> following sequence to occur:
>
> 1. Thread A executes:
> updateKeyStmt.execute();
> As a result, SURROGATEKEYVALUE is now set to 101.
>
> 2. Thread B executes:
> updateKeyStmt.execute();
> As a result, SURROGATEKEYVALUE is now set to 102.
>
> 3. Thread A executes:
> ResultSet rs = getKeyStmt.executeQuery();
>
> This will return 102.
>
> 4. Thread B executes:
> ResultSet rs = getKeyStmt.executeQuery();
>
> This will also return 102 !
>
> Am I missing something here?

You need to use an atomic query that updates the data at the same time
as returning the value.

"Larry" <larry.grant...@gmail.com> wrote:
>> On the following page:
>>
>> http://www-128.ibm.com/developerwork...rticle/dm-0407...
>>
>> IBM discusses a database "surrogate key" generation function, along
>> with a
>> listing in Java (Appendix A at the end of the web page). However it
>> seems the function is not thread-safe!
>>
>> Suppose for a certain Table X, the SURROGATEKEYVALUE is currently set
>> to 100, and INCREMENT is set to 1. If 2 threads, A and B, enter
>> getSurrogateKey at nearly the same time It is possible for the
>> following sequence to occur:
>>
>> 1. Thread A executes:
>> updateKeyStmt.execute();
>> As a result, SURROGATEKEYVALUE is now set to 101.
>>
>> 2. Thread B executes:
>> updateKeyStmt.execute();
>> As a result, SURROGATEKEYVALUE is now set to 102.
>>
>> 3. Thread A executes:
>> ResultSet rs = getKeyStmt.executeQuery();
>>
>> This will return 102.
>>
>> 4. Thread B executes:
>> ResultSet rs = getKeyStmt.executeQuery();
>>
>> This will also return 102 !
>>
>> Am I missing something here?

Daniel Pitts wrote:
> You need to use an atomic query that updates the data at the same time
> as returning the value.

The problem is likely database concurrency rather than thread concurrency. Try
wrapping your transactions in COMMIT / ROLLBACK.

- Lew

Larry wrote:
> On the following page:
>
> http://www-128.ibm.com/developerwork...rticle/dm-0407...
>
> IBM discusses a database "surrogate key" generation function, along
> with a
> listing in Java (Appendix A at the end of the web page). However it
> seems the function is not thread-safe!

> Am I missing something here?

No - you are correct.

The code is not thread safe as is.

To do that getSurrogateKey should be made synchronized
to be threadsafe in a single node config and use some
database synchronization technique like a transaction
with transaction islation level serializable or switch
the statements and use SELECT ... FOR UPDATE (I can not
remember if DB2 supports that) to be safe in a multi node
config.

But if you look at the Singleton method, then you
get a clear indication that there is something very wrong
with that code.

Arne

On Feb 13, 7:18 pm, Arne Vajhøj <a...@vajhoej.dk> wrote:
> Larry wrote:
> > On the following page:
>
> >http://www-128.ibm.com/developerwork...rticle/dm-0407...
>
> > IBM discusses a database "surrogate key" generation function, along
> > with a
> > listing in Java (Appendix A at the end of the web page). However it
> > seems the function is not thread-safe!
> > Am I missing something here?
>
> No - you are correct.
>
> The code is not thread safe as is.
>

So what is it doing on an official IBM site?

Larry wrote:
> On Feb 13, 7:18 pm, Arne Vajhøj <a...@vajhoej.dk> wrote:
>> Larry wrote:
>>> On the following page:
>>> http://www-128.ibm.com/developerwork...rticle/dm-0407...
>>> IBM discusses a database "surrogate key" generation function, along
>>> with a
>>> listing in Java (Appendix A at the end of the web page). However it
>>> seems the function is not thread-safe!
>>> Am I missing something here?
>> No - you are correct.
>>
>> The code is not thread safe as is.
>
> So what is it doing on an official IBM site?

I do not know.

Maybe the article is written by a DB2 expert
not a Java expert.

Maybe an early draft of the code got posted instead
of the final.

Mistakes happen in IBM also.

Arne

On Feb 13, 9:57 pm, Arne Vajhøj <a...@vajhoej.dk> wrote:
> Larry wrote:
> > On Feb 13, 7:18 pm, Arne Vajhøj <a...@vajhoej.dk> wrote:
> >> Larry wrote:
> >>> On the following page:
> >>>http://www-128.ibm.com/developerwork...rticle/dm-0407....
> >>> IBM discusses a database "surrogate key" generation function, along
> >>> with a
> >>> listing in Java (Appendix A at the end of the web page). However it
> >>> seems the function is not thread-safe!
> >>> Am I missing something here?
> >> No - you are correct.
>
> >> The code is not thread safe as is.
>
> > So what is it doing on an official IBM site?
>
> I do not know.
>
> Maybe the article is written by a DB2 expert
> not a Java expert.
>
> Maybe an early draft of the code got posted instead
> of the final.
>
> Mistakes happen in IBM also.
>
> Arne


The feedback page lists Java and EJB among the author's specialties.
In any event, I have emailed my concerns to the author and also left
feedback on the page. It seems that the page was put up in 2004, so
it's still strange that the error is still there.

Larry wrote:

> The feedback page lists Java and EJB among the author's specialties.
> In any event, I have emailed my concerns to the author and also left
> feedback on the page. It seems that the page was put up in 2004, so
> it's still strange that the error is still there.

Maybe it's not

When I try to visit:

http://www-128.ibm.com/developerwork...rticle/dm-0407

I get an error:

Our apologies...
The page you requested cannot be displayed

(both today, and when the OP originally mentioned it).

-- chris

Chris Uppal wrote:
> Larry wrote:
>> The feedback page lists Java and EJB among the author's specialties.
>> In any event, I have emailed my concerns to the author and also left
>> feedback on the page. It seems that the page was put up in 2004, so
>> it's still strange that the error is still there.
>
> Maybe it's not
>
> When I try to visit:
>
> http://www-128.ibm.com/developerwork...rticle/dm-0407
>
> I get an error:
>
> Our apologies...
> The page you requested cannot be displayed
>
> (both today, and when the OP originally mentioned it).

That is just because the URL was cut short in the original post.

The correct URL is:

http://www-128.ibm.com/developerwork...ang/index.html

(I hope it goes through without being truncated)

Arne

Arne Vajhøj wrote:

> > http://www-128.ibm.com/developerwork...rticle/dm-0407
[...]

> That is just because the URL was cut short in the original post.
>
> The correct URL is:
>
>
http://www-128.ibm.com/developerwork...ang/index.html

Thank you.

But how did you manage to guess the full URL ? Other people seemed to be able
to read the article too, but while it's common enough to add a missing .html or
index.htm to the end of an URL, in this case the missing bit was

zhang/index.html

Am I alone in lacking the clairvoyant skills needed to reconstruct it ?

-- chris