MySQL：如何在多个只读架构中确保完整性
场景很容易描述，但可能有一个复杂的答案：
想象一下这样一种情况，您有一个只写 mysql 数据库。那么你就有大约 5 或 6 个只读数据库。写入数据库具有特定库存的计数。您有数十万用户在购买这个特定的库存商品，但数量有限。为了便于论证，请说 10 项。
确保只售出 10 件商品的最佳方法是什么？如果只读从属设备更新的时间之间甚至存在 200 毫秒的增量，那么计数的完整性是否会过时，从而出售您没有的库存？
您将如何解决/扩展这个问题？
Scenario is simple to describe, but might have a complex answer:
Imagine a case where you have one write only mysql database. Then you have about 5 or 6 read only databases. The write database has a count for a particular inventory. You have hundreds of thousands of users banging away at this particular inventory item, but only limited quantity. For argument's sake, say 10 items.
What's the best way to ensure that only 10 items get sold? If there is even a 200ms delta between the time the read-only slaves get updated, can't the integrity of the count go stale, thus selling inventory you do not have?
How would you solve/scale this problem?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
并发用户的基本解决方案可能也会涵盖这一点。在“购买”交易的某个时刻，您需要减少库存（在写入服务器上）。通过任何方法，强制库存不能低于零。
如果只剩下一件商品，而两个人都想购买它，那么其中一个人就会倒霉。
复制延迟是完全相同的事情。两个用户看到一个产品可用，但当他们尝试购买时，该产品已经消失了。对于这种情况，一个好的解决方案既涵盖了复制延迟，也涵盖了用户从另一个用户手中夺取最后一项的情况。
The basic solution to concurrent users will probably cover this too. At some point in the "buy" transaction, you need to decrement the inventory (on the write-server). Through whatever method, enforce that inventory can't go below zero.
If there's one item left, and two people trying to buy it, one will be out of luck.
The replication latency is exactly the same thing. Two users see a product available, but by the time they try to buy it, it's gone. A good solution for that scenario covers both replication latency and a user simply snatching the last item out from under another user.
这完全取决于您决定锁定主表以进行更新的时间和窗口。
答：如果您必须 100% 确定某件商品只有在确实有货时才会尝试购买。一旦您向特定用户列出该商品，您就必须锁定该商品（这意味着您将暂时减少库存）
B. 如果您同意展示该商品，“抱歉，我们刚刚缺货 ” “ 信息。你应该在结账之前锁定该商品（好吧，你可以在交易完成后这样做。但代价是客户非常愤怒）
我会选择方法 A 进行锁定，并且可能会标记“很快售完”警告适用于库存非常少的商品。 （如果情况非常频繁，您还可以计算同时点击该商品的用户数量，并给出更准确的警告）
从业务角度来看，您不希望库存如此之低（低于数量）的同时购买者）这当然是不可避免的，在“圣诞节”时期，缺货也没关系:)
It all depends on when and what window you decide to lock the master table for the update.
A. If you have to be 100% sure an item will be attempted to be bought only when its surely available. You will have to lock the item for the particular user as soon as you list it to him (which means you will temporarily decrement the inventory stock)
B. If you are okay with showing the one off "sorry, we just ran out of stock" message. you should lock the item just before you bill (well, you could do that after transaction is complete. but at the cost of a very furious customer)
I would chose approach A for locking, and may be flag a "selling out soon" warning for items with very low stock left. (if its a very frequent situation, you could proly also count the number of concurrent users hitting on the item and give a more accurate warning)
From the business perspective, you wouldn't want to be so low on stock (lower than the number of concurrent buyers) This is inevitable of course at "christmas" times when its okay to be out of stock :)