Managing the product count in the database

3. Use database concurrency control

Why?

• 1 & 2 are OK if your e-commerce app is absolutely the only way to modify the product count. That's a big if. In the course of doing business and maintaining inventory the store may need other ways to update the product count and the e-commerce app may not be the ideal solution. A database, on the other hand, is generally easier to hook into different applications that aid the inventory process of your store.

• Database products usually have a lot of fail-safe mechanisms so that if something goes wrong you can trace what transactions succeeded, which didn't, and you can roll back to a specific point in time. A java program floating in memory doesn't have this out of the box, you would have to develop that yourself if you did 1 or 2. Spring and Hibernate and other things like that are certainly better than nothing but compare what they offer and what a database offers in terms of recovering from some electronic disaster.

For the first two possibilities you are considering, those work only if you are restricted to deploying only a single instance of the application. You can't have singletons managed across multiple application instances, you can't have synchronization across multiple JVMs. So if you go with one of these your deployment options will be constrained, the only way to deploy multiple instances of the application is if you do something like pin the sessions to a specific instance, which is bad for load-balancing. So these both seem undesirable.

The approach of getting the product counts from the database has the advantage that it remains valid as your application scales up across multiple instances without messing up load-balancing.

You may think, this will only be one instance on one server so I can get by with this. But at the time you're building an application it may not be entirely clear how the application will be deployed (I've been in situations where we didn't know what the plan was until the application was set up in a preprod environment), or at a later date there might be a reason to change how an application is deployed; if your application has more-than-expected load then it may be beneficial to set up a second box.

One thing that is not apparent to me is how vital it is that the product count is actually correct. In different business domains (airline tickets, shipping) it's common to overbook, and it might be more trouble than it's worth to keep a 100% accurate count, especially if it's at an early point in the process such as adding an item to the shopping cart (compared to the point where the customer actually commits to making a purchase). At the time the customer buys something it may make more sense to make sure you reserve those items with a database transaction (or not, cf. overbooking again).

It seems common in web applications to expect a low conversion rate from items in the cart to items actually purchased. Keep in mind what level of accuracy for your counts is appropriate for your business domain.

The right way to do it is use database locks, as it designed for this work. And if you are using hibernate it's pretty simple with LockRequest:

Session session = sessionFactory.openSession()Transaction transaction;boolean productTaken = false;try {    transaction = session.beginTransaction();    Product product = session.get(Product.class, id);    if (product == null)        throw ...    Session.LockRequest lockRequest = session.buildLockRequest(LockOptions.UPGRADE);    lockRequest.lock(product);    productTaken = product.take();    if (productTaken) {        session.update(product);        transaction.commit();    }} finally {    if (transaction != null && transaction.isActive())        transaction.rollback();    session.close();}

Here we are fetching product from database for updating which prevents any concurrent updates.