PHP，MySQL，PDO事务-fetchAll()可以在commit()之前吗? [英] PHP, MySQL, PDO Transactions - Can fetchAll() come before commit()?
问题描述
更多交易问题！
我现在所遇到的是一堆串在一起的查询，如果有任何失败，都将手动将其撤消:
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()?
推荐答案
您的第一个问题:
具体来说，我可以包含$ result = $ stmt3-> fetchAll();吗?在commit()之前，然后执行条件查询?
Specifically, can I include the $result = $stmt3->fetchAll(); before the commit(), and then execute the conditional query?
我看不出为什么它不起作用.事务的行为与没有事务的操作基本相同-除了更改只是草稿外.您在前面的语句中所做的任何更改都将应用于仅对该单个会话有效的工作副本".对您来说，它将显得完全透明.但是，如果您不提交任何更改，则会将其回滚.
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.
此外，我对此不太确定，但是我需要$ db-> rollBack();吗?在try块中，如果在commit()之前退出了代码(返回false)?
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()?
从文档中说:
当脚本结束或即将关闭连接时，如果您有未完成的事务，PDO将自动将其回滚.
When the script ends or when a connection is about to be closed, if you have an outstanding transaction, PDO will automatically roll it back.
因此，您不一定需要手动回滚，这将由驱动程序本身完成.
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:
警告PDO仅在驱动程序级别检查事务功能.如果某些运行时条件意味着事务不可用，那么如果数据库服务器接受启动事务的请求，PDO :: beginTransaction()仍将返回TRUE而不会出现错误.
Warning PDO only checks for transaction capabilities on driver level. If certain runtime conditions mean that transactions are unavailable, PDO::beginTransaction() will still return TRUE without error if the database server accepts the request to start a transaction.
因此请确保事先检查兼容性！
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.
文档中的另一注:
在事务中发出诸如DROP TABLE或CREATE TABLE之类的数据库定义语言(DDL)语句时，包括MySQL在内的某些数据库会自动发出隐式COMMIT.隐式COMMIT将阻止您回滚事务边界内的任何其他更改.
Some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction. The implicit COMMIT will prevent you from rolling back any other changes within the transaction boundary.
这篇关于PHP，MySQL，PDO事务-fetchAll()可以在commit()之前吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
