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.

Subscribe to the Developer Insider Newsletter

From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays

Subscribe to the Developer Insider Newsletter

From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays