Hi All,

I am putting together a generic online shop webapp using the open session in view pattern (I begin the Hibernate session as soon as the request thread hits my control servlet and end it after the page has been rendered with a servlet filter).

Almost everything is working fine, but I do have a couple of issues. The first problem is that if a user clicks on the 'add to basket' button several times in quick succession (i.e. before waiting for the request to be processed and the response to return) then several instances of the same item get added to their basket. This is particularly bad because multiple clicks like this will cause the item to be added even if the first click should reduce the stock to zero!

Here is the code in my AddToBasketAction class:



First point to note: rather than storing an actual User object inside my HttpSession, I'm storing the user id - every time I need to get hold of an instance of the User object I just use my UserDAO to get me a fresh copy from the database. Likewise with the current Product etc - I'm doing a lookup using the id each time. This is bad, right?

Let's say I have a Product with a "stock" value of 1. A user should be able to buy only one instance of that Product. Clearly each time a request thread enters the above method, it's looking up the Product from the database, seeing that the stock is greater than zero and proceeding with adding the Product to the basket. Now if a second request thread enters the above method before the hibernate session associated with the previous request has closed, the second request thread will also see that the Product has a stock value of 1 and allow the basket add to proceed. Likewise with the 3rd, 4th and 5th request threads.

I've tried all mannor of session.flush() and session.refresh(product) method calls in an attempt to make sure my view of the data within this method is consistent with the underlying database, but I still seem to see the Product object as it was before the first request thread added it to the basket. I've also tried to synchronize the method, and also to set the LockMode to UPGRADE when obtaining the Product instance from the hibernate session.

If anyone has any ideas I'd be grateful!

Cheers,
Richard.

Hibernate version: 3.2.3

Name and version of the database you are using: MySQL 5

After a bit more investigation I'm still puzzled. I've played around a bit with transaction demarcation and synchronization, so my code now looks like this:



productDAO.findById is getting the Product object like this:

getSession().get(Product.class, id, LockMode.UPGRADE);

After turning on SQL logging, I can see that hibernate is generating the following statement:

select id from ols.product where id =? for update

I would expect this to cause any thread that subsequently attempts a select for update on that particular row to block until the first thread has committed its transaction. What appears to happen instead is that every subsequent request thread retrieves a stale view of the Product object - one where it's value for "stock" hasn't been decremented by the previous thread.

Does anyone have a suggestion?

Cheers.

You could have transaction isolation problems. Increase the isolation level on your database connection and see if that helps you. In addition, using a synchronized block in a web or enterprise application is ugly. Try to avoid it.




Farzad-

Thanks - this seems to have done the trick. After setting my transaction isolation level to 8 (TRANSACTION_SERIALIZABLE according to the java.sql.Connection javadoc) I now get a LockAcquisitionException if I attempt to lock the same Product for a second time in my action class. Which is good, because I can catch it & deal with the situation appropriately.

I've also removed the synchronized block (it wasn't actually preventing the problem anyway!)

Cheers,
Richard.

There is only one thing, TRANSACTION_SERIALIZABLE is very restrictive and it will have a huge performance impact on you application. I am not sure but you should be able to get around this by even READ_COMMITTED isolation level.


Farzad-

Hi Farzad,

Yes - I did wonder about the performance impact. However I tried the 3 other (less restrictive) transaction isolation levels before TRANSACTION_SERIALIZABLE but they didn't appear to make any difference. Perhaps there's another solution?

I've thought about implementing my own "locking" logic in order to prevent this from happening but I'm still hoping that I can find a way to get Hibernate to do it for me.

In any case, I'll play around again with the other isolation levels and see if I have any luck.

Cheers.

Hi,

After a little more time trawling through this forum, I found a really old posting from a guy with a similar problem (http://forum.hibernate.org/viewtopic.ph ... lock+stale). He ended up resolving his issue by adding



to his object mapping file. I found that after adding select-before-update to my Hibernate mapping file for Product, I was able to reduce the transaction isolation to TRANSACTION_REPEATABLE_READ and still get the desired result.

I think this is a little better from a performance point of view.

Cheers,
Richard.