'PHP PDO transaction rollBack doesn't work

How is everything
Please can anyone help me in this
I'm trying to use PHP PDO transaction and there is a problem that I face and I can't deal with it.
rollBack function doesn't work when It catch an exception
Here is the connection code 

$host   = 'localhost';
$user   = 'root';
$pass   = '';
$error  = '';
$dbname = 'tameras_finance';
// Set DSN
$dsn = 'mysql:host=' . $host . '; dbname=' . $dbname;
// Set options
$options = array(
    PDO::ATTR_PERSISTENT => true,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
);
// Create a new PDO instanace
try {
    $dbh = new PDO($dsn, $user, $pass);
    $dbh->exec("set names utf8");
}
//Catch any errors
catch (PDOException $e) {
    echo $error = $e->getMessage();
}

and here the code 

try{
    $dbh->beginTransaction();
    $dbh->query('SET @newDebit = (SELECT max(`debtor_id`) + 1 AS maxDebit FROM `vocher`)');
    for($x = 0; $x < count($debits); $x++){
        $dbh->query('UPDATE `vocher` SET `debtor_id` = @newDebit, `status_id` = "4" WHERE `id` = '.$debits[$x]);
    }

    for($x = 0; $x < count($others); $x++){
        $columns = '`acc_id`, `value`, `date`, `desc`, `reject`, `vo_type_id`, `user`, `debtor_id`';
        $vals  = "'".$accs[$x]."','".$values[$x]."','".$dates[$x]."','".$descs[$x]."','1','1','".$user."', @newDebit";
        if($others[$x] == 'e'){
            $columns .= ', `cheque_no`, `available_date`, `issue_date`, `bank_id`';
            $vals  .= ", '".$sns[$x]."', '".$availdates[$x]."', '".$issueDates[$x]."', '".$banks[$x]."'";
        }
        $dbh->query("INSERT INTO creditor (".$columns.") VALUES (".$vals.")");
        if($lists[$x] != 'e'){
            $lastId = $dbh->lastInsertId();
            $q  = 'INSERT INTO `creditor_cc` (`creditor`, `cc`) VALUES ';
            for($y = 0; $y < count($lists[$x]); $y++){
                $dif = count($lists[$x]) - $y;
                $q .= '(';
                $q .= '"' . $lastId . '",';
                $q .= '"'.$lists[$x][$y].'"';
                $q .= ')';
                if($dif > 1){
                    $q .= ',';
                }
            }
            $dbh->query($q);
        }
    }
    $dbh->commit();
} catch(PDOException $e) {
    echo $error = $e->getMessage();
    $dbh->rollBack();
}

This code doesn't rollback
Please note that:

  • $sns, $others, $accs, $values, $dates, $descs, $availdates, $issueDates and $banks are arrays with the same size
  • Also $lists is a two dimension array with the same size

please help me with this why this code doesn't rollBack

phppdotransactionsrollback


Solution 1:[1]

Transactions are not supported on MySQL's default table type MyISAM. You need to make sure you're using InnoDB tables.

Also check to make sure the exception that's being thrown is PDOException or it'll fall through the try/catch and not hit your rollback.

Solution 2:[2]

I would say it shoud be done this way:

$pdo = new \PDO(/*...*/);

$pdo->beginTransaction();

try {
//...
} catch(\Exception $e) {
    $pdo->rollBack();
    throw $e;
}

$pdo->commit();

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Jochen Schultz

Related Questions