11.8 PHP and Transactions

Transactions and locks are strongly related to each other. After you have seen how locks can be treated, you can take a closer look at PHP and transactions. Usually working with transactions is an easy task. However, a closer look is necessary to find out what happens to uncommitted transactions. The next example shows what happens with transactions that are not committed manually:

 <?php         $dbh = pg_connect("host=localhost user=postgres dbname=phpbook");         if      (!$dbh) { echo "error while connecting.<br>\n"; }         execute("BEGIN;");         execute("INSERT INTO plant VALUES (6, 'cactus', 'green')"); function execute($sql) {         global $dbh;         $status = pg_exec($dbh, $sql);         if      (!$status)         {                 echo "an error has occurred when executing ($sql)<br>\n";         } } ?> 

First a transaction is started. In the next step one value is added to the table, but the transaction is not committed manually. Therefore, the new value cannot be found in the table:

 phpbook=# SELECT * FROM plant WHERE id=6;  id | name | color ----+------+------- (0 rows) 

When the script has been executed, PHP closes the connection to PostgreSQL and the database performs an implicit ROLLBACK. This way, no data from uncommitted transactions will pollute your data.

The situation differs when the transaction is ended explicitly:

 <?php         $dbh = pg_connect("host=localhost user=postgres dbname=phpbook");         if      (!$dbh) { echo "error while connecting.<br>\n"; }         execute("BEGIN;");         execute("INSERT INTO plant VALUES (6, 'cactus', 'green')");         execute("COMMIT;"); function execute($sql) {         global $dbh;         $status = pg_exec($dbh, $sql);         if      (!$status)         {                 echo "an error has occurred when executing ($sql)<br>\n";         } } ?> 

This time the record can be found in the table because the transaction has been committed correctly.

 phpbook=# SELECT * FROM plant WHERE id=6;  id |  name  | color ----+--------+-------   6 | cactus | green (1 row) 

If you are not using BEGIN and COMMIT explicitly, every command will be executed as one separate transaction. This is an important point because you don't have to deal with transactions explicitly. As you have seen in this section, PHP does not provide onboard tools for handling transactions. Everything is done by executing ordinary SQL statements.