I'm not sure that I see a difference between using straight SQL in the session bean versus finding the appropriate entity bean and querying it for the current inventory. The advantage to using the entity bean is, of course, the portability that you mention. I would think that two methods on the session bean - checkInventory and reserveInventory would do the trick. Each would look up the appropriate entity and query for available inventory and reserve would reduce inventory if available. They would both be marked as requiring a transaction. Assuming that transactions are deferred to the underlying database, separate servers should play nicely together. Using a transaction isolation level of repeatable read or serializable (ugh) would then keep the data consistent within each transaction.
Ultimately, unless the entity bean is marked as read-only, writing SQL in the session bean to look up inventory vs. using the entity bean shouldn't make a difference wrt transactions. David >>> [EMAIL PROTECTED] 06/21/02 11:54AM >>> Thanks Marc for the reply. The issue with your solution is that the inventory checks are for different users than the where we are updating inventory. Each time an item is displayed on a page, the server checks inventory to see if it is still in stock. This is required because the data keeps changing. If the user wants the item then they will click add-to-cart and that process will lock the inventory, double check that there is still one available and reserve a copy all in one transaction. Lets say user A on one JBoss presses the add-to-cart button. The record is locked and checked to see if there is one. User B on a different JBoss requests a page that has the same item on it. The system selects back the value for user B. User A finishes the transaction and commits. User B finishes the transaction. JBoss does the safety check for User B, determines that the record has changed, and throws an exception. This may not seem very likely, but in my experience it can happen at high volume, especially as the DB starts slowing down because of a great many requests. Also keep in mind that there will probably be 100 User Bs to 1 User A. I think I have two options at this point. Make the User B queries lock the row so that all users wait in line for access and nobody gets an error. Or, make the inventory query from a session bean using SQL. I posted this to see if someone knew of a better solution that uses CMP so that code is portable. For now, I am going with the direct SQL option because it is fast. I have also made the User A requests lock the rows so they play nice with each other. > From: Marc Zampetti <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: Re: [JBoss-user] Trying to get outside a transaction > Reply-To: [EMAIL PROTECTED] > > > I think you are over-thinking the problem. All you need to do is have a > Session Bean method that performs the inventory check, and mark the > method as Requires Transaction (or Requires New). Then, when you check > the inventory, reserve a copy at that time. Thus, the check is the > reserve, and either it works, or it doesn't. If it works, you have the > copy reserved, and it doesn't, you know that it is not avaiable, and can > display the appropriate message to the user. The DB locking should > ensure that multile jBoss servers can't be caught in a race condiction. > On the db side, you would do something like this: > > begin tran > > update table, reduce inventory by 1 > > select info back that you need. > > commit transaction. > > The update will reduce the eventory, and hold a lock on the page or row > (depending upon the db setup). Then your select will return the info you > need to determine if the inventory was still available. > > If you are using an Entity Bean, then the ejbLoad should be called when > the transaction starts. I'm not sure how jBoss does it, but you would > want to set the isolation level to REPEATABLE_READ, which basically > locks the db page or row with the select and holds it. In this case, you > must make sure that the entity is releases quickly, otherwise you are > holding locks in the database for a long time, which will grind your > system to a halt. ------------------------------------------------------- Sponsored by: ThinkGeek at http://www.ThinkGeek.com/ _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user ------------------------------------------------------- Sponsored by: ThinkGeek at http://www.ThinkGeek.com/ _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
