如何在PHP中实现MySQL的提交/回滚？

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循环的开头和结尾处使用提交/回滚来执行此操作,但不知道如何操作。

4 个解决方案

#1

Take a look at this tutorial on transactions with PDO.

看一下关于PDO交易的本教程。

Basically wrap the long running code in:

基本上将长时间运行的代码包装在:

$dbh->beginTransaction();
...
$dbh->commit();

And according to this PDO document page:

并根据此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. "

“当脚本结束或连接即将关闭时,如果您有未完成的事务,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.

但实际上,你应该重新设计它,以便它不依赖于留下来的抄写员。

#2

You need to use InnoDB based tables for transactions then use any library like PDO or MySQLi that supports them.

您需要使用基于InnoDB的表进行事务处理,然后使用任何支持它们的PDO或MySQLi库。

#3

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();
}

#4

-1

<?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>";
    }
}
?>

#1