I think all you need is database pessimistic locking (SELECT FOR UPDATE) which will row lock the inventory item in the database for the length of the transaction as soon as it is loaded (finder or ejbLoad). You can turn this on in JBossCMP with the row-locking element and in JAWS I think you use the select-for-update element.
-dain Jon Swinth wrote: > 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
