如何使用MySQLi事务回滚和提交？

Question

I seem to be having trouble with the rollback feature in PHP7. Here's my code, but if I supply the queries and put an error into one of them, it won't roll back. It still commits everything that had been completed.

function sql_transaction(...$queries) {
    global $db;

    $success = 1;
    $db->autocommit(FALSE);

    foreach($queries as $query) {
        $result = $db->query($query);
        echo $result;
        if(!$result) {
            $success = 0;
            break;
        }
    }

    if($success == 1) {
        $db->commit();
    }else{
        $db->rollback();
    }
    $db->autocommit(TRUE);
    return $success;
}

Answer 1

You've to start transaction before doing this.

Because You must tell to database that You're going to start transaction.

You've to put: $db->begin_transaction(); after autocommit(FALSE);

Please read documentation: mysqli::begin_transaction

P.S. Please keep in mind it cannot be done with tables that engine does not support transactions. So if after adding begin_transaction statement rollback() did not work, check Your tables engine that it's set to engine with transaction support.

To check Your tables engine call query in mysql terminal:

SHOW TABLE STATUS FROM database_name_goes_here;

You'll get list of tables in Your database with engines defined.

To get list of transaction-safe engines You can do by calling query in mysql terminal (find Transactions: YES):

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 3. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
...