What is the spring transaction isolation level used to maintain a counter for a sold product?

Depending on how you get the total number of items sold, the options available may vary:

1. If you have counted the number of items sold dynamically using an sum

order request

I presume that in this case you have an isolation setting SERIALIZABLE

for the transaction as it is the only one that supports range locks

and prevents phantom reads

. However, I would not recommend going with this isolation level as it has a significant impact on your system (or is used very carefully in well-designed places).

Links: https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html#isolevel_serializable

2. If you maintain a counter on the product or some other line related to the product

In this case, I would probably recommend using row level locking

eg select for update

in a service method that checks for product availability and increases the number of items sold. The high-level product placement algorithm can be similar to the following steps:

• Get a string that stores the number of items remaining / sold using a query select for update
  
  ( @Lock(LockModeType.PESSIMISTIC_WRITE)
  
  in a repository method).
• Make sure the resulting string has the updated field values ​​as it can be fetched from the Hibernate session level cache (hibernate will just execute the request select for update
  
  for id
  
  just to acquire the lock). You can achieve this by calling "entityManager.refresh (entity)".
• Check the count
  
  line box , and if this value matches your business rules, then increase / decrease it.
• Save the object, clear your hibernate session and commit the transaction (explicitly or implicitly).

Meta code below:

    @Transactional
    public Product performPlacement(@Nonnull final Long id) {
        Assert.notNull(id, "Product id should not be null");
        entityManager.flush();
        final Product product = entityManager.find(Product.class, id, LockModeType.PESSIMISTIC_WRITE);
        // Make sure to get latest version from database after acquiring lock, 
        // since if a load was performed in the same hibernate session then hibernate will only acquire the lock but use fields from the cache
        entityManager.refresh(product);
        // Execute check and booking operations
        // This method call could just check if availableCount > 0
        if(product.isAvailableForPurchase()) {
            // This methods could potentially just decrement the available count, eg, --availableCount
            product.registerPurchase();
        }
        // Persist the updated product 
        entityManager.persist(product);
        entityManager.flush();
        return product;
    }


      

        
        
        
      

    

This approach will ensure that no two threads / transactions will ever check and update on the same row storing the item counter at the same time .

However, because of this, it will also have some effect of degrading your system's performance, so you need to make sure that the atomic increment / decrement is used as far in the shopping flow as possible and as infrequent (for example, right in the checkout routine when customer reaches pay

). Another useful trick to minimize blocking effects is to add the "count" column not to the product itself, but to another table associated with the product. This will prevent product row locks because locks will be acquired on a different row / table combination that is exclusively used during the validation phase.

Links: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html

Summary

Please note that both of these technologies introduce additional sync points in your system, which reduces bandwidth. Therefore, please take a close look at the impact it has on your system using a performance test or any other technique that is used in your project to measure throughput.

More often than not, online retailers prefer to resell / order certain items rather than impact performance.

Hope it helps.