问题描述
更多交易问题!
我现在所遇到的是一堆串在一起的查询,如果有任何失败,都将手动将其撤消:
What I have right now is a mess of strung-together queries, that are all manually reversed if any fail:
代码块1
$stmt1 = $db->prepare(...); // Update table1, set col=col+1
if($stmt1 = $db->execute(...)){
$stmt2 = $db->prepare(...); // Insert into table2, id=12345
if($stmt2 = $db->execute(...)){
$stmt3 = $db->prepare(...); // Select val from table3
if($stmt3 = $db->execute(...)){
$result = $stmt3->fetchAll();
if($result[0]['val'] == something){
$stmt4 = $db->prepare(...); // Update table4, set status=2
if($stmt4 = $db->execute(...)){
return true;
}else{
$stmt1 = $db->prepare(...); // Update table1, set col=col-1 (opposite of above)
$stmt1 = $db->execute(...);
$stmt2 = $db->prepare(...); // Delete from table2, where id=12345 (opposite of above)
$stmt2 = $db->execute(...);
return false;
}
}
return true;
}else{
$stmt1 = $db->prepare(...); // Update table1, set col=col-1 (opposite of above)
$stmt1 = $db->execute(...);
$stmt2 = $db->prepare(...); // Delete from table2, where id=12345 (opposite of above)
$stmt2 = $db->execute(...);
return false;
}
}else{
$stmt1 = $db->prepare(...); // Update table1, set col=col-1 (opposite of above)
$stmt1 = $db->execute(...);
return false;
}
}
这是一团糟,难以调试,难以添加,难以理解,当查询很大时,并且如果在连接过程中丢失连接,也不会将所有表返回到原始状态.
It's a mess, difficult to debug, difficult to add on to, difficult to understand when the queries are large, and won't return all tables back to original state if the connection is lost mid-way through.
删除行时,此过程甚至更糟,因为其中的所有内容都需要存储-以防万一需要撤消该操作.
This same process is even worse when deleting a row, because everything in it needs to be stored - just in case the operation needs to be undone.
现在,当我将其移植到单个事务中时,我知道其中的大多数仍然可以使用,但是我不确定的部分是:
Now, I know most of this will still work when I port it over to a single transaction, but the one part I am unsure of is:
代码块2
$result = $stmt3->fetchAll();
if($result[0]['val'] == something){
... continue ...
}else{
... reverse operations ...
return false;
}
因为结果收集将在事务中的commit()
之前进行.如下:
Because the results-gathering would take place before the commit()
in the transaction. As follows:
代码块3
$db->beginTransaction();
try{
$stmt1 = $db->prepare(...);
$stmt1->execute();
$stmt2 = $db->prepare(...);
$stmt2->execute();
$stmt3 = $db->prepare(...);
$stmt3->execute();
$result = $stmt3->fetchAll();
if($result[0]['val'] == something){
$stmt4 = $db->prepare(...);
$stmt4->execute();
}else{
$db->rollBack();
return false;
}
$db->commit();
return true;
}catch(Exception $e){
$db->rollBack();
throw $e;
return false;
}
这项工作有效吗?具体来说,我可以在commit()
之前加入$result = $stmt3->fetchAll();
,然后执行条件查询吗?
Will this work? Specifically, can I include the $result = $stmt3->fetchAll();
before the commit()
, and then execute the conditional query?
此外,我不确定,但是如果在commit()
之前退出了代码(return false
),我是否需要在try
块内使用$db->rollBack();
?
Also, I'm not entirely sure on this, but do I require the $db->rollBack();
within the try
block, if the code is exited (return false
) before the commit()
?
推荐答案
您的第一个问题:
我看不出为什么它不起作用.事务的行为与没有事务的操作基本相同-除了更改只是草稿外.您在前面的语句中所做的任何更改都将应用于仅对该单个会话有效的工作副本".对您来说,它将显得完全透明.但是,如果您不提交任何更改,则会将其回滚.
I see no reason why it should not work. A transaction behaves basically the same as operations without transactions - except that changes are only drafts. Any changes you make in the previous statements will be applied to a "working copy" valid for this single session only. For you it will appear completely transparent. However any changes will be rolled back if you do not commit them.
也应注意(强调我的意思):
Also worth noting (emphasis mine):
这可能会导致赛车状况.
This can cause racing conditions.
从文档中说:
因此,您不一定需要手动回滚,这将由驱动程序本身完成.
Therefore you do not necessarily require to roll back manually as it will be done by the driver itself.
不过,请注意同一来源的以下内容:
However note the following from the same source as well:
因此请确保事先检查兼容性!
So be sure to check the compatibility beforehand!
请勿在另一笔交易中开始交易.这将隐式提交第一个事务.参见此评论.
Do NOT begin a transaction in another transaction. This will commit the first transaction implicitely. See this comment.
文档中的另一注:
这篇关于PHP,MySQL,PDO事务-fetchAll()可以在commit()之前吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!