How can I implement commit/rollback for MySQL in PHP?

Question

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.

Answer 1

Take a look at this tutorial on 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. "

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.