Managing the product count in the database

Question

Excuse me if this question may seem naive but I have come across a scenario where I need to manage the product count in the database of an e-commerce store.

There is a Product class with an integer variable productCount which signifies the number of available products in the database which is visible to users of the site. Now this class is accessed by several threads or can say several users of the e-commerce site. Everyone is adding or removing the product to his cart.

The ORM framework being used is hibernate

Sample code

@Entity
@Table
class Product{
   @Column
   private int productCount;

   public void addProductToCart(){
     // decrements the product count by 1 & updates the database
   }

   public void removeTheProductFromTheCart(){
    // increments the product count by 1 & updates the database
   }

As it is clear from the code that I need to keep a concurrency check on the product count in the database to prevent lost updates.

Also if several users are trying to add only single left product in the database. Which user's cart the product should be added to?

I did a little research on this

Possible ways I found were

1. Creating a singleton class for Product. That would ensure that just one instance of product is available throughout the application.

2. Synchronize the addProductToCart & removeTheProductFromTheCart methods. which would allow only one thread to update the product count & update the db at a time.

3. Use database concurrency control apply some db transaction isolation level, optimistic/pessimistic locking for the productCount. I am using mysql the default isolation level is REPEATABLE_READ.

What would be the best approach to deal with this?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

IMO a conventional layered approach would help here - not sure how radical a change this would be as don't know the size/maturity of the application but will go ahead and describe it anyway and you can choose which bits are workable.

The theory...

Services   a.k.a. "business logic", "business rules", "domain logic" etc.
 ^
DAOs       a.k.a. "Data Access Objects", "Data Access Layer", "repository" etc.
 ^
Entities   a.k.a. "model" - the ORM representation of the database structure
 ^
Database

It's useful for the entities to be separate from the DAO layer so they are just simple units of storage that you can populate, compare etc. without including methods that act on them. So these are just a class representation of what is in the database and ideally shouldn't be polluted with code that defines how they will be used.

The DAO layer provides the basic CRUD operations that allow these entities to be persisted, retrieved, merged and removed without needing to know the context in which this is done. This is one place where singletons can be useful to prevent multiple instances being created again and again - but use of a singleton doesn't imply thread safety. Personally I'd recommend using Spring to do this (Spring beans are singletons by default) but guess it could be done manually if preferred.

And the services layer is where "domain logic" is implemented, i.e. the specific combinations of operations needed by your application to perform particular functions. Thread safety issues can be tackled here and there will be times when it is needed and times when it isn't.

In practice...

Following this approach you might end up with something like this (lots omitted for brevity):

@Entity
@Table
public class Product {
    @ManyToOne
    @JoinColumn
    private ShoppingCart shoppingCart;
}

@Entity
@Table
public class ShoppingCart {
    @OneToOne
    @JoinColumn
    private User user;

    @OneToMany(mappedBy = "shoppingCart")
    private Set<Product> products;
}

public class ShoppingCartDao { /* persist, merge, remove, findById etc. */ }

@Transactional
public class ProductService() {
    private ConcurrentMap<Integer, Integer> locks = 
        new ConcurrentHashMap<Integer, Integer>();

    public void addProductToCart(final int productId, final int userId) {
        ShoppingCart shoppingCart = shoppingCartDao.findByUserId(userId);                               
        Product product = productDao.findById(productId);
        synchronized(getCacheSyncObject(productId)) {
            if (product.shoppingCart == null) {
                product.setShoppingCart(shoppingCart);
            } else {
                throw new CustomException("Product already reserved.");
            }
        }
    }

    public void removeProductFromCart(final int productId, final int userId) {
        ShoppingCart shoppingCart = shoppingCartDao.findByUserId(userId);
        Product product = productDao.findById(productId);
        if (product.getShoppingCart() != shoppingCart) {
            throw new CustomException("Product not in specified user's cart.");
        } else {
            product.setShoppingCart(null);
        }
    }

    /** @See http://stackoverflow.com/questions/659915#659939 */
    private Object getCacheSyncObject(final Integer id) {
      locks.putIfAbsent(id, id);
      return locks.get(id);
    }       
}