PHP + MySQL transactions examples

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 (\Throwable $e) {
    // An exception has been thrown
    // We must rollback the transaction
    $db->rollback();
    throw $e; // but the error must be handled anyway
}

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.

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

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

<?php

// trans.php
function begin(){
    mysql_query("BEGIN");
}

function commit(){
    mysql_query("COMMIT");
}

function rollback(){
    mysql_query("ROLLBACK");
}

mysql_connect("localhost","Dude1", "SuperSecret") or die(mysql_error());

mysql_select_db("bedrock") or die(mysql_error());

$query = "INSERT INTO employee (ssn,name,phone) values ('123-45-6789','Matt','1-800-555-1212')";

begin(); // transaction begins

$result = mysql_query($query);

if(!$result){
    rollback(); // transaction rolls back
    echo "transaction rolled back";
    exit;
}else{
    commit(); // transaction is committed
    echo "Database transaction was successful";
}

?>

As this is the first result on google for "php mysql transaction", I thought I'd add an answer that explicitly demonstrates how to do this with mysqli (as the original author wanted examples). Here's a simplified example of transactions with PHP/mysqli:

// let's pretend that a user wants to create a new "group". we will do so
// while at the same time creating a "membership" for the group which
// consists solely of the user themselves (at first). accordingly, the group
// and membership records should be created together, or not at all.
// this sounds like a job for: TRANSACTIONS! (*cue music*)

$group_name = "The Thursday Thumpers";
$member_name = "EleventyOne";
$conn = new mysqli($db_host,$db_user,$db_passwd,$db_name); // error-check this

// note: this is meant for InnoDB tables. won't work with MyISAM tables.

try {

    $conn->autocommit(FALSE); // i.e., start transaction

    // assume that the TABLE groups has an auto_increment id field
    $query = "INSERT INTO groups (name) ";
    $query .= "VALUES ('$group_name')";
    $result = $conn->query($query);
    if ( !$result ) {
        $result->free();
        throw new Exception($conn->error);
    }

    $group_id = $conn->insert_id; // last auto_inc id from *this* connection

    $query = "INSERT INTO group_membership (group_id,name) ";
    $query .= "VALUES ('$group_id','$member_name')";
    $result = $conn->query($query);
    if ( !$result ) {
        $result->free();
        throw new Exception($conn->error);
    }

    // our SQL queries have been successful. commit them
    // and go back to non-transaction mode.

    $conn->commit();
    $conn->autocommit(TRUE); // i.e., end transaction
}
catch ( Exception $e ) {

    // before rolling back the transaction, you'd want
    // to make sure that the exception was db-related
    $conn->rollback(); 
    $conn->autocommit(TRUE); // i.e., end transaction   
}

Also, keep in mind that PHP 5.5 has a new method mysqli::begin_transaction. However, this has not been documented yet by the PHP team, and I'm still stuck in PHP 5.3, so I can't comment on it.