CODEWITHSUNDEEP
phpsql
nickdnk
nickdnk

Reputation: 4319

SQL php Rollback does't work

I have the following in php:

try {
    // INSERT FETCHED LIST INTO ARCHIVE
    $stmt1 = $sql->prepare('INSERT INTO hsarchive (LIST) VALUES (?)');
    $stmt1->bind_param("s",$total);
    $stmt1->execute();

    $stmt2 = $sql->prepare('TRUNCATE TABLE highscore');
    $stmt2->execute();

    $sql->rollback();
    $stmt1->close();
    $stmt2->close();

    } catch (Exception $e) {

    echo "error";
    $sql->rollback();

    }

Engine is InnoDB and the connection is started like:

$sql = getSQLAccess();
$sql->autocommit(false);
$sql->begin_transaction();

with getSQLAccess returning an object of the type connection with user, pw etc. in it.

No matter how I spin this, the table is truncated and the list is inserted into the archive. I tried switching around where I close the statements, and as you can see I'm currently not even committing, as I'm trying to figure out why the rollback doesnt work.

Anyone?

EDIT: So this would be the way to go, according to best answer:

try {

// INSERT FETCHED LIST INTO ARCHIVE
    $stmt = $sql->prepare('INSERT INTO hsarchive (LIST) VALUES (?)');
    $stmt->bind_param("s",$total);
    $stmt->execute();
    $stmt->close();

    $stmt = $sql->prepare('DELETE FROM highscore');
    $stmt->execute();
    $stmt->close();

    $sql->commit();

    } catch (Exception $e) {
     $sql->rollback();
    }

Upvotes: 2

Views: 87

Answers (1)

Alma Do
Alma Do

Reputation: 37365

DDL in transactions

Since we've figured out that there are no FK constraints to table highscore - then your issue is caused because since MySQL 5.0.3, TRUNCATE table syntax is equivalent to deletion of all rows logically but not physically

If there are no foreign key constraints to this table (your case) which restricts from doing this, MySQL will produce TRUNCATE operation via fast scheme: it will do DROP table + CREATE table. So while logically it's same to deletion of all rows, it's not the same in terms of how operation is maintained.

Why this is the difference? Because MySQL doesn't support DDL in transactions. More precise, such operations can not be rolled back. For MySQL, DDL operations will cause immediate implicit commit. That is why you see that your TRUNCATE statement: first, is committed even if you don't commit; second, rollback has no effect on it.

Solution

If you still need to rollback your operation, then, unfortunately, you'll need to use DELETE syntax instead of TRUNCATE. Unfortunately - because, obviously, DELETE is much slower than TRUNCATE, because rows will be processed one by one.

Upvotes: 3

Related Questions