CODEWITHSUNDEEP
phpmysqlpdotransactionsrollback
Rubentje
Rubentje

Reputation: 128

MySQL transaction, rollback doesn't work, (PHP PDO)

I have a loop that inserts data in two tables, in the first iteration of the loop the insert is succesfull, but in the second iteration the insert will fail.

I have written the script so that if any of the iterations fail, the entire transaction should be rollbacked. However, this doesn't work.

The first iteration (that succeeded) isn't rolled back...

<?php
    include('model/dbcon.model.php');

    $languages = array('project_nl', 'project_en');

    DBCon::getCon()->beginTransaction();

    $rollback = true;

    foreach($languages as $language) {
        $Q = DBCon::getCon()->prepare('INSERT INTO `'.$language.'`(`id`, `name`, `description`, `big_image`) VALUES (:id,:name,:description,:big_image)');
        $Q->bindValue(':id', '1', PDO::PARAM_INT);
        $Q->bindValue(':name', 'test', PDO::PARAM_INT);
        $Q->bindValue(':description', 'test', PDO::PARAM_INT);
        $Q->bindValue(':big_image', 'test', PDO::PARAM_INT);

        try {
            $Q->execute();
        } catch (PDOException $e) {
            $rollback = true;
        }
    }

    if ($rollback) {
        echo 'rollbacking...';
        DBCon::getCon()->rollBack();
    } else {
        echo 'commiting...';
        DBCon::getCon()->commit();
    }

?>

Why isn't the entire transaction rolled back?

Thanks in advance.

Upvotes: 2

Views: 914

Answers (1)

Gerard Roche
Gerard Roche

Reputation: 6411

Either auto-commit is enabled, or the connection is not persisting, or you're not using innodb.

This will work, which means DBCon::getCon() is not doing what you think it's doing.

<?php
    include('model/dbcon.model.php');

    $languages = array('project_nl', 'project_en');


    $connection = DBCon::getCon();

    $connection->beginTransaction();

    $rollback = true;

    foreach($languages as $language) {
        $Q = $connection->prepare('INSERT INTO `'.$language.'`(`id`, `name`, `description`, `big_image`) VALUES (:id,:name,:description,:big_image)');
        $Q->bindValue(':id', '1', PDO::PARAM_INT);
        $Q->bindValue(':name', 'test', PDO::PARAM_INT);
        $Q->bindValue(':description', 'test', PDO::PARAM_INT);
        $Q->bindValue(':big_image', 'test', PDO::PARAM_INT);

        try {
            $Q->execute();
        } catch (PDOException $e) {
            $rollback = true;
        }
    }

    if ($rollback) {
        echo 'rollbacking...';
        $connection->rollBack();
    } else {
        echo 'commiting...';
        $connection->commit();
    }

?>

Upvotes: 3

Related Questions