Function on IBM web page not thread-safe?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Larry - 13 Feb 2007 17:49 GMT

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

Reply to this Message

Knut Stolze - 14 Feb 2007 06:49 GMT

> 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
[quoted text clipped - 24 lines]
>
> Am I missing something here?

Yes, fortunately you do. ;-)

The "updateKeyStmt" 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.prepareStatement" methods directly from "getSurrogateKey".
(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.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Knut Stolze - 14 Feb 2007 11:44 GMT

> (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
[quoted text clipped - 9 lines]
> 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

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Tzadik.Vanderhoof@gmail.com - 18 Feb 2007 20:02 GMT

> > On the following page:
>
[quoted text clipped - 35 lines]
> 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.

Reply to this Message

Larry - 18 Feb 2007 20:05 GMT

> > On the following page:
>
[quoted text clipped - 41 lines]
> DB2 z/OS Utilities Development
> IBM Germany

Reply to this Message