单个字段上可靠减/增量所需的隔离级别
假设我们有一个如下表，
+----+---------+--------+
| id | Name | Bunnies|
+----+---------+--------+
| 1 | England | 1000 |
| 2 | Russia | 1000 |
+----+---------+--------+
并且我们有多个用户在指定的时间段（例如 2 小时）内删除兔子。 （所以最少 0 个兔子，最多 1000 个兔子，兔子被返回，而不是由用户添加）
我正在使用两个基本事务查询，例如
BEGIN;
UPDATE `BunnyTracker` SET `Bunnies`=`Bunnies`+1 where `id`=1;
COMMIT;
“当有人返回兔子时”和
BEGIN;
UPDATE `BunnyTracker` SET `Bunnies`=`Bunnies`-1 where `id`=1 AND `Bunnies` > 0;
COMMIT;
“当有人尝试拿走兔子时”。我假设这些查询将在幕后实现某种原子性
用户不能接受比每个国家/地区更多的兔子（即，如果 23 个用户同时进行交易，则为 -23 个兔子）
我的问题是，在这种情况下如何保持 ACID 安全，同时能够同时添加/增加/减少兔子字段，同时保持在范围内（0-1000） 我可以将隔离级别设置为序列化，但我担心这会降低性能。
有什么建议吗？ 提前致谢
Imagine we have a table as follows,
+----+---------+--------+
| id | Name | Bunnies|
+----+---------+--------+
| 1 | England | 1000 |
| 2 | Russia | 1000 |
+----+---------+--------+
And we have multiple users removing bunnies, for a specified period, such as 2 hours. (So minimum 0 bunnies, max 1000 bunnies, bunnies are returned, not added by users)
I'm using two basic transaction queries like
BEGIN;
UPDATE `BunnyTracker` SET `Bunnies`=`Bunnies`+1 where `id`=1;
COMMIT;
When someone returns a bunny and,
BEGIN;
UPDATE `BunnyTracker` SET `Bunnies`=`Bunnies`-1 where `id`=1 AND `Bunnies` > 0;
COMMIT;
When someone attempts to take a bunny. I'm assuming those queries will implement some sort of atomicity under the hood
It's imperative that users cannot take more bunnies than each country has, (ie. -23 bunnies if 23 users transact concurrently)
My issue is, how do I maintain ACID safety in this case, while being able to concurrently add/increment/decrement the bunnies field, while staying within the bounds (0-1000)
I could set the isolation level to serialized, but I'm worried that would kill performance.
Any tips?
Thanks in advance
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我相信您需要实现一些额外的逻辑，以防止并发增量事务和减量事务读取相同的初始值。
按照目前的情况，如果 Bunnies = 1，则可以同时进行增量和减量事务，它们都读取初始值 1。如果增量首先完成，则其结果将被忽略，因为减量已经读取了初始值 1并将值递减至 0。无论其中哪个操作最后完成，都会有效地取消另一个操作。
要解决此问题，您需要使用 SELECT ... FOR UPDATE 来实现锁定读取，如下所示
此处进行了描述。例如：
I believe you need to implement some additional logic to prevent concurrent
incrementanddecrementtransactions from both reading the same initial value.As it stands, if Bunnies = 1, you could have simultaneous increment and decrement transactions that both read the initial value of 1. If the increment then completes first, its results will be ignored, since the decrement has already read the initial value of 1 and will decrement the value to 0. Whichever of these operations completes last would effectively cancel the other operation.
To resolve this issue, you need to implement a locking read using
SELECT ... FOR UPDATE, asdescribed here. For example:
尽管在用户看来多个事务在数据库中同时发生，但它们实际上是连续的。 （例如，条目一次写入重做/事务日志中）。
因此，对表“bunnys >= 0”施加约束并捕获尝试违反该约束的事务的失败是否有效？
Although it looks to the users like multiple transactions occur simultaneously within the DB they are actually sequential. (E.g. entries get written to the redo/transaction logs one at a time).
Would it therefore work for you to put a constraint on the table "bunnies >= 0" and catch the failure of a transaction which attempts to breach that constraint?