How to make MySQL's start transaction query fail?

Question

I can see in the PHP documentation that begin_transaction can fail. How can I make it fail? (All of my tables use the InnoDB engine. MySQL version 5.7.38-log.)

Why do I care? I'm investigating a programming error in a system that does not check for the failure of starting a transaction. After adding some logging, I discovered that the system thinks that many inserts took place --- I have primary-key ids, for example --- but these records never make it to the database, so the system's deductions do not proceed. Since the system doesn't check for failures in a start transaction statement, this could explain the logging-evidence that I have.

More context. Although the system doesn't check for a possible failure of a start-transaction statement, it does check for the result of a commit. I'm interested in discovering what happens when a start-transaction statement does not succeed, but (using the same connection) I proceed with an insert and a commit. (Should this insert and commit succeed at all? Since I don't know how to make a start-transaction statement fail, I cannot try and look at the result.)

This is a PHP 7.4 web system --- with Apache as the frontend.

I tried the Apache ab program in the hope that overwhelming my web system with requests, I could get a start-transaction statement to fail. I expected them to fail. They did not fail.

(*) The log

Here's the log that proves that a series of SQL INSERT statements have been run, but did not make it to the database. (I assume nobody is deleting things on my back --- I am the sole owner of the database.) The code (where these logs are made) is below.

1675716327901,"{""message"":""New Order for user ID: 21473"",""AWN"":""some-aws-tag"",""log_level"":6}"
1675716327942,"{""message"":""Order created: 4328077174"",""AWN"":""some-aws-tag"",""log_level"":6}"
1675716327971,"{""message"":""Attendee created: 3156"",""AWN"":""some-aws-tag"",""log_level"":6}"
1675716327988,"{""message"":""Invoice created: 336845"",""AWN"":""some-aws-tag"",""log_level"":6}"
1675716331883,"{""message"":""Committed fine"",""AWN"":""some-aws-tag"",""log_level"":6}"

(*) The code

public function create() {
  $this->sendCloudWatchLog("New Order for user ID: $this->userId");
  $oSql->start_transaction();
  foreach ($this->orders as $aCourseOrder) {
    $oRegistration = new ProgramRegistration($aCourseOrder->iProgramID, $aCourseOrder->iCourseID);
    $iRegID = $oRegistration->createRegistration('unpaid', $iRegistrarUserId);
    if ($iRegID > 0) {
      $this->sendCloudWatchLog("Order created: {$oRegistration->registrationNumber}");
    } else {
      $error = 'Registration order creation failed';
      break;
    }
  
    foreach ($this->attendes as $aAttendeeInfo) {
      $newAttendeeId = $oRegistration->createAttendee($aAttendeeData);
      if ($newAttendeeId > 0) {
        $this->sendCloudWatchLog("Attendee created: {$newAttendeeId}");
      } else {
        $error = "Attendee creation failed for user id: $this->userId";
        break;
      }
    }
  }
  
  if ($error != '') {
    $oSql->rollback_transaction();
    $this->sendCloudWatchLog("Order error");
    throw new \Exception("Order Error" . $error);
  }
  
  try {
    $this->sendCloudWatchLog("Invoice created: {$this->getInvoiceId()}");
    $statementID = $this->createInvoiceStatement();
    if (intval($statementID) <= 0) {
      throw new \Exception('Invoice Error - Statement failed to be created');
    }
    /* credit card charge, possible exception thrown */
    $this->processPayment();
  } catch (\Exception $e) {
    $error = handleError($e->getMessage());
  }
  
  if ($error == '') {
    $bCommit = $oSql->commit_transaction();
    if ($bCommit === false) {
      $error = 'Commit transaction failed.';
    } else {
      $this->sendCloudWatchLog("Committed fine");
    }
  }
  
  if ($error !== '') {
    $oSql->rollback_transaction();
    $this->sendCloudWatchLog("Rollback due to non-empty: {$error}");
  }
  return true;
}```

Answer 1

mysqli_begin_transaction() is just a wrapper around mysqli_query('START TRANSACTION'), so it can fail for the same reasons that mysqli_query() would fail. But in general, it should never fail. Potential failures are limited to some generic errors such as dropped connections.

If beginning the transaction fails for any reason, then PHP will throw an exception just like it would with any other mysqli error (unless, for some reason, you have mysqli error reporting set to silent). So you should not worry about inconsistent transaction as long as you have error reporting enabled and you do not catch the error from mysqli_begin_transaction(). If the error is ignored but the error is not because of broken connection, then the following queries will execute as if the transaction was never opened. This could lead to inconsistent results in the database (one query fails and the other succeeds). This is why it's important always to have error reporting fully enabled!

The correct way would be to have mysqli_begin_transaction() outside of your try-catch.

$mysqli->begin_transaction();
try {
    // All your prepared statements go here
    $mysqli->commit();
} catch (mysqli_sql_exception $exception) {
    $mysqli->rollback();
    throw $exception;
}

However, having it inside of the try-catch would make no difference. When it fails, it would just unnecessarily call rollback(), which would rollback nothing.

Just make sure you do not use if statements and mysqli_error() for error checking. This is extremely bug-prone!