What about using "select for update" on the SeqIdGenerator EJB?
This should help to avoid the rollbacks.
May Charles N wrote:
> 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.
>
--
_______________________________________________________________________________
Evan Ireland Sybase EAServer Engineering [EMAIL PROTECTED]
Wellington, New Zealand +64 4 934-5856
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
