PHP + MySQL transactions examples

Question

I really haven't found normal example of PHP file where MySQL transactions are being used. Can you show me simple example of that?

And one more question. I've already done a lot of programming and didn't use transactions. Can I put a PHP function or something in header.php that if one mysql_query fails, then the others fail too?

---

I think I have figured it out, is it right?:

mysql_query("SET AUTOCOMMIT=0");
mysql_query("START TRANSACTION");

$a1 = mysql_query("INSERT INTO rarara (l_id) VALUES('1')");
$a2 = mysql_query("INSERT INTO rarara (l_id) VALUES('2')");

if ($a1 and $a2) {
    mysql_query("COMMIT");
} else {        
    mysql_query("ROLLBACK");
}

Answer 1

The idea I generally use when working with transactions looks like this (semi-pseudo-code):

try {
    // First of all, let's begin a transaction
    $db->beginTransaction();

    // A set of queries; if one fails, an exception should be thrown
    $db->query('first query');
    $db->query('second query');
    $db->query('third query');

    // If we arrive here, it means that no exception was thrown
    // i.e. no query has failed, and we can commit the transaction
    $db->commit();
} catch (Exception $e) {
    // An exception has been thrown
    // We must rollback the transaction
    $db->rollback();
}

Note that, with this idea, if a query fails, an Exception must be thrown:

• PDO can do that, depending on how you configure it
  • See PDO::setAttribute
  • and PDO::ATTR_ERRMODE and PDO::ERRMODE_EXCEPTION
• else, with some other API, you might have to test the result of the function used to execute a query, and throw an exception yourself.

Unfortunately, there is no magic involved. You cannot just put an instruction somewhere and have transactions done automatically: you still have to specific which group of queries must be executed in a transaction.

For example, quite often you'll have a couple of queries before the transaction (before the begin) and another couple of queries after the transaction (after either commit or rollback) and you'll want those queries executed no matter what happened (or not) in the transaction.

Answer 2

Please check which storage engine you are using. If it is MyISAM, then Transaction('COMMIT','ROLLBACK') will not be supported because only the InnoDB storage engine, not MyISAM, supports transactions.

Answer 3

One more procedural style example with mysqli_multi_query, assumes $query is filled with semicolon-separated statements.

mysqli_begin_transaction ($link);

for (mysqli_multi_query ($link, $query);
    mysqli_more_results ($link);
    mysqli_next_result ($link) );

! mysqli_errno ($link) ?
    mysqli_commit ($link) : mysqli_rollback ($link);