What used to appear to be a problem with generating primary keys has now become a scalability issue. Read on to understand how we no longer get duplicate keys. The mystery is why, when we have several users running tests, one user per application server (WebSphere 3.5.4), and all app servers running against the same database (Oracle 8.1.7, thin driver), that everything works correctly, whereas when we have several users running tests, all against the same application server on one database, then we get what looks like optimistic locking behavior. I need to eliminate the appearance of optimistic locking. Any suggestions or even comments about why we are getting this behavior are greatly appreciated...
We were occasionally getting duplicate key exceptions for the generated primary keys for our container-managed EJBs. We devised a setup in our application where duplicate keys would always occur and then attempted to correct the problem.
Previously, we were generating primary keys with an Entity Bean solution (a CMP EJB, keyed by class name, with a value being the last authorized id) which handed out ids in blocks of size n (an environment variable set to size 25) so that the EJB only got hit for every nth primary key request for that particular class. We believe that the reason we were getting duplicate keys was that the settings on the relevant nextId() method on the EJB were TX_REQUIRED, TRANSACTION_READ_COMMITTED and that concurrent requests to identical EJBs were reading the same initial value.
We were able to circumvent the duplicate key exceptions with this Entity Bean solution with some deployment descriptor changes to the SeqIdGenerator EJB (TX_REQUIRES_NEW, TRANSACTION_SERIALIZABLE). However, when multiple users made concurrent requests on the same application server, only the first request would succeed; the others would have their transactions rolled back. The others would have to resubmit their requests until they eventually succeeded.
We then implemented a solution which makes use of JDBC calls to Oracle Sequences, using a WebSphere DataSource. Currently this hits the sequences for every single primary key request, though it will be easy to make it so that it issues the SQL for only every n calls as defined by some environment variable. We had to declare the Session Bean method which issues the JDBC call as TX_NOT_SUPPORTED in order to get the calling transaction not to roll back (marking the datasource as JTA-enabled did not help -- in fact it made entire application fail catastrophically). This solution ends up behaving equivalently to the Entity Bean solution, with the same problem: when there are multiple users with concurrent requests, the first one completes and the others must resubmit until they eventually succeed.
Basically, we have two equivalent solutions to the duplicate key problem. However, we now have a problem which looks like simultaneous requests are being optimistically locked out by the application server itself (not by the database), since when we have several users each hitting their own application servers which talk to the same database, all requests complete with no problems or deadlocking, but if we have several users hit the same application server then only the first request in will finish. The others must resubmit their requests repeatedly until they each finally complete.
Charles May
Mellon Financial Corporation/TDD
[EMAIL PROTECTED]
***************************************************************** DISCLAIMER: The information contained in this e-mail may be confidential and is intended solely for the use of the named addressee. Access, copying or re-use of the e-mail or any information contained therein by any other person is not authorized. If you are not the intended recipient please notify us immediately by returning the e-mail to the originator.
