By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,589 Members | 2,276 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,589 IT Pros & Developers. It's quick & easy.

Function on IBM web page not thread-safe?

P: n/a
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 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?

Feb 13 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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 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?
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.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Feb 14 '07 #2

P: n/a
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.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.
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
Feb 14 '07 #3

P: n/a
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 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?

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

Feb 18 '07 #4

P: n/a
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 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?

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.

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

Feb 18 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.