MySQL Transaction support for PHP

A transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back.
The MySQL database has different types of storage engines. The most common are the MyISAM and the InnoDB engines. The MyISAM is the default one. There is a trade-off between data security and database speed. The MyISAM tables are faster to process and they do not support transactions. On the other hand, the InnoDB tables are more safe against the data loss. They support transactions. They are slower to process.

<?php

mysql_connect('localhost', 'testuser', 'test623') 
    or die("cannot connect to database\n");

mysql_select_db("testdb") or die(mysql_error());

$r1 = mysql_query("UPDATE Writers SET Name = 'Leo Tolstoy' WHERE Id = 1")
    or die(mysql_error());

$r2 = mysql_query("UPDATE Writers SET Name = 'Boris Pasternak' WHERE Id = 2")
    or die(mysql_error());

$r3 = mysql_query("UPDATE Writer SET Name = 'Leonid Leonov' WHERE Id = 3")
    or die(mysql_error());

mysql_close();

?>

In this script, we try to update three rows. The storage engine of the table is MyISAM.

$r1 = mysql_query("UPDATE Writers SET Name = 'Leo Tolstoy' WHERE Id = 1")
    or die(mysql_error());

$r2 = mysql_query("UPDATE Writers SET Name = 'Boris Pasternak' WHERE Id = 2")
    or die(mysql_error());

Here we want to change names of authors for rows 1 and 2.

$r3 = mysql_query("UPDATE Writer SET Name = 'Leonid Leonov' WHERE Id = 3")
    or die(mysql_error());

There is an error in the SQL statement. There is no Writer table.

$ php update.php
Table 'testdb.Writer' doesn't exist

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Leo Tolstoy       |
|  2 | Boris Pasternak   |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
|  6 | O'Neill           |
+----+-------------------+
6 rows in set (0.00 sec)

Running the script gives an error. But as we see, the first two rows already were changed.
In the last example of this tutorial, we are going to recreate the Writers table. This time, the table will be of InnoDB type. InnoDB MySQL database tables support transactions.

DROP TABLE Writers;

CREATE TABLE IF NOT EXISTS Writers(Id INT PRIMARY KEY AUTO_INCREMENT, 
    Name VARCHAR(25)) ENGINE=INNODB;

INSERT INTO Writers(Name) VALUES('Jack London');
INSERT INTO Writers(Name) VALUES('Honore de Balzac');
INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger');
INSERT INTO Writers(Name) VALUES('Emile Zola');
INSERT INTO Writers(Name) VALUES('Truman Capote');

This is writers.sql file. It is used to recreate the Writers table.

mysql> source writers.sql
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.10 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.02 sec)

We can use the source commnad to load and execute the sql script.

<?php

mysql_connect('localhost', 'testuser', 'test623') 
    or die("cannot connect to database\n");

mysql_select_db("testdb") or die(mysql_error());

mysql_query("SET AUTOCOMMIT=0");
mysql_query("START TRANSACTION");

$r1 = mysql_query("DELETE FROM Writers WHERE Id = 3")
    or die(mysql_error());

$r2 = mysql_query("DELETE FROM Writers WHERE Id = 4")
    or die(mysql_error());

$r3 = mysql_query("DELETE FROM Writer WHERE Id = 5")
    or die(mysql_error());

if ($r1 and $r2 and $r3) {
    mysql_query("COMMIT");
} else {
    mysql_query("ROLLBACK");
}

mysql_close();

?>

Now, we are going to execute the above script. We want to delete three rows from the table. The third SQL statement has an error.

mysql_query("START TRANSACTION");

The START TRANSACTION statement starts a new transaction. All changes must be made permanent with the COMMIT statement or ignored with the ROLLBACK statement.

if ($r1 and $r2 and $r3) {
    mysql_query("COMMIT");
} else {
    mysql_query("ROLLBACK");
}

We commit the statements only if all SQL statements three returned True. Otherwise, we roll them back. In our case the $r3 variable holds False, so the statements are not made permanent and the rows are not deleted from the table.

$ php transaction.php
Table 'testdb.Writer' doesn't exist

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Jack London       |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
+----+-------------------+
5 rows in set (0.00 sec)

The error occurred before we have committed the changes to the database. The ROLLBACK statement was called and no deletions took place.