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.

Answer 2

Use MySQLi.

Answer 3

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

Answer 4

Yes, transaction are a good idea here and not hard to implement with PHP5.

You can either keep to mysql or use mysqli which offers more flexibility with transactions.

In either way you would do all your inserts from the loop but only commit the transaction if your loop has finished.

An example of mysqli code:

$mysqli->autocommit(FALSE);
$mysqli->query("INSERT INTO book (isbn, title, author_id, publisher_id)
VALUES (’0395974682’, ’The Lord of the Rings’, 1, 3)");
$mysqli->query("INSERT INTO book (title) VALUES (’Animal Farm’, 3, 2)");
if (!$mysqli->commit()) {
$mysqli->rollback();
}

Your your case you would not try to commit but you would only commit, if your loop is finished and otherwise you would rollback.

EDIT: Same with PDO

/* Begin a transaction, turning off autocommit */
$dbh->beginTransaction();

/* Do your looping queries */
loop {
    queries...
}

/* Commit or rollback the changes */
if (your condition) {
$dbh->commit();
}
else
{
$dbh->rollback();
}

/* Database connection is now back in autocommit mode */

Answer 5

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

?>