Oracle SEQUENCE is indeed non transactional and from the comments that Charles has made it actually appears to NOT be a database problem since multiple users on different app servers have no problem but they do if they are on the same app server. It is almost as if the old COMMIT option 1 from EJB 1.1 is in action ie. once an entity bean is used, that bean with the chosen key cannot be acquired by anyone else until the transaction is completed. I'm not a WAS expert, and WAS 3.5 only supports EJB 1.1 but isn't this an option that can be set ? ie. use commit option 1 or not ? Also, is there a sufficient number of instances available for this bean in the pool ? If not, that could be a problem. Also is there is timeout option for the acquisition of the bean instance ? What is the exact message that WAS is putting out ? Is it a DB error message or a transaction message ?
I would have thought that you could have a stateless session bean that performs the look up and caches n values. When the ssb is called it dolls put the next available number. When it runs out of numbers it gets the next n from an Oracle sequence (non transactional). Since ssb are never passivated the instance variable that stores the next available is OK and 'persistent'. The only hassle is that you cannot guarantee that if there are X instances of the ssb running in the container, then the next row that is inserted using a key may or may not have the next successive number eg. if we have 5 instances and the first ssb used takes numbers 1 -50 and the next ssb used takes 51 -100, then when a client calls a ssb , we wouldn't know whether it would get number 1 or number 51 as the key. The other hassle is that if the container goes down you lose the cached numbers altho if 24x7 and you use an INTEGER (up to 2 billion) a few going missing isn't going to be a problem. This method works fine in clusters, HTH eddie ================= At 10:58 PM 25/07/2002, you wrote: >With an Oracle SEQUENCE, there is no locking and thus no chance for a >deadlock. The whole reason SEQUENCEs exist is to avoid the primary key >locking problems you are trying to solve. You said there was some problem >when using sequences.... can you explain more? > >-eric > > > > > > May Charles > N To: [EMAIL PROTECTED] > <[EMAIL PROTECTED] cc: > M> Subject: Re: Sequence > ID Generator problems -- > Sent by: A optimistic locking > by App S erve r? > mailing list for > Enterprise > JavaBeans > development > <EJB-INTEREST@JAV > A.SUN.COM> > > > 07/25/2002 07:51 > AM > Please respond to > May Charles N > > > > > >That caused the database to deadlock. > > >-----Original Message----- >From: Evan Ireland [mailto:[EMAIL PROTECTED]] >Sent: Wednesday, July 24, 2002 5:11 PM >To: May Charles N >Cc: [EMAIL PROTECTED] >Subject: Re: Sequence ID Generator problems -- optimistic locking by App >Serve r? > > > > > >What about using "select for update" on the SeqIdGenerator EJB? >This should help to avoid the rollbacks. > > >***************************************************************** >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. > >=========================================================================== >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". =========================================================================== 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".
