Another aspect to the problem (I should have mentioned it in my original post) is that we have an EIS (a customer care and billing system) sitting between us and the db. For particularly dubious reasons, we cannot write to the database directly, but have to use the EIS's bespoke API (I have written a resource adapter to handle this).
It is within the guts of this single "create entity" API call that the 5 seconds or so are elapsing. We *do* have some control over the structure of the underlying table: When I added a unique constraint on the primary key field I suddenly found that the system could only handle one concurrent user! (It seemed obvious in retrospect) Somebody suggested I use a Singleton which maintains a list of primary keys currently being created. The first thing the create process would then do is check to see if the id exists in the list. If not, it would add it to the list and the perform the creation processing, remove it from the list and commit the transaction. If it finds the id is already on the list, it throws an exception. My problem with this solution is twofold: First, the Singlton would have to be replaced with some enterprise suitable object backed by a shared database. This not only seems like an over complication of the system, but also has a performance impact. (Although you could argue that with an existing transaction time of 5 seconds, the few extra milliseconds of reaching out to the db a couple of times is negligable) Secondly, the system the looses the theoretical notion of a transaction. More specifically, the isolation principle is lost since a second transaction's success is dependant upon the existence of the incomplete first transaction. Now my management may argue that they couldn't care a fig for the isolation principle, and if I cannot find find a better solution this is what I will be forced to implement. There *must* be a better solution. On Wednesday 17 July 2002 15:38 pm, you wrote: > Richard, > > The INSERT will acquire a (table) lock depending on the transaction > isolation level you specify for the datasource used by your EJBs. > > So, the possible solutions are: > (a) Lower the isolation level (if your application can get away with > it), Or > (b) Specify that the ejbCreate() needs to run in its own transaction > (RequiresNew). Therefore, locks will be now be held for a smaller > duration of time. But you are now breaking up your long running > transaction into smaller ones; and this might not be acceptable. > (c) Explore the use of lock timeouts (can be set as properties in > some JDBC drivers). > (d) Explore the use of CMP - here the Container has a lot more control > over when SQLs are sent to the database and might be option iff > your EJB Container has the ability to defer writes (INSERTs, etc) > to the database. > > -krish > > > -----Original Message----- > > From: A mailing list for Java(tm) 2 Platform, Enterprise Edition > > [mailto:[EMAIL PROTECTED]]On Behalf Of Richard S.Martin > > Sent: Wednesday, July 17, 2002 6:14 PM > > To: [EMAIL PROTECTED] > > Subject: Entity creation locking problem > > > > > > Problem: > > > > 1. The uniqueness of the entity beans is based upon an unique constraint > > on the primary key field in the underyling database. > > > > 2. The ejbCreate method on the BMP entity bean, due to various essential > > business processes, takes a long time to write the data to the table. (in > > the region of 5-10 secs.) > > > > 3. As soon as the INSERT statement occurs, the transaction acquires a > > table-lock to ensure the uniquness of the primary key. > > > > 4. The lock is only released when the transaction commits or rollsback. > > > > 5. The consequence of this is that every ejbCreate causes the table to be > > exclusively locked for about 5 seconds, meaning we can handle only a > > single ejbCreate at any time! > > > > How can this problem be resolved? > > > > > > > > ========================================================================= > >===== This email and any files transmitted with it are confidential and > > intended solely for the use of the individual or entity to whom they are > > addressed. All information is the view of the individual and not > > necessarily the company. If you are not the intended recipient you are > > hereby notified that any dissemination, distribution, or copying of this > > communication and its attachments is strictly prohibited. If you have > > received this email in error please notify: [EMAIL PROTECTED] > > > > > > ========================================================================= > >===== > > > > ========================================================================= > >== To unsubscribe, send email to [EMAIL PROTECTED] and include in the > > body of the message "signoff J2EE-INTEREST". For general help, send > > email to [EMAIL PROTECTED] and include in the body of the message > > "help". ============================================================================== This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. All information is the view of the individual and not necessarily the company. If you are not the intended recipient you are hereby notified that any dissemination, distribution, or copying of this communication and its attachments is strictly prohibited. If you have received this email in error please notify: [EMAIL PROTECTED] ============================================================================== =========================================================================== 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".
