I am using Java Spring Framework and Hibernate. I am doing a e-commerce web application where public user is able to purchase product from the web app. Each product will have a quantity count.
Issue
I want to prevent the scenario where two concurrent users purchase the same product is left with quantity value 1. In this case, only the first user should get to purchase the product while the second user should receive a "no stock available" message.
Workflow
I intend to decrement the quantity count and then send the purchase information to the payment gateway. If payment gateway encounters payment issue, I will increment the quantity count. The lock will be released before purchase info is sent to payment gateway.
So my question is,
1) I would like to confirm this answer on pessimistic locking does help solves my issue.
2) Is my workflow ok? Is there a better way to do this?
You can also use optimistic locking and prevent possible lost updates.
With optimistic locking, if two users have loaded the same entity with a quantity of 1, and one user has just made a purchase and decremented the quantity counter, then the second user won't be able to UPDATE the product entity since his Product version doesn't match the one that was found in the database at the moment when the entity was read.
If the entity version changes, then the UPDATE statement that tries to match the old version will return a 0 when the executeUpdate method of the PreparedStatement is called. That's when Hibernate will detect the stale state situation and throw an OptimisticLockException.
The Spring TransactionInterceptor will catch the OptimisticLockException, and the associated transaction will be rolled back.
The optimistic locking approach scales better than pessimistic locking, and it also works on multi-request logical transactions.