php mysqli 自动提交和回滚的正确使用 [英] Proper usage of php mysqli autocommit and rollback

问题描述

在正确使用 mysqli 自动提交时遇到问题.以下是查询.

Having trouble with proper usage of mysqli autocommit. Below are the queries.

Table1 和 Table3 是 InnoDB 而 Table2 是 MyISAM

Table1 and Table3 are InnoDB while Table2 is MyISAM

表 2 和表 3 的值已正确插入，但表 1 的值未存储.运行代码时没有出现错误.

Values to Table2 and Table3 are inserted properly but values to Table1 are not being stored. No errors occur while running the code.

$dbconnect->autocommit(false);

$stmt = $dbconnect->prepare("INSERT INTO `table1`(`col1`,`col2`) VALUES (?,?)");
$stmt->bind_param('ss',$val1,$val2);
$stmt->execute();
$dbconnect->rollback();

$stmt = $dbconnect->prepare("INSERT INTO `table2`(`col1`,`col2`) VALUES (?,?)");
$stmt->bind_param('ss',$val3,$val4);
$stmt->execute();
$dbconnect->rollback();

$stmt = $dbconnect->prepare("INSERT INTO `table3`(`col1`,`col2`) VALUES (?,?)");
$stmt->bind_param('ss',$val5,$val6);
$stmt->execute();

$dbconnect->commit();

何时以及如何使用 autocommit(false) 和 rollback()?

When and how do you use autocommit(false) and rollback()?

推荐答案

当您有一系列 sql 语句必须一起执行以保持数据库中的一致性时，您可以使用它.将调用 commit 视为在游戏中建立保存点.任何时候调用 rollback 都会撤消上一次提交之前所做的一切.

You use it when you have a series of sql statements that must be performed together to maintain consistency in your database. Think of calling commit as establishing a save point in a game. Anytime you call rollback you undo everything that was done up to the previous commit.

想象一下，您需要在发票表中保存发票，在invoice_details 表中保存详细信息，在付款表中保存付款.为了保持一致性，您需要确保这些都完成了或没有完成.如果您在何处添加发票和详细信息，然后插入付款失败，那么您的数据库将处于不一致状态.

Imagine a situation where you need to save an invoice in your invoice table, details in your invoice_details table and payments in your payments table. To maintain consistency you need to make sure that these are all done or none of them is done. If you where to add the invoice and the details and then there was a failure on inserting the payment then your database is left in an inconsistent state.

通常这是使用像这样的 try/catch 块来完成的:

Normally this is accomplished using a try/catch block like this:

try {
    $dbconnect->autocommit(false);

    $stmt = $dbconnect->prepare("INSERT INTO `invoices`(`col1`,`col2`) VALUES (?,?)");
    $stmt->bind_param('ss',$val1,$val2);
    $stmt->execute();

    $stmt = $dbconnect->prepare("INSERT INTO `invoice_details`(`col1`,`col2`) VALUES (?,?)");
    $stmt->bind_param('ss',$val3,$val4);
    $stmt->execute();

    $stmt = $dbconnect->prepare("INSERT INTO `payments`(`col1`,`col2`) VALUES (?,?)");
    $stmt->bind_param('ss',$val5,$val6);
    $stmt->execute();

    $dbconnect->commit();
} catch(Exception $e){
    // undo everything that was done in the try block in the case of a failure.
    $dbconnect->rollback();

    // throw another exception to inform the caller that the insert group failed.
    throw new StorageException("I couldn't save the invoice");
}

这篇关于php mysqli 自动提交和回滚的正确使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！