I'm not sure I understand all the details of what you are doing, however it might be that the firebird database versioning scheme would do what you want. With this, and the db set to repeatable read, each transaction is guaranteed a stable view of the committed data as of the time the transaction started, no matter how many other users change it subsequently. Due to the db design, repeatable read is approximately the same speed as read committed. If you read and update within one transaction, the db guarantees that your changes do not conflict with any other tx.
I think this satisfies your needs as you have stated them. I haven't set up a system relying on this in jboss, but can't see any reason it wouldn't work, and I don't know if changing your db is an option. david jencks On 2002.06.21 11:54:21 -0400 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
