防止同时进行数据库表行访问 [英] Preventing simultaneous db table row access
问题描述
有时，我们支持团队中的两个管理员试图对数据库表行执行相同的敏感操作(例如，修改行中的值).我们需要防止这种情况. (因为表是"myisam"，所以行锁定是不可能的)
It sometimes happens that two admins in our support team are trying to do the same sensitive operation on db table row (let's say, modifying the value in the row). We need to prevent that. (Row locking is not possible because tables are "myisam")
我想到了几种解决方案:
I have thought of several solutions:
<input name="money"><input type="hidden" name="old_money" value="10">
然后在更新之前:
$currentmoney=value_from_query("select money from mytable","money");
if($currentmoney!=$_REQUEST["old_money"]){
return "value changed to $currentmoney while you were editing it, are you sure you still want to change it?!??!?!?!?";
}
else{
mysql_query("update everyonesmoney set money='".intval($_REQUEST["money"])."' where user='$user_id'");
return true;
}
但可能存在以下情况:
-
用户需要将钱值从9 $更改为10 $
user needs money value to be changed from 9$ to 10$
admin1将他的钱更改为10 $
admin1 changes his money to 10$
用户明智地花费1美元，因此他目前的钱又变成了9美元！
user smartly spends 1$, so his current money becomes 9$ again!
admin2在没有警告的情况下将他的钱改为10美元.
admin2 changes his money to 10$ with no warning.
在行中创建时间戳记(updated_at列)设置
并与解决方案1相同.这样做的好处是，它比简单的数据比较更具说服力.我们可以肯定地说，在摆弄表格时是否更改了数据.缺点-除非我们将其与解决方案1组合在一起，否则我们无法跟踪确切更改了哪一列
creating timestamp (updated_at column) setting in the row
And doing same as in solution 1. This has advantage that it's saying more than simple data comparison. We can say for sure if data was changed while we were fiddling with the form or no. disadvantage - we cannot track which column exactly was changed, unless we combine it with solution 1
<input type="hidden" name="formtimestamp" value="<? echo time();?>">
然后在更新时:
$query_add = ($overriden ? "" : " and updated_at>'".securevalue($_REQUEST["formtimestamp"])."'");
if(mysql_affected_rows(mysql_query("update everyonesmoney set money='".intval($_REQUEST["money"])."', updated_at=NOW() where user='$user_id' ".$query_add))==0){
return "some values were changed by someone else while you were editing it, are you sure you still want to change it?!??!?!?!?";
}
else{
return true;
}
使用对象/动作专用名称创建长度为0的临时文件
在更新期间创建/锁定它，并检查其 更新之前存在/日期戳.
creating the temporary 0-length file with object/action-specific name
Creating/locking it during the update, and checking for its existence/datestamp before update.
更新前:
$myfname="/tmp/user{$user_id}EDITMONEY.tmp";
$timedifference=((time()-filectime($myfname)); //in seconds
if(file_exists($myfname) and ($timedifference<60) and (!$overriden)){ // a minute difference
$currentmoney=value_from_query("select money from mytable","money");
return "money were edited by someone else $timedifference seconds ago and set to {$currentmoney}, are you sure you still want to change it?!??!?!?!?";
}else{
$fp = fopen("/tmp/user".intval($_REQUEST["user_id"])."EDITMONEY.tmp", "r+");
if (flock($fp, LOCK_EX)) { // do an exclusive lock
mysql_query("update everyonesmoney set money='".intval($_REQUEST["money"])."' where user='$user_id'")
flock($fp, LOCK_UN); // release the lock
return true;
} else {
return "Couldn't get the lock, it's possible that someone tried to execute query simultaneously!";
}
fclose($fp);
}
就目前而言，文件创建是我的首选方法，因为:
For now file creation is my preferred approach because:
-
我认为创建本地文件比访问数据库更快.
I think it's faster to create local file than access database.
我不需要在表中再添加一列(时间戳)
I don't need to add one more column(timestamp) to the table
我可以轻松地修改文件名以检查特定的列修改，即在完成mysqlupdate后创建文件"money_user {$ userid} _modified".
I can easily modify the filename to check for specific column modification, ie create file "money_user{$userid}_modified" when mysqlupdate is done.
是对的还是我误会了什么?
Is that right or is there something I misunderstand?
推荐答案
您可以在UPDATE操作的WHERE子句中指定旧值，然后查看受影响的行数:
You can specify the old value in the UPDATE operation's WHERE clause, and then look at the number of rows affected:
给予
id name amount
--- ------------- ---------
1 Joe User 10
线程1执行
UPDATE accounts SET amount=9 WHERE id=1 AND amount=10;
=> Query Okay, 1 row(s) affected
线程2执行
UPDATE accounts SET amount=9 WHERE id=1 AND amount=10;
=> Query Okay, 0 row(s) affected
除此之外，为了减少浪费的时间，我可能会更早地实现排除，方法是先将任务分配给各个管理员.
Other than that, I'd probably implement the exclusion a bit earlier, by assigning tasks to individual admins first, in order to reduce the amount of time wasted.
这篇关于防止同时进行数据库表行访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
