Mysql Transactions with PHP

One feature of Mysql that is often overlooked is transactions. Transactions allow you to preform multiple queries and rollback the changes if any one fails.

In mysql, only InnoDB tables have transaction support. MyISAM, which is often the default table type, does not support this.

Let's say you are transferring funds between 2 accounts. You withdraw the money in one query and deposit the money in another one. What happens if the deposit query fails? The money just disappears. Transactions are an easy way to solve this problem.

We must add some helper functions to our mysql config file. First we'll make a wrapper function for mysql_query(). It takes a query and an optional error message.

function query_db($query,$error="Error")
{
  //perform query
  $result = mysql_query($query);
  if(!$result) //query fails
  {
    //exit with error message
    exit($error."<br>$query<br> ".mysql_error());
  }
  else //query succeeds
    return $result;
}

Next, are three transaction functions, begin_transaction(), commit_transaction(), and rollback_transaction().

//Begins the transaction
//Every query after this can be rolled back
function begin_transaction()
{
  query_db("SET AUTOCOMMIT=0");
  query_db("BEGIN");

  //Set the $begin_transaction variable to true 
  //so that other functions know a transaction started
  global $begin_transaction;
  $begin_transaction = true;
}

//Commits all queries.  Changes cannot be undone after this
function commit_transaction()
{
  //if transaction is started, commit changes
  global $begin_transaction;
  if($begin_transaction)
    query_db("COMMIT");

  //update global variable
  $begin_transaction = false;
}

//Rolls back all queries. All changes are canceled.
function rollback_transaction()
{
  //if transaction is started, roll back changes
  global $begin_transaction;
  if($begin_transaction)
    query_db("ROLLBACK");

  //update global variable
  $begin_transaction = false;
}

We need to alter the query_db() function to rollback changes if an error occurs.

function query_db($query,$error="Error")
{
  //get global variable
  global $begin_transaction;

  //perform query
  $result = mysql_query($query);
  if(!$result) //query fails 
  {
    //if transaction is started, rollback changes
    if($begin_transaction)
      rollback_transaction();

    //exit with error message
    exit($error."<br>$query<br> ".mysql_error());
  }
  else //query succeeds
    return $result;
}

That's all you need to use transactions. Here's an example of how to use this.

<?php
begin_transaction();
  query_db("UPDATE accounts 
    SET balance = balance-500
    WHERE id=1"
  );
  query_db("UPDATE accounts 
    SET balance = balance+500    
    WHERE id=2"
  );
commit_transaction();
?>

If either query fails, both accounts are unaffected. If both queries succeed, the accounts are updated.

Sometimes, you want to make sure a row is affected, not just that the query succeeded. I didn't build this into the functions, but it's easy to implement. Here's a simple way to do it:

<?php
begin_transaction();
  query_db("UPDATE accounts 
    SET balance = balance-500
    WHERE id=1"
  );
  if(!mysql_affected_rows()) //no rows affected
    exit("error".rollback_transaction());

  query_db("UPDATE accounts 
    SET balance = balance+500    
    WHERE id=2"
  );
  if(!mysql_affected_rows()) //no rows affected
    exit("error".rollback_transaction());

commit_transaction();
?>

Transactions also help with testing an application. You can perform 20 queries, do some tests, and then roll back the changes automatically. Think of how much time this could save.