CODEWITHSUNDEEP
phpmysqltransactionscommitrollback
user1407642
user1407642

Reputation:

How to Process more than 100 mysql query with rollback feature?

Currently I am developing a system in which at a point I have to fire 100 inter dependent queries like,

In my script (Currently doing),

$query1 = mysql_query("INSERT_QUERY_STRING");
if(!$query) {
    die('error'.mysql_error());
}

//this loop can be turn more than 100 times

while(CONDITION) {
    //query generated here
    $query2 = mysql_query("UPDATE_QUERY_STRING");
    $query3 = mysql_query("UPDATE_QUERY_STRING");
    $query4 = mysql_query("UPDATE_QUERY_STRING");
    $query5 = mysql_query("UPDATE_QUERY_STRING");

    if(!$query2 || !$query3 || !$query4 || !$query5) {
        die('error '.mysql_error());
    }
}

$query6 = mysql_query("UPDATE_QUERY_STRING");
if(!$query6) {
    die('error '.mysql_error());
}

Ignore syntax error, it's just a logic

The problem is, since all the queries are depended on each other, if one fails then all the changes occurred before should be revoked. I know one solution of using MYSQL TRANSACTION, COMMIT AND ROLLBACK. Here's what I figured out (planning to do this),

$processes = array();

$processes[] = "INSERT_QUERY_STRING"; //query1

//this loop can be turn more than 100 times

while(CONDITION) {
    //query generated here
    $processes[] = "UPDATE_QUERY_STRING"; //looping queries
}

$processes[] = "UPDATE_QUERY_STRING" //last query

mysql_query("SET AUTOCOMMIT=0");
mysql_query("START TRANSACTION");    
$error = false;
for($i=0;$i < count($processes) ; $i++)
{
    $q = mysql_query($processes[$i]);
    if(!$q) {
        $error = true;
        break;
    }
}

if($error == false) {
    mysql_query("COMMIT");
}
else {
    mysql_query("ROLLBACK");
}

Is my solution good / best ? Is there any other possible solution do same stuff more quickly and effectively ? Thanks.

Upvotes: 1

Views: 1342

Answers (2)

SlawomirJ
SlawomirJ

Reputation: 106

I think that will be better use try/catch in tour code:

$processes = array();

$processes[] = "INSERT_QUERY_STRING"; //query1

//this loop can be turn more than 100 times

while(CONDITION) {
    //query generated here
    $processes[] = "UPDATE_QUERY_STRING"; //looping queries
}

$processes[] = "UPDATE_QUERY_STRING"; //last query

mysql_query("SET AUTOCOMMIT=0");
mysql_query("START TRANSACTION");  

$count_processes = count($processes);

for($i=0; $i < $count_processes; $i++)
{
    try {
        $q = mysql_query($processes[$i]);
        if (!$q) throw new Exception(mysql_error());
    }
    catch (Exception $e) {
        mysql_query("ROLLBACK");
        break;
    }
}

mysql_query("COMMIT");
mysql_query("SET AUTOCOMMIT=1");

Use try/catch is better because you have control over the code and error :)

Upvotes: 2

Jarek.D
Jarek.D

Reputation: 1294

Transactions are definitely the best way to go here. I'd put insert queries in try-catch statement to cover the unpredictable cases and use foreach for traversing $processes

Upvotes: 1

Related Questions