MySQL ROLLBACK实际上没有回滚(MySQL ROLLBACK not actually rolling back)

 我有以下PHP代码： 
 
$dbh->beginTransaction();
$dbh->exec("LOCK TABLES
    `reservations` WRITE, `settings` WRITE");
$dbh->exec("CREATE TEMPORARY TABLE
        temp_reservations
    SELECT * FROM reservations");
$dbh->exec("ALTER TABLE
        `temp_reservations`
    ADD INDEX ( conf_num ) ; ");

// [...Other stuff here with temp_reservations...]

$dbh->exec("DELETE QUICK FROM `reservations`");
$dbh->exec("OPTIMIZE TABLE `reservations`");
$dbh->exec("INSERT INTO `reservations` SELECT * FROM temp_reservations");

var_dump(GlobalContainer::$dbh->inTransaction()); // true
$dbh->exec("UNLOCK TABLES");
$dbh->rollBack();
 
 事务对于定期更新/插入工作正常，但上述代码由于某种原因不适用。 当上面发生错误时，我留下了一个完全空的reservations表。 我在PDO :: beginTransaction页面上读到，“ 当在事务中发出数据库定义语言（DDL）语句（如DROP TABLE或CREATE TABLE）时，某些数据库（包括MySQL）会自动发出隐式COMMIT ”。 MySQL手册有一个“数据定义语句”列表，我假设它与上面列出的DDL相同，后者列出了CREATE TABLE但我只创建了一个临时表。 有没有办法解决？ 
 
 另外，我留下一个空的reservations表这一事实是否表明在DELETE QUICK FROM reservations查询后发生了提交？ 
 
 编辑 ：另外注意， INSERT INTO reservations行也会产生以下错误： 
 
 
 
 当其他未缓冲的查询处于活动状态时，无法执行查询。 考虑使用PDOStatement :: fetchAll（）。 或者，如果您的代码只是针对mysql运行，则可以通过设置PDO :: MYSQL_ATTR_USE_BUFFERED_QUERY属性来启用查询缓冲。 
 
 
 我试过做$dbh->setAttribute( PDO::MYSQL_ATTR_USE_BUFFERED_QUERY , true); 但这似乎并没有影响它。 我假设它与交易有关，但我不确定。 任何人都可以找出导致此错误的确切原因吗？ 

I have the following PHP code:
 
$dbh->beginTransaction();
$dbh->exec("LOCK TABLES
    `reservations` WRITE, `settings` WRITE");
$dbh->exec("CREATE TEMPORARY TABLE
        temp_reservations
    SELECT * FROM reservations");
$dbh->exec("ALTER TABLE
        `temp_reservations`
    ADD INDEX ( conf_num ) ; ");

// [...Other stuff here with temp_reservations...]

$dbh->exec("DELETE QUICK FROM `reservations`");
$dbh->exec("OPTIMIZE TABLE `reservations`");
$dbh->exec("INSERT INTO `reservations` SELECT * FROM temp_reservations");

var_dump(GlobalContainer::$dbh->inTransaction()); // true
$dbh->exec("UNLOCK TABLES");
$dbh->rollBack();
 
Transactions are working fine for regular updates/inserts but the above code for some reason is not. When an error happens above, I'm left with a completely empty reservations table. I read on the PDO::beginTransaction page that "some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction". The MySQL manual has a list of "Data Definition Statements", which I would assume is the same as DDL mentioned above which lists CREATE TABLE but I am only creating a temporary table. Is there any way around this?
 
Also, does the fact that I'm left with an empty reservations table show that a commit occurred after the DELETE QUICK FROM reservations query?
 
Edit: On an additional note, the INSERT INTO reservations line also produces the following error:
 
 
 
Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. 
 
 
I tried doing $dbh->setAttribute( PDO::MYSQL_ATTR_USE_BUFFERED_QUERY , true); but this doesn't seem to affect it. I'm assuming it would have something to do with the transaction, but I'm not sure. Can anyone pinpoint what exactly is causing this error as well?

原文：https://stackoverflow.com/questions/14574037