正确使用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语句时，可以使用它.可以将调用提交视为在游戏中建立保存点.每当您调用回滚时，您都将撤消直到上一次提交为止的所有操作.

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屋！