如果使用预准备语句的多个插入之一失败，则回滚

Question

我有一个表单脚本，可以在多个表中插入数据。但如果失败了，就必须把一切都恢复过来。我一直在寻找这个问题，但我没有找到使用多个(mysqli)准备语句和回滚的问题。

$mysqli = new mysqli(/* connection details */);
$mysqli->autocommit(FALSE);

// rollback should revert here
$mysqli->begin_transaction();

if ($stmt_one = $mysqli->prepare('INSERT INTO main_table (one, two, three) VALUES (?, ?, ?)')) {
    $stmt_one->bind_param('sss', $one, $two, $three);
    $stmt_one->execute();

    $id = (int) $mysqli->insert_id;

    if ($stmt_two = $mysqli->prepare('INSERT INTO sub_table_one (id, four) VALUES (?, ?)')) {
        $stmt_two->bind_param('is', $id, $four);
        $stmt_two->execute();
    }

    if ($stmt_three = $mysqli->prepare('INSERT INTO sub_table_two (id, five) VALUES (?, ?)')) {
        $stmt_three->bind_param('is', $id, $five);
        $stmt_three->execute();
    }
}

if ($mysqli->commit()) {
    // everything ok
    header('Location: /');
} else {
    // something went wrong, we have to rollback
    $mysqli->rollback();
    // and display the error message
    echo $stmt_one->error;
    echo $stmt_two->error;
    echo $stmt_three->error;
}

我不确定，我能做这样的事吗？或者我必须检查每个$stmt_*的错误吗？

​

更新

​

(问题是我使用的是DB引擎MyISAM而不是InnoDB)

我已经升级到PHP 7，现在回滚不起作用了。我试过以下几种方法：

if (!$mysqli->rollback()) $log .= 'no rollback :(';

但我在日志里没看到那条信息..。

我的当前代码如下：

public function Upload() {

    try {

        // rollback should revert here
        $mysqli->begin_transaction();

        // multiple prepared statements

        if ($mysqli->commit()) {
            $exit = $log;
        } else {
            throw new Exception('Transaction commit failed. Property ID: ' . $this->id);
        }

    } catch (Exception $e) {

        try {

            $test = $this->owner['id'] ? 'property' : ($this->applicant ? 'demand' : 'Fatal Error: PropertyFromInput() contact error (no owner, no applicant)');

            $log = 'Rolling back new ' . $test . ' upload' . "\n";

            if (!$mysqli->rollback()) $log .= 'no rollback...' . "\n";

            if ($test == 'property') $log .= $this->cleanup_prop() ? 'property successfully cleaned up' . "\n" : 'error while cleaning up property' . "\n";

            $err_msg = $e->getMessage();

        } catch (Exception $f) {

            $err_msg .= $f->getMessage();

        }

        $usr_msg = $upload_err ? $err_msg : 'Se ha producido un error. Por favor contacte con un administrador.';

        $log .= 'User triggered an error while uploading a new ' . $test . ".\n" . 'Error message: ' . $err_msg;

        $exit = array($log, $usr_msg);

    }

    $mysqli->autocommit(TRUE);

    return $exit;
}

我的日志看起来是：

Logging new property upload...

Rolling back new property upload
property successfully cleaned up
User triggered an error while uploading a new property.
Error message: Upload Error: $thumbnailsPath is false and/or not a dir: ". Property ID: 200

这意味着rollback()被执行/没有返回false，但表无论如何都是插入的.

现在谈谈5.4以来的新的php特性：

• 5.5添加了finally块，我是否只使用它设置自动提交为true？
• 我读了php 7，改进了错误处理，但我真的不知道如何利用这一点。

我需要解决这个问题，因为我不希望我的产品数据库结尾满是破损/未使用的数据，谁会呢？

Answer 1

正如Xorifelse所提到的，即使您不能使用try和catch，也可以使用finally。我从未使用过mysqli，但我假设它类似于PDO，您可以这样做：

$redirect = false;

try {
    $mysqli = new mysqli(...);
    $mysqli->autocommit(FALSE);

    // rollback should revert here
    $mysqli->begin_transaction();

    if ($stmt_one = $mysqli->prepare('INSERT INTO main_table (one, two, three) VALUES (?, ?, ?)')) {
        $stmt_one->bind_param('sss', $one, $two, $three);
        if (!$stmt_one->execute()) {
            throw new Exception($stmt_one->error);
        }

        $id = (int) $mysqli->insert_id;

        if ($stmt_two = $mysqli->prepare('INSERT INTO sub_table_one (id, four) VALUES (?, ?)')) {
            $stmt_two->bind_param('is', $id, $four);
            if (!$stmt_two->execute()) {
                throw new Exception($stmt_two->error);
            }
        }

        if ($stmt_three = $mysqli->prepare('INSERT INTO sub_table_two (id, five) VALUES (?, ?)')) {
            $stmt_three->bind_param('is', $id, $five);
            if (!$stmt_three->execute()) {
                throw new Exception($stmt_three->error);
            }
        }
    }

    if ($mysqli->commit()) {
        $redirect = true;
    } else {
        throw new Exception('Transaction commit failed.');
    }

} catch (Exception $e) {
    try {
        // something went wrong, we have to rollback
        $mysqli->rollback();
        // and display the error message
        echo $e->getMessage();
    } catch (Exception $f) {
        // and display the error message
        echo $f->getMessage();
    }
}

// don't forget to do this also
$mysqli->autocommit(TRUE);

if ($redirect) {
    header('Location: /');
}

关于最后一行，

根据http://www.php.net/manual/en/mysqli.commit.php#89976的说法，调用$mysqli->commit()不会自动将autocommit()设置为'true‘。这意味着当脚本退出时，在$mysqli->commit()之后的任何查询都将被回滚，如果autocommit()不会切换回TRUE。