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
