PHP PDO事务复制 [英] PHP PDO transaction Duplicating
问题描述
我有一个游戏网站，我想更新用户的资金，但是，如果我在同一时间使用2个pc，此代码将执行两次，并且用户将得到零钱.我该如何阻止这种情况的发生?这让我发疯.
I have a game website and I want to update the users money, however if I use 2 pc's at the exact same time this code will execute twice and the user will be left with minus money. How can I stop this from happening? It's driving me crazy.
$db = getDB();
$sql = "UPDATE users SET money = money- :money WHERE username=:user";
$stmt = $db->prepare($sql);
$stmt->bindParam(':money', $amount, PDO::PARAM_STR);
$stmt->bindParam(':user', $user, PDO::PARAM_STR);
$stmt->execute();
感谢您的帮助.
推荐答案
从@GarryWelding中回显注释:数据库更新不是处理所描述用例的代码中的适当位置.锁定用户表中的行不是正确的解决方法.
Echoing the comment from @GarryWelding: the database update isn't an appropriate place in the code to handle the use case that is described. Locking a row in the user table isn't the right fix.
备份一个步骤.听起来我们想要对用户的购买进行细粒度的控制.似乎我们需要一个地方来存储用户购买记录，然后我们可以进行检查.
Back up a step. It sounds like we are wanting some fine grained control over user purchases. Seems like we need a place to store a record of user purchases, and then we can can check that.
在不深入数据库设计的情况下，我将在这里提出一些想法...
Without diving into a database design, I'm going to throw out some ideas here...
除了用户"实体
user
username
account_balance
似乎我们对用户购买的一些信息感兴趣.我抛出了一些关于我们可能感兴趣的信息/属性的想法，而不是宣称您的用例都需要这些信息/属性:
Seems like we are interested in some information about purchases a user has made. I'm throwing out some ideas about the information/attributes that might be of interest to us, not making any claim that these are all needed for your use case:
user_purchase
username that made the purchase
items/services purchased
datetime the purchase was originated
money_amount of the purchase
computer/session the purchase was made from
status (completed, rejected, ...)
reason (e.g. purchase is rejected, "insufficient funds", "duplicate item"
我们不想尝试在用户的帐户余额"中跟踪所有这些信息，尤其是因为可能会有来自用户的多次购买.
We don't want to try to track all of that information in the "account balance" of a user, especially since there can be multiple purchases from a user.
如果我们的用例比这简单得多，并且仅跟踪用户最近的购买，则可以将其记录在用户实体中.
If our use case is much simpler than that, and we only to keep track of the most recent purchase by a user, then we could record that in the user entity.
user
username
account_balance ("money")
most_recent_purchase
_datetime
_item_service
_amount ("money")
_from_computer/session
然后在每次购买时，我们可以记录新的account_balance，并覆盖以前的最近购买"信息
And then with each purchase, we could record the new account_balance, and overwrite the previous "most recent purchase" information
如果我们关心的只是同时"阻止多次购买，那么我们需要定义……这是否意味着在同一精确的微秒内? 10毫秒之内?
If all we care about is preventing multiple purchases "at the same time", we need to define that... does that mean within the same exact microsecond? within 10 milliseconds?
我们是否只想防止从不同的计算机/会话重复"购买?在同一会话中有两个重复的请求怎么办?
Do we only want to prevent "duplicate" purchases from different computers/sessions? What about two duplicate requests on the same session?
这不是我解决问题的方式.但是要回答您所提出的问题，如果我们使用一个简单的用例-防止彼此之间在一毫秒内进行两次购买"，而我们想在user表
This is not how I would solve the problem. But to answer the question you asked, if we go with a simple use case - "prevent two purchases within a millisecond of each other", and we want to do this in an UPDATE of user table
给出这样的表定义:
user
username datatype NOT NULL PRIMARY KEY
account_balance datatype NOT NULL
most_recent_purchase_dt DATETIME(6) NOT NULL COMMENT 'most recent purchase dt)
在用户表中记录最近购买的日期时间(低至微秒)(使用数据库返回的时间)
with the datetime (down to the microsecond) of the most recent purchase recorded in the user table (using the time returned by the database)
UPDATE user u
SET u.most_recent_purchase_dt = NOW(6)
, u.account_balance = u.account_balance - :money1
WHERE u.username = :user
AND u.account_balance >= :money2
AND NOT ( u.most_recent_purchase_dt >= NOW(6) + INTERVAL -1000 MICROSECOND
AND u.most_recent_purchase_dt < NOW(6) + INTERVAL +1001 MICROSECOND
)
然后我们可以检测该语句影响的行数.
We can then detect the number of rows affected by the statement.
如果受影响的行数为零，则找不到:user，或者:money2大于帐户余额，或者most_recent_purchase_dt在现在的+/- 1毫秒范围内.我们不知道是哪个.
If we get zero rows affected, then either :user wasn't found, or :money2 was greater than the account balance, or most_recent_purchase_dt was within a range of +/- 1 millisecond of now. We can't tell which.
如果受影响的行超过零，那么我们知道发生了更新.
If more than zero rows are affected, then we know that an update occurred.
编辑
为了强调一些可能被忽略的关键点...
To emphasize some key points which might have been overlooked...
示例SQL期望支持小数秒，这需要MySQL 5.7或更高版本.在5.6及更早版本中，DATETIME分辨率仅下降到第二个. (请注意示例表中的列定义，SQL指定的分辨率低至微秒... DATETIME(6)和NOW(6).
The example SQL is expecting support for fractional seconds, which requires MySQL 5.7 or later. In 5.6 and earlier, DATETIME resolution was only down to the second. (Note column definition in the example table and SQL specifies resolution down to microsecond... DATETIME(6) and NOW(6).
示例SQL语句期望username是user表中的PRIMARY KEY或UNIQUE键.在示例表定义中对此进行了注明(但未突出显示).
The example SQL statement is expecting username to be the PRIMARY KEY or a UNIQUE key in the user table. This is noted (but not highlighted) in the example table definition.
示例SQL语句为彼此之间在一毫秒之内执行的两个语句覆盖了user的更新.为了进行测试，请将毫秒分辨率更改为更长的时间间隔.例如，将其更改为一分钟.
The example SQL statement overrides update of user for two statements executed within one millisecond of each other. For testing, change that millisecond resolution to a longer interval. for example, change it to one minute.
也就是说，将两次出现的1000 MICROSECOND更改为60 SECOND.
That is, change the two occurrences of 1000 MICROSECOND to 60 SECOND.
其他一些注意事项:使用bindValue代替bindParam(因为我们正在为语句提供值，而不是从语句中返回值.
A few other notes: use bindValue in place of bindParam (since we're providing values to the statement, not returning values from the statement.
还要确保将PDO设置为在发生错误时抛出异常(如果我们不打算检查代码中PDO函数的返回值)，因此代码不会将其(象征性的)小手指插入Dr.Evil风格在我们的嘴角我只是认为一切都会按计划进行.什么?"
Also make sure PDO is set to throw an exception when an error occurs (if we aren't going to check the return from the PDO functions in the code) so the code isn't putting it's (figurative) pinky finger to the corner of our mouth Dr.Evil style "I just assume it will all go to plan. What?")
# enable PDO exceptions
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "
UPDATE user u
SET u.most_recent_purchase_dt = NOW(6)
, u.account_balance = u.account_balance - :money1
WHERE u.username = :user
AND u.account_balance >= :money2
AND NOT ( u.most_recent_purchase_dt >= NOW(6) + INTERVAL -60 SECOND
AND u.most_recent_purchase_dt < NOW(6) + INTERVAL +60 SECOND
)";
$sth = $dbh->prepare($sql)
$sth->bindValue(':money1', $amount, PDO::PARAM_STR);
$sth->bindValue(':money2', $amount, PDO::PARAM_STR);
$sth->bindValue(':user', $user, PDO::PARAM_STR);
$sth->execute();
# check if row was updated, and take appropriate action
$nrows = $sth->rowCount();
if( $nrows > 0 ) {
// row was updated, purchase successful
} else {
// row was not updated, purchase unsuccessful
}
为了强调我之前提出的观点，锁定行"不是解决问题的正确方法.并按照我在示例中演示的方式进行检查，并没有告诉我们购买不成功的原因(资金不足或在先前购买的指定时间内).
And to emphasize a point I made earlier, "lock the row" is not the right approach to solving the problem. And doing the check the way I demonstrated in the example, doesn't tell us the reason the purchase was unsuccessful (insufficient funds or within specified timeframe of preceding purchase.)
这篇关于PHP PDO事务复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
