mysql autocommit undo_正确使用php mysqli autocommit和rollback

当您拥有一系列必须一起执行的sql语句以保持数据库的一致性时,可以使用它.考虑将提交称为在游戏中建立保存点.无论何时调用回滚,都可以撤消在上一次提交之前完成的所有操作.

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

通常这是使用try / catch块完成的,如下所示：

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");

}