Function on IBM web page not thread-safe?

[Larry]

On the following page:

http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0407zhang/index.html

IBM discusses a 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 SURROGATEKEYVAL UE 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.e xecute();
As a result, SURROGATEKEYVAL UE is now set to 101.

2. Thread B executes:
updateKeyStmt.e xecute();
As a result, SURROGATEKEYVAL UE is now set to 102.

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

This will return 102.

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

This will also return 102 !

Am I missing something here?

[Knut Stolze]

Re: Function on IBM web page not thread-safe?

Larry wrote:

On the following page:
>
>

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

>
IBM discusses a 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 SURROGATEKEYVAL UE 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.e xecute();
As a result, SURROGATEKEYVAL UE is now set to 101.
>
2. Thread B executes:
updateKeyStmt.e xecute();
As a result, SURROGATEKEYVAL UE is now set to 102.
>
3. Thread A executes:
ResultSet rs = getKeyStmt.exec uteQuery();
>
This will return 102.
>
4. Thread B executes:
ResultSet rs = getKeyStmt.exec uteQuery();
>
This will also return 102 !
>
Am I missing something here?

Yes, fortunately you do. ;-)

The "updateKeyS tmt" is an UPDATE statement which will result in an X-lock to
be set on the KEY table for a specific row. Thus, thread B must wait at
step 2 until thread A has issued a COMMIT or ROLLBACK statement and
released that lock.

If you really want to use the KeyManager class (and not sequences, which are
better performing), then you should consider the following as well:

(1) DB2 uses its own class loader in the JVM tat executes the UDF. This
class loader ensures that objects created for one UDF call are completely
independent of objects created for another invocation. In fact, objects
from different invocations don't know anything about each other.
Therefore, the use of the singleton doesn't have any benefits. Just call
the "connection.pre pareStatement" methods directly from "getSurrogateKe y".
(But stick with the parameter markers for basic security reasons.)

(2) Take care of NULLs returned from the UDF.

(3) I would make the schema name for each table a separate parameter of the
UDF. That would be a cleaner interface.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Knut Stolze]

Re: Function on IBM web page not thread-safe?

Knut Stolze wrote:

(1) DB2 uses its own class loader in the JVM tat executes the UDF. This
class loader ensures that objects created for one UDF call are completely
independent of objects created for another invocation. In fact, objects
from different invocations don't know anything about each other.
Therefore, the use of the singleton doesn't have any benefits. Just call
the "connection.pre pareStatement" methods directly from "getSurrogateKe y".
(But stick with the parameter markers for basic security reasons.)
>
(2) Take care of NULLs returned from the UDF.
>
(3) I would make the schema name for each table a separate parameter of
the
UDF. That would be a cleaner interface.

Actually, these 3 points are only relevant if you want to implement a key
manager as a user-defined function running inside the DB2 database. The
article talks about a client-side/application-specific management.

With UDFs, things are a bit more complicated. You have to consider
(1) Invoking the UDF from different SQL sessions (connections)
(2) Calling the UDF in different SQL statements executed on the same
connection.
(3) Calling the UDF in the same SQL statement without intra-parallelism.
(4) Calling the UDF in the same SQL statement with intra-parallelism.

Option (2) and option (4) require some more thought because neither locking
nor the inherent sequential execution w/o parallelism have to be
considered. In such cases, it may be a good idea to put the UPDATE and the
SELECT into a single statement:

SELECT ...
FROM NEW TABLE ( UPDATE ... ) AS t

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Tzadik.Vanderhoof@gmail.com]

Re: Function on IBM web page not thread-safe?

On Feb 14, 1:49 am, Knut Stolze <sto...@de.ibm. comwrote:

Larry wrote:

On the following page:

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

IBM discusses a 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 SURROGATEKEYVAL UE 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.e xecute();
As a result, SURROGATEKEYVAL UE is now set to 101.

>

2. Thread B executes:
updateKeyStmt.e xecute();
As a result, SURROGATEKEYVAL UE is now set to 102.

>

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

>

This will return 102.

>

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

>

This will also return 102 !

>

Am I missing something here?

>
Yes, fortunately you do. ;-)
>
The "updateKeyS tmt" is an UPDATE statement which will result in an X-lock to
be set on the KEY table for a specific row. Thus, thread B must wait at
step 2 until thread A has issued a COMMIT or ROLLBACK statement and
released that lock.

I don't see any COMMIT or ROLLBACK being issued in the code. And
furthermore, the same connection is being left open for the entire
lifetime of the application. So this code seems to assume that
AUTOCOMMIT is ON. So your statement that an X-lock is placed on the
row does not seem to be correct.

[Larry]

Re: Function on IBM web page not thread-safe?

On Feb 14, 1:49 am, Knut Stolze <sto...@de.ibm. comwrote:

Larry wrote:

On the following page:

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

IBM discusses a 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 SURROGATEKEYVAL UE 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.e xecute();
As a result, SURROGATEKEYVAL UE is now set to 101.

>

2. Thread B executes:
updateKeyStmt.e xecute();
As a result, SURROGATEKEYVAL UE is now set to 102.

>

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

>

This will return 102.

>

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

>

This will also return 102 !

>

Am I missing something here?

>
Yes, fortunately you do. ;-)
>
The "updateKeyS tmt" is an UPDATE statement which will result in an X-lock to
be set on the KEY table for a specific row. Thus, thread B must wait at
step 2 until thread A has issued a COMMIT or ROLLBACK statement and
released that lock.
>
(snip)
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

I don't see any COMMIT or ROLLBACK being issued in the code. And
furthermore, the same connection is being left open for the entire
lifetime of the application. So this code seems to assume that
AUTOCOMMIT is ON. So your statement that an X-lock is placed on the
row does not seem to be correct.