MySQL Transactions in PHP

Error handling transactions in PHP

Transactions are very crucial for maintaining the integrity of our data. Web applications often ignore transactional integrity which can be very dangerous.

A common problem is when we need to perform two or more inserts in one operation. If we don't use transactions, then only some of them will be inserted which will bring a complete chaos in our database.



Say for example that we need to update an employee record and after that insert a record of the update to an employee_history table. If for some reason the insert to table employee fails, the employee_history will have a record for a modification of an employee which hasn't been done, because of the failure.

Of course there many more dangerous things, just imagine bank transactions, which most of the times involve more than 1 database modification operations for each bank transaction.

Those kind of problems can only be solved by using transactions. OK, it can also be done by catching the errors and manually deleting the rows but this is a very bad practice.

In MySQL this is can only be achieved using the InnoDB storage engine.

The following example script illustrates the use of transactions in PHP/MySQL

We first create a test table which will be used in our example:

CREATE TABLE IF NOT EXISTS `trancheck` (
  `id` int(10) unsigned NOT NULL,
  `d` varchar(40) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

We insert some rows:

INSERT INTO `trancheck` (`id`, `d`) VALUES
(1, 'val1'),
(2, 'val2'),
(3, 'val3'),
(4, 'val4'),
(10, 'val0')

Sample script using the super "sexy" try-catch error handling of PHP. We intentionally try to insert a row with a unique id which already exists in order to produce a "duplicate key" error:

<?php

$link = mysqli_connect("localhost", "username", "password", "test");

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

/* disable autocommit */
mysqli_autocommit($link, FALSE);

try {
        if (!mysqli_query($link, "INSERT INTO trancheck values (5,'val1')")) {
                throw new Exception(mysqli_error($link));
        }
        if (!mysqli_query($link, "INSERT INTO trancheck values (10,'val1')")) {
                throw new Exception(mysqli_error($link));
        }
} catch (Exception $e) {
        echo 'Caught exception: ',  $e->getMessage(), "\n";
        mysqli_rollback($link);
}
mysqli_commit($link);

mysqli_close($link);

?>

The example above will insert the first line with id: 5, then will continue to the second insert which will produce the following MySQL error:

Caught exception: Duplicate entry '10' for key 'PRIMARY'

Therefore, the try-catch statements will handle this and will rollback the whole transaction and everything will be undone.