SQL php回滚不起作用

I have the following in php:

我在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:

引擎是InnoDB,连接启动如下:

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

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

使用getSQLAccess返回与user,pw等类型连接的对象。

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();
    }

1 个解决方案

#1

DDL in transactions

DDL在交易中

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

因为我们已经发现没有FK约束来表高分 - 那么你的问题是因为自MySQL 5.0.3以来,TRUNCATE表语法相当于逻辑上删除所有行而不是物理上

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.

如果没有外键约束这个表(你的情况)限制这样做,MySQL将通过快速方案产生TRUNCATE操作:它将执行DROP表+ CREATE表。因此,从逻辑上讲,删除所有行的方法相同,但在维护操作方面却不尽相同。

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.

为什么这有区别?因为MySQL在事务中不支持DDL。更准确地说,此类操作无法回滚。对于MySQL,DDL操作将导致立即隐式提交。这就是为什么你看到你的TRUNCATE语句:首先,即使你没有提交,也会被提交;第二,回滚对它没有影响。

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.

如果仍然需要回滚操作,那么,遗憾的是,您需要使用DELETE语法而不是TRUNCATE。不幸的是 - 因为很明显,DELETE比TRUNCATE慢得多,因为行将逐个处理。

#1