当前位置：文江博客话题详情

mysql innodb 事务并发

发布于 2024-11-27 07:17:28 字数 1392 浏览 3 评论 0原文

我有 3 个表

产品

product_id
max_products_can_sell
max_products_can_sell_to_individual

购买

user_id
产品 ID
数量

预订

product_id
用户 ID
数量

希望您理解其结构。

现在，当用户尝试购买产品时，我必须检查 max_products_can_sell 剩余的商品 - （已售数量 + 保留数量）。

如果有可用的物品，我必须将其存储在预订表中，直到他购买为止。（将通过该表的 cron 作业进行内务管理）

现在实际的问题是，如何处理这里的并发问题。例如：笔产品只有 1 个。两个用户请求reserve.php。

“a”用户请求 Reserve.php 且可用笔为 1 。但在插入之前，对于“b”用户可用的笔还为 1。所以两个用户预留笔。

我正在使用 innodb 表。怎么处理呢？

EIDT

  $query = SELECT count(*) as sold     FROM purchases WHERE product_id = 1;    
    $query = SELECT count(*) as reserved FROM reservations WHERE product_id = 1;     
    $items_remaining = $sold+$reserved;       

    if ($items_remaining) {      
       //INSERT data to reservations    
    }

现在，我需要确保没有其他查询会干扰并执行相同的 SELECT（在连接完成更新行之前读取“旧值”）。

如 Dan 告诉我，我可以锁定 TABLES 表以确保一次只有 1 个连接正在执行此操作，并在完成后将其解锁，但是这看起来像将其包装在事务中会做同样的事情吗（确保在另一个连接仍在处理时没有其他连接尝试相同的过程）或者 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE 会更好吗

？我很困惑是使用事务还是锁定表还是两者都使用。

EDIT2
在产品表中，还有一个名为 max_can_sell_to_individual 的字段。我必须检查当前库存，还必须检查个人限额。

我可以维持库存（可用库存），但我还必须检查个人限制。这可以从采购表中找到。

请建议我我该如何处理？

提前致谢！

原文

I have 3 tables

Products

product_id
max_products_can_sell
max_products_can_sell_to_individual

purchases

user_id
product_id
quantity

reservations

product_id
user_id
quantity

Hope you understood the structure.

Now, When ever user is trying to purchase a product, I have to check the items remaining by max_products_can_sell - (quantity sold + quantity reserved).

If items are available I have to store it in reservations table until he purchases. (Will do house keeping through cron job for this table)

Now Actual question is, How to handle concurrency issues here.
eg: pen product have 1 only. two users request reserve.php.

"a" user request reserve.php and available pen is 1 . but before insert, for "b" user available pen is 1 yet. so two users reserve pen.

I am using innodb table. How to handle it?

EIDT

  $query = SELECT count(*) as sold     FROM purchases WHERE product_id = 1;    
    $query = SELECT count(*) as reserved FROM reservations WHERE product_id = 1;     
    $items_remaining = $sold+$reserved;       

    if ($items_remaining) {      
       //INSERT data to reservations    
    }

Now, I need to ensure that no other queries will interfere and perform the same SELECT (reading the 'old value' before that connection finishes updating the row.

As Dan told, I can LOCK TABLES table to just make sure that only 1 connection is doing this at a time, and unlock it when I'm done, but that seems like overkill. Would wrapping that in a transaction do the same thing (ensuring no other connection attempts the same process while another is still processing)? Or would a SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE be better?

I'm confused whether to use transactions vs locking tables or use both. Please suggest me..

EDIT2
In products table, there is another field called max_can_sell_to_individual. I have to check the current inventory and also have to check the personal limit.

I can maintain inventory(stock available) but I have to check the individual limit also. That can be found from purchases table.

Please suggest me How can I handle it?

Thanks in advance!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

守不住的情 2024-12-04 07:17:28

我将仅锁定产品记录（使用选择更新 - 请注意，在共享模式下选择锁定不会阻止其他用户购买相同的产品），然后执行其余操作。这样我就不会阻止购买其他产品（同时锁定表将阻止任何写操作，无论产品1还是产品2）
为什么要保留 max_product_can_sell 属性而不是（或不连同） available_quantity 属性？