CODEWITHSUNDEEP
phpsqlmysqli
Jens Stuber
Jens Stuber

Reputation: 75

Why is the rollback for my transaction not working?

I executed the following code:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = mysqli_connect($host, $user, $pw, $db);
$mysqli->begin_transaction();
try {
    $mysqli->query("Update User SET name='Ginso1' WHERE id=6");
    $mysqli->query("Update Userr SET name='Ginso2' WHERE id=6");
    $mysqli->commit();
} catch(mysqli_sql_exception $exeption) {
    $mysqli->rollback();
    echo "exception";
}

Of course, the table Userr does not exist (while User does), so the 2nd query should throw an exception. That seems to work since the echo is executed. But when I check my DB afterwards, I see that the given id's name has been changed to 'Ginso1'. Why is the rollback not working?

I just tried to this directly in phpMyAdmin and the rollback didn't work there either:

START TRANSACTION;
UPDATE User SET name='TEST' WHERE id=6;
ROLLBACK;

Upvotes: 1

Views: 514

Answers (1)

Dharman
Dharman

Reputation: 33238

Most likely your table engine is not InnoDB. As the manual says, to make the transaction work, your database table has to use a transactional engine e.g. InnoDB.

MyISAM is not transactional.

Upvotes: 1

Related Questions