I’ve been using database transactions heavily in an application I’m working on and thought it might be a good topic to write about. I started searching for tutorials on the subject and found very few that are of good quality. Hopefully this will enlighten you and provide a better understanding of the subject.

Database transactions, according to Wikipedia, are defined as:

A database transaction comprises a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions.

Put simply, transactions are a series of SQL statements that run in an all-or-nothing fashion, usually because they are dependent on one another. Most transactions involve a minimum of two steps; the start of the transaction followed by a series of SQL statements and either a commit, which ends the transaction and makes these statements permanent, or a rollback which ends the transaction and reverses changes made during the transaction.

Transactions became available in MySQL with the release of version 4.0. Not long after this release came PHP 5.x which brought us the new MySQL Improved (mysqli) extension. This new extension gave developers the ability to take advantage of transaction support in MySQL using native PHP functions.

**NOTE: Your MySQL database tables need to be configured to use Oracles InnoDB storage engine.

So lets start with a simple example; a classic transfer of funds from one bank account to another. This transaction consists of a debit to one account we’ll call debitAccount and a credit to another account we’ll call creditAccount. The credit transaction to creditAccount is dependent on the funds being available in debitAccount. If either one of the transactions fail, then no transfer should take place.

Here’s a working example:

/* function to carry out account update transaction */
function updateAccount($action = DEBIT, $acctnum, $amount) {
    global $mysqli;
    $ok = 0;
    if($action) {
        /* debit funds from account if available*/
        $query = sprintf('UPDATE account SET balance = balance - ' .
                              '%1$f WHERE acctID = %2$d AND ' .
                              'balance >= %1$f',
                              $amount, $acctnum);
    } else {
        /* credit funds to account */
        $query = sprintf('UPDATE account SET balance = balance + ' .
                              '%1$f WHERE acctID = %2$d',
                              $amount, $acctnum);
    }

		if ($stmt = $mysqli->prepare($query)) {

		    /* execute statement */
		    $stmt->execute();

		    $ok = $stmt->affected_rows;

		    /* close statement */
		    $stmt->close();
		}
    /* return whether query passed or failed */
    return $ok;
}

/* define CREDIT and DEBIT constants */
define("DEBIT", 0);
define("CREDIT", 1);

/* set up variables */
$success = TRUE;
$debitAccount = 9276852;
$creditAccount = 9583214;
$host = "localhost";
$user = "someUser";
$pass = "somePass";
$database = "someDatabase";

/* create mysqli object */
$mysqli = new mysqli($host, $user, $pass, $database);

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* set autocommit to FALSE so queries are not automatically
    committed when run */
$mysqli->autocommit(FALSE);

/* attempt to debit debitAccount
    if unsuccessful set $success to FALSE */
updateAccount(DEBIT, $debitAccount, 500) ? null : $success = FALSE;

/* attempt to credit creditAccount
    if unsuccessful set $success to FALSE */
updateAccount(CREDIT, $creditAccount, 500) ? null : $success = FALSE;

/* check for failure in transaction
    rollback if transaction failed
    commit if transaction succeeded */
if($success) {
	$mysqli->commit();
	echo "Good commit!";
} else {
	$mysqli->rollback();
	echo "Bad Transaction!";
}
$mysqli->close();

In the example above, I’ve created a function (updateAccount()) that executes an UPDATE query given a transaction type (debit or credit), the account number and an amount. The function returns TRUE if the query is executed with no errors AND at least one row was acted upon. If an error occurs or no rows were acted upon the function returns FALSE.

I have a variable success initialized to TRUE that will be used to determine whether to “commit” or “rollback” the transaction. On line 56 I pass the mysqli->autocommit() function a value of FALSE which turns off the auto-committing of queries on our database. This will allow us to run our queries in groups as a single transaction. If any query in that group fails, we can undo any changes made during that transaction.

On lines 60 and 64 I call the updateAccount() function first with the debit and then the credit. If either of the function calls return a value of TRUE, I do nothing. If either of the function calls return a value of FALSE, I set the value of the success variable to FALSE signifying an error with one of the queries in our transaction.

On line 69 I test the value of the success variable. If TRUE I call mysqli->commit() which makes our queries permanent in the database and echo to the browser that our transaction succeeded. If FALSE I call mysqli->rollback() and echo to the browser that our transaction failed. The mysqli->rollback() function undoes all queries run during the current transaction and returns the database to the state it was in before we started our transaction.

I hope this tutorial has helped you. If you have any questions, comments, suggestions or corrections please don’t hesitate to leave something in the comment section.

/cheers