处理来自不同用户的并发 MySQL 查询

发布于 2025-01-03 20:27:34 字数 443 浏览 1 评论 0原文

我有一个 PHP/MySQL 注册系统，它限制了可以注册某项内容的人数。假设有 5 个空位，其中 4 个已满。现在，登录的 2 个不同用户同时提交了一个表单，该表单执行 MySQL 查询以将该人添加到列表中。

简化后，PHP 逻辑看起来像这样：

if($numberOfSpotsTaken < 5) {
    //add user to list
}
else {
    //don't add user to list
}

现在，因为用户同时提交请求，所以当代码检查有多少名额被占用时，两个用户的名额都将低于 5。最终发生的情况是，两个请求都通过了，当限制应该为 5 时，最终有 6 个人出现在列表中。

如何防止这种情况，以便两个（或更多）用户在同一时间提交请求同一时间无法突破人数限制？

原文

I have a PHP/MySQL sign-up system that limits the number of people who can sign up for something. So, let's say there are 5 spots available, and 4 of them are already full. Now, 2 different users who are logged in submit a form at the exact same time that executes a MySQL query to add that person to the list.

Simplified, the PHP logic looks something like:

if($numberOfSpotsTaken < 5) {
    //add user to list
}
else {
    //don't add user to list
}

Now, because the users both submit the request at the same time, when the code checks to see how many spots are taken, it will be under 5 for both. What ends up happening is that both requests go through and 6 people end up on the list when the limit is supposed to be 5.

How can I prevent this situation so that two (or more) users who submit a request at the same time can't bypass the limit of how many people can sign up?

分享到QQ

分享到微博

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

发布评论

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

伤痕我心 2025-01-10 20:27:34

我通过使用 PDO 的 beginTransaction()、rollBack() 和 commit() 方法来防止超额预订，从而解决了这个问题。

简单地说，这就是我所做的：

<?php

$db->beginTransaction();

//add to list
$sth = $db->prepare("INSERT INTO...");
$sth->execute();

//select count from list
$sth = $db->prepare("SELECT ... FROM ...");
$sth->execute();

//check to see whether the list is overbooked -- in this case, 5 is the list limit
if($sth->rowCount() > 5) {
    $db->rollBack();
}
else {
    $db->commit();
}

?>

I was able to solve this problem by using PDO's beginTransaction(), rollBack(), and commit() methods to prevent overbooking.

Simplified, here's what I did:

<?php

$db->beginTransaction();

//add to list
$sth = $db->prepare("INSERT INTO...");
$sth->execute();

//select count from list
$sth = $db->prepare("SELECT ... FROM ...");
$sth->execute();

//check to see whether the list is overbooked -- in this case, 5 is the list limit
if($sth->rowCount() > 5) {
    $db->rollBack();
}
else {
    $db->commit();
}

?>

回复收藏 0 原文