正确使用php mysqli autocommit和rollback

Question

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

Table1 and Table3 are InnoDB while Table2 is MyISAM

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

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

Answer 1

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.

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.

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