管理数据库中的产品计数

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.

有一个带有整数变量productCount的Product类，它表示数据库中对站点用户可见的可用产品数量。现在，这个类被几个线程访问，或者说是电子商务网站的几个用户。每个人都在添加或删除产品到他的购物车中。

The ORM framework being used is hibernate

使用的ORM框架是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.

   同步addProductToCart和removeTheProductFromTheCart方法。它只允许一个线程一次更新产品计数和更新db。

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.

   使用数据库并发控制应用一些db事务隔离级别，对productCount进行乐观/悲观锁定。我使用的是mysql，默认的隔离级别是REPEATABLE_READ。

What would be the best approach to deal with this?

处理这个问题最好的方法是什么?

9 个解决方案

#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 you are never going to be able to deploy multiple instances of your application in a cluster or farm, or even two instances with a load balancer in front. So these both seem undesirable since they only work in single-point-of-failure mode.

对于您正在考虑的前两种可能性，只有当您仅限于部署应用程序的单个实例时，这些可能性才有效。不能跨多个应用程序实例管理单例，不能跨多个jvm进行同步。因此，如果你使用其中的一个，你将永远无法在集群或集群中部署应用程序的多个实例，甚至无法在前面部署两个负载均衡器的实例。所以这两种方法似乎都不可取，因为它们只在单点故障模式下工作。

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.

从数据库获取产品计数的方法的优点是，当应用程序跨多个实例扩展时，它仍然有效。

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.

您可能会认为，这将是一个服务器上的一个实例，所以我可以使用这个。但是当时你正在构建一个应用程序可能不完全清楚如何部署应用程序(我一直在我们不知道的情况下计划,直到应用程序成立于preprod环境),或在稍后的日期可能有理由改变一个应用程序部署;如果您的应用程序的负载超过预期，那么建立第二个框可能是有益的。

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).

有一件事对我来说不太明显，那就是产品计数是否正确。在不同业务领域(机票、运输)超额预定是很常见的,它可能是更多的麻烦比值得保持100%准确计数,尤其是如果它是在这个过程中早期,如将一个条目添加到购物车(相比,客户承诺购买)。当客户购买某样东西时，确保您使用数据库事务(或不使用cf. overbooking)保留这些物品可能更有意义。

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.

在web应用程序中，期望从购物车中的商品到实际购买的商品的转化率很低，这似乎很常见。请记住，您的计数的精确度适合您的业务领域。

#2

3. Use database concurrency control

3所示。使用数据库并发控制

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.

  如果你的电子商务应用程序绝对是修改产品数量的唯一方法，那么1 & 2是可以的。这是一个很大的未知数。在做生意和维护库存的过程中，商店可能需要其他方法来更新产品数量，而电子商务应用程序可能不是理想的解决方案。另一方面，数据库通常更容易与不同的应用程序挂钩，以帮助存储的库存处理。

• 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.

  数据库产品通常有很多故障保护机制，因此如果出现问题，您可以跟踪哪些事务成功了，哪些没有成功，并且您可以回滚到特定的时间点。一个在内存中浮动的java程序没有现成的东西，如果你做了1或2，你必须自己开发它。Spring和Hibernate以及其他类似的东西肯定比什么都没有好，只是比较一下它们提供的内容和数据库在从某些电子灾难中恢复时提供的内容。

#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:

正确的方法是使用数据库锁，因为它是为这项工作而设计的。如果你用的是hibernate用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.

在这里，我们从数据库中获取产品进行更新，以防止任何并发更新。

#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.

实体与DAO层分离是很有用的，因此它们只是简单的存储单元，您可以对它们进行填充、比较等等，而不包括作用于它们的方法。这些只是数据库中的类表示，理想情况下不应该被定义如何使用的代码污染。

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.

DAO层提供了基本的CRUD操作，这些操作允许持久化、检索、合并和删除这些实体，而不需要知道这些操作的上下文。这是一个地方，在这个地方，单例对象可以有效地防止多次创建实例——但是使用singleton并不意味着线程安全。我个人建议使用Spring来实现这一点(默认情况下，Spring bean是单例的)，但如果您愿意，可以手动完成。

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://*.com/questions/659915#659939 */
    private Object getCacheSyncObject(final Integer id) {
      locks.putIfAbsent(id, id);
      return locks.get(id);
    }       
}

#5

Let's evaluate three options.

让我们评估三个选项。

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

1。为产品创建一个单例类。这将确保在整个应用程序中只有一个产品实例可用。

Single instance for product is fine. But if you are offering a Product like Mobile with quantity 20, still you have to increment product count (static variable ) on addProductToCart and decrement product count on removeTheProductFromTheCart. Still you have to synchronize the access to this mutable count Or update database and read product count.

产品的单实例是可以的。但是，如果您提供的产品是Mobile，数量为20，仍然需要在addProductToCart上增加产品计数(静态变量)，在removeTheProductFromTheCart上减少产品计数。仍然需要同步对这个可变计数或更新数据库的访问并读取产品计数。

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

2。同步addProductToCart和removeTheProductFromTheCart方法。它只允许一个线程一次更新产品计数和更新db。

This is one solution but I prefer third one : Remove synchronization in application and provide data consistency at database layer.

这是一种解决方案，但我更喜欢第三种:删除应用程序中的同步，并在数据库层提供数据一致性。

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.

3所示。使用数据库并发控制应用一些db事务隔离级别，对productCount进行乐观/悲观锁定。我使用的是mysql，默认的隔离级别是REPEATABLE_READ。

Defer consistency to database instead of application. But you have to use READ COMMITTED for isolation level instead of REPEATABLE_READ

将一致性推迟到数据库而不是应用程序。但是您必须为隔离级别使用READ COMMITTED，而不是REPEATABLE_READ

Have a look at this article

看看这篇文章好吗

READ COMMITTED

读过承诺

A somewhat Oracle-like isolation level with respect to consistent (nonlocking) reads: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot.

对于一致的(非锁定的)读取，有点像oracle的隔离级别:每个一致的读取，甚至在相同的事务中，都设置和读取自己的新快照。

#6

As I understood carts are also persisted in db? And as final result bought products too.

正如我所理解的，购物车也被保存在db中?最终结果也购买了产品。

product:

产品:

[{id:1, count:100}]

cart:

购物车:

[{user_id:1, product_id:1}, {user_id:2, product_id:1}]

bought :

买了:

[{user_id:3, product_id:1}]

Then you can get count for the product

然后你可以计算产品的数量

select count as total from product where id = 1
select count(*) as in_cart from cart where product_id = 1
select count(*) as total_bought from bought where product_id = 1

now you can show final count by

现在你可以显示最终计数

int count = total - (in_cart + total_bought);

This way you guarantee that there will not overriden or bypassed increments/decrements. Finally beside code check for count you can also add a trigger in DB level which checks for the total count and if the product can be inserted in carts or bought tabel.

这样，您就可以保证不会超过或绕过递增/递减。最后，除了代码检查计数之外，您还可以在DB级别上添加一个触发器，检查总计数以及产品是否可以插入购物车或购买tabel。

If your product count is changing daily, I mean yesterday you had 100 product and sold 20, today 50 products has arrived, thus you should have 150 - 20 sold count which is 130. In order to have a good reports you can make product count daily. like

如果你的产品数量每天都在变化，我的意思是昨天你有100个产品，卖了20个，今天有50个产品到了，所以你应该有150 - 20个销售数量，也就是130个。为了有一个好的报告，你可以使产品计数每天。就像

 [
  {id:1, count:100, newly_arrived: 0, date: 23/02/2016}, 
  {id:1, count:80, newly_arrived: 50, date: 24/02/2016}
 ]

then your queries will change like

然后您的查询将发生类似的变化

select count+newly_arrived as total from product where id = 1 and date = today
select count(*) as in_cart from cart where product_id = 1 and date = today
select count(*) as total_bought from bought where product_id = 1 and date = today

for this you have to only insert new product data at midnight 00:00:00 and when your new products arrive in the morning you can update newly_arrived without intervening any insert or delete operations on carts or bought tables. and you can have detailed reports easily without making sophisticated report queries :))

为此，您必须在午夜00:00:00时插入新产品数据，当您的新产品在早上到达时，您可以更新您的新产品，而无需对购物车或购买的表进行插入或删除操作。您可以轻松地获得详细的报告，而无需进行复杂的报告查询:)

#7

If you have to use a relational database for this (and not a key value store e.g.). I would highly recommend to do this as close to the storage as possible to avoid locks and conflicts and get the highest performance as possible.

如果您必须为此使用关系数据库(而不是键值存储)。我强烈建议尽可能靠近存储，以避免锁和冲突，并尽可能获得最高的性能。

On the other hand it sounds like a typical scenario where multiple nodes are working on one database which also creates problems with latency due to Hibernate session handling.

另一方面，它听起来像一个典型的场景，其中多个节点在一个数据库上工作，这也会导致Hibernate会话处理导致的延迟问题。

At the end you need statements like

最后你需要像这样的陈述

UPDATE product SET productCount = productCount + 1 WHERE id = ?

To be executed in a transaction maybe using a simple JDBC statement or by a JPA repository method with @Query annotation.

要在事务中执行，可以使用简单的JDBC语句或使用带有@Query注释的JPA存储库方法。

To make Hibernate aware of the changes you can use Session.refresh() on the product after such an operation.

要使Hibernate意识到更改，您可以在这样的操作之后对产品使用Session.refresh()。

#8

Best way to do that work in ecommerce web app, using Messaging Queue . You can use RabbitMQ ,

在电子商务web应用程序中，使用消息队列完成这项工作的最佳方式。你可以使用RabbitMQ，

1. Create a queue on rabbitMQ and keep all requests here that reduce product count

   在rabbitMQ上创建一个队列，并将所有减少产品数量的请求保存在这里

2. because of queue all request will be processed one by one so there wont be any conflicts to reduce product.

   因为队列所有的请求将被一个一个处理，所以不会有任何冲突来减少产品。

#9

This isn't so much a technical question as a process question. Personally, I would not reduce inventory until someone had actually purchased the product. Adding an item to a shopping cart is an indicator that they may purchase it. Not that they have.

与其说这是一个技术问题，不如说是一个过程问题。就我个人而言，我不会减少库存，直到有人真的购买了产品。在购物车中添加物品是他们可能购买的指示物。不是,他们有。

Why not reduce the inventory count only when the payment has successfully processed? Wrap the entire thing in a database transaction. If 10 people add the same item to their cart and there is only 1 left, then the first to pay wins. 9 folks will be upset they didn't pay faster.

为什么不减少库存计数只有当付款已成功处理?在数据库事务中包装整个内容。如果10个人在他们的购物车中添加了相同的商品，并且只剩下1个，那么第一个支付的就会赢。人们会因为没有更快的支付而感到不安。

The airlines reference is different. They do issue temp holds, but it is way more complicated than managing inventory. That and they are working with a fluid pool of seats that they monkey with as the inventory becomes scarce.

航空公司的参考是不同的。他们确实会发行临时债券，但这比库存管理要复杂得多。他们正在用流动的座椅，当库存变得稀缺时，他们就会胡乱摆弄。