Web Development

Implement MySQL-based transactions with a new set of PHP extensions

This brief tutorial will show you how to use a PHP 5.x MySQL extension called, MySQL Improved, to implement MySQL-based transactions with PHP.

Transactional support has long been on the wish list of most MySQL developers and, with the release of MySQL 4.0, this wish was finally granted. Not long after MySQL 4.0, PHP 5.x was released with a new MySQL extension, MySQL Improved, which allowed PHP developers to access these new transactional capabilities using native PHP functions. This brief tutorial will show you how to use these MySQLi functions to implement MySQL-based transactions with PHP.

Overview


In case you don't already know, a transaction is simply a block of SQL statements to be executed in all-or-nothing mode, usually because they are inter-dependent on each other. A transaction is said to be successful only if all its constituent statements are executed successfully; a failure in any one should cause the system to be "rolled back" to its pre-existing state to avoid data linkage/corruption problems.

A good example of this is a money transfer between two bank accounts. At the database level, such a transfer involves two steps: first, subtracting the transfer sum from balance of the source account and then adding it to the balance of the target account. If an error occurs in the second step, then the first step must be reversed to avoid a mismatch (and mobs of angry customers). A transaction-safe system would automatically perform this reversal to an earlier system "snapshot".

Most databases (including MySQL) accomplish this through a mixture of commands:

  • The START TRANSACTION command marks the beginning of a new transaction block. It's usually followed by a series of SQL commands.
  • The COMMIT command marks the end of a transaction block and signals that all changes made during the transaction should be "committed" or made permanent.
  • The ROLLBACK command marks the end of a transaction block and signals that all changes made during the transaction should be reversed.

Transactional functions in PHP


PHP's MySQLi extension introduces new functions to help developers leverage MySQL's transactional capabilities. Essentially, these functions are equivalent to calling the SQL START TRANSACTION, COMMIT and ROLLBACK commands. Listing A shows you an example.

Listing A


<?php
// connect to database
$dbh = mysqli_connect($host, $user, $pass, $db);

// turn off auto-commit
mysqli_autocommit($dbh, FALSE);

// run query 1
$result = mysqli_query($dbh, $query1);
if ($result !== TRUE) {
    mysqli_rollback($dbh);  // if error, roll back transaction
}

// run query 2
$result = mysqli_query($dbh, $query2);
if ($result !== TRUE) {
    mysqli_rollback($dbh);  // if error, roll back transaction
}
   
// and so on...

// assuming no errors, commit transaction
mysqli_commit($dbh);

// close connection
mysqli_close($dbh);
?>

There are three basic steps to implementing a transaction in PHP:

  1. The first step is always to turn off database "auto-commit", which essentially means that the system saves your changes as you make them. This is important because in a transactional environment, you should only save your changes after you're sure that all the transactional "units" have been successfully completed. You can turn off database auto-commit through the mysqli_autocommit() function.
  2. Next, proceed to issue INSERT, UPDATE and/or DELETE queries in the usual way, via the mysqli_query() function. It's important to test the return value of each query to see whether or not it succeeded. In the event that any of the queries fails, the mysqli_rollback() function is used to return the system to its pre-transaction state.
  3. Assuming all the commands that make up the transaction block are successfully executed, use the mysqli_commit() function to save the changes to the database system. Note that the transaction cannot be reversed once this function is called.

Working example


To see how this might work in practice, let's go back to the bank transfer example discussed previously. Let's suppose your task is to build a simple Web application to let users transfer money between their bank accounts. Let's further assume that an individual user's accounts are stored in a MySQL database which looks like this:

mysql> SELECT * FROM accounts;

+----+------------+---------+

| id | label      | balance |

+----+------------+---------+

|  1 | Savings #1 |    1000 |

|  2 | Current #1 |    2000 |

|  3 | Current #2 |    3000 |

+----+------------+---------+

3 rows in set (0.34 sec)

Now, it's necessary to build a simple interface that allows users to enter a cash amount and transfer it from one account to another. The actual "transaction" will be performed using two UPDATE statements, one debiting the amount from the source account and the other crediting the target account. Given that all we're doing is transferring money between accounts, the total available balance across all the accounts ($6000) should remain constant at all times.

Listing B shows what the code might look like.

Listing B


<?php
// connect to database
$dbh = mysqli_connect("localhost", "user", "pass", "test") or die("Cannot connect");

// turn off auto-commit
mysqli_autocommit($dbh, FALSE);

// look for a transfer
if ($_POST['submit'] && is_numeric($_POST['amt'])) {
    // add $$ to target account
    $result = mysqli_query($dbh, "UPDATE accounts SET balance = balance + " . $_POST['amt'] . " WHERE id = " . $_POST['to']);
    if ($result !== TRUE) {
        mysqli_rollback($dbh);  // if error, roll back transaction
    }
   
    // subtract $$ from source account
    $result = mysqli_query($dbh, "UPDATE accounts SET balance = balance - " . $_POST['amt'] . " WHERE id = " . $_POST['from']);
    if ($result !== TRUE) {
        mysqli_rollback($dbh);  // if error, roll back transaction
    }

    // assuming no errors, commit transaction
    mysqli_commit($dbh);
}

// get account balances
// save in array, use to generate form
$result = mysqli_query($dbh, "SELECT * FROM accounts");
while ($row = mysqli_fetch_assoc($result)) {
    $accounts[] = $row;
}

// close connection
mysqli_close($dbh);
?>
<html>
<head></head>
<body>

<h3>TRANSFER</h3>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
Transfer $ <input type="text" name="amt" size="5"> from

<select name="from">
<?php
foreach ($accounts as $a) {
    echo "<option value=\"" . $a['id'] . "\">" . $a['label'] . "</option>";   
}
?>
</select>

to

<select name="to">
<?php
foreach ($accounts as $a) {
    echo "<option value=\"" . $a['id'] . "\">" . $a['label'] . "</option>";   
}
?>
</select>

<input type="submit" name="submit" value="Transfer">

</form>

<h3>ACCOUNT BALANCES</h3>
<table border=1>
<?php
foreach ($accounts as $a) {
    echo "<tr><td>" . $a['label'] . "</td><td>" . $a['balance'] . "</td></tr>";   
}
?>
</table>
</body>
</html>

As you can see, the script begins by connecting to the database and turning off automatic commits. It then performs a SELECT query to retrieve the current balances for all accounts and then constructs a form with a drop-down interface to select source/target accounts for the transaction. Figure A shows you what the initial form looks like.

Figure A

Accounts originally

Once the form is completed and submitted, two UPDATE queries are fired to actually perform the debit and credit. Notice how each query is accompanied with a mysqli_rollback() stub that gets activated if the query fails. Assuming neither of the queries fails, the new balances are saved to the database via a call to mysqli_commit(). The database connection is then closed.

You can try this out for yourself, by transferring $500 from Savings #1 to Current #2. Once you perform the transfer, you'll see an updated statement of balances which looks like Figure B.

Figure B

After the transaction

Tip: Of course, this was only a simple two-command transaction. Typically, you'd use this transaction model when you had many more SQL statements to be executed together, with a failure in one having a cascade effect on the others. In these situations, you might find it simpler to encapsulate the mysqli_query() and mysqli_rollback() calls in a single user-defined function that could be called as needed.


As you can see, implementing a transactional model with PHP and MySQL can make your MySQL databases more robust to query execution errors. However, before you go out and start rewriting all your code to use this model, it's worth noting that transactions do impose performance overhead on the system and so performing a cost-benefit analysis of the tradeoff before implementing this model is always a good idea.

0 comments

Editor's Picks