如何在 PHP 中为 MySQL 实现提交/回滚？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/329622/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I implement commit/rollback for MySQL in PHP?
提问by Andrew G. Johnson
Well basically I have this script that takes a long time to execute and occasionally times out and leaves semi-complete data floating around my database. (Yes I know in a perfect world I would fix THAT instead of implementing commits and rollbacks but I am forced to not do that)
嗯，基本上我有这个脚本需要很长时间来执行，偶尔会超时，并在我的数据库中留下半完整的数据。（是的，我知道在一个完美的世界中，我会解决这个问题，而不是实现提交和回滚，但我被迫不这样做）
Here is my basic code (dumbed down for simplicity):
这是我的基本代码（为简单起见，已简化）：
$database = new PDO("mysql:host=host;dbname=mysql_db","username","password");
while (notDone())
{
$add_row = $database->prepare("INSERT INTO table (columns) VALUES (?)");
$add_row->execute(array('values'));
//PROCESSING STUFF THAT TAKES A LONG TIME GOES HERE
}
$database = null;
So my problem is that if that if the entire process within that while loop isn't complete then I don't want the row inserted to remain there. I think that somehow I could use commits/rollbacks at the beginning and end of the while loop to do this but don't know how.
所以我的问题是，如果那个 while 循环中的整个过程没有完成，那么我不希望插入的行保留在那里。我认为以某种方式我可以在 while 循环的开始和结束时使用提交/回滚来做到这一点，但不知道如何。
回答by Brian C. Lane
Take a look at this tutorialon transactions with PDO.
Basically wrap the long running code in:
基本上将长时间运行的代码包装在：
$dbh->beginTransaction();
...
$dbh->commit();
And according to this PDO document page:
"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. "
“当脚本结束或连接即将关闭时，如果您有未完成的事务，PDO 会自动回滚它。”
So you will lose the transaction that was pending when the script timed out.
因此，当脚本超时时，您将丢失挂起的事务。
But really, you ought to redesign this so that it doesn't depend on the scriipt staying alive.
但实际上，您应该重新设计它，使其不依赖于脚本的存活。
回答by Neelesh
try
{
$mysqli->autocommit(FALSE);
$mysqli->query("insert into tblbook (id,cid,book) values('','3','book3.1')");
echo $q_ins=$mysqli->affected_rows."<br>";
$mysqli->query("update tblbook set book='book3' where cid='3'");
echo $q_upd=$mysqli->affected_rows."<br>";
$mysqli->commit();
}
catch(PDOException $e)
{
$mysqli->rollback();
echo $sql . '<br />' . $e->getMessage();
}
回答by Coder2000
You need to use InnoDB based tables for transactions then use any library like PDO or MySQLi that supports them.
您需要使用基于 InnoDB 的表进行事务，然后使用支持它们的任何库，如 PDO 或 MySQLi。
回答by Coder2000
<?php
//This may help someone....This code commit the transactions
//only if both queries insert and update successfully runs
$mysqli=new mysqli("localhost","user_name","password","db_name");
if(mysqli_connect_errno())
{
echo "Connection failed: ".mysqli_connect_error();
}
else
{
$mysqli->autocommit(FALSE);
$mysqli->query("insert into tblbook (id,cid,book) values('','3','book3.1')");
echo $q_ins=$mysqli->affected_rows."<br>";
$mysqli->query("update tblbook set book='book3' where cid='3'");
echo $q_upd=$mysqli->affected_rows."<br>";
if($q_ins==1 && $q_upd==1)
{
$mysqli->commit();
echo "Commit<br>";
}
else
{
$mysqli->rollback();
echo "Rollback<br>";
}
}
?>
