11.7 Locking

If you are writing software for a frequently used Web server, you'll need to take a closer look at locking. Usually locking is done by PostgreSQL internally. However, it is possible to influence locking explicitly. In many situations this is necessary, and it can help you to get around problems easily.

The most important thing when working with explicit locking is to see how concurrent PHP scripts treat locking.

In general, working with locks is easy. Everything can be done using just one command:

 phpbook=# \h LOCK Command:     LOCK Description: explicitly lock a table Syntax: LOCK [ TABLE ] name [, ...] LOCK [ TABLE ] name [, ...] IN lockmode MODE where lockmode is one of:         ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE |         SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE 

As you can see, LOCK provides many different options for defining how to lock a table. One important point is how PHP and PostgreSQL treat locks that are not released explicitly. In the next example you will see how this works:

 <?php         $dbh = pg_connect("host=localhost user=postgres dbname=phpbook");         if      (!$dbh) { echo "error while connecting.<br>\n"; }         execute("INSERT INTO plant VALUES (4, 'pines', 'green')");         execute('LOCK plant IN EXCLUSIVE MODE');         execute("INSERT INTO plant VALUES (5, 'rose', 'red')"); function execute($sql) {         global $dbh;         echo "start: ".date('Y-m-d H:i:s')." --- $sql<br>\n";         $status = pg_exec($dbh, $sql);         echo "end: ".date('Y-m-d H:i:s')." --- $sql<br>\n";         if      (!$status)         {                 echo "an error has occurred when executing ($sql)<br>\n";         } } ?> 

The function called execute combines the execution of a SQL statement with error handling. This is helpful because error handling has to be implemented only once. In addition, the starting time and the finishing time of the SQL statements is displayed. This information is necessary to see what effects locking has.

First one record is added to the table. In the next step the table is locked exclusively and an additional record is added to the table. The lock is not released, and the question now is: How will this affect the further treatment of the table?

After executing the script the first time, two additional values can be found in plant:

 phpbook=# SELECT * FROM plant;  id |        name         | color ----+---------------------+--------   1 | Sambucus nigra      | yellow   2 | Abies               | green   3 | Colchicum autumnale | pink   4 | pines               | green   5 | rose                | red (5 rows) 

The logging information displayed by the script contains nothing special:

 start: 2001-12-14 10:06:25 --- INSERT INTO plant VALUES (4, 'pines', 'green') end: 2001-12-14 10:06:25 --- INSERT INTO plant VALUES (4, 'pines', 'green') start: 2001-12-14 10:06:25 --- LOCK plant IN EXCLUSIVE MODE end: 2001-12-14 10:06:25 --- LOCK plant IN EXCLUSIVE MODE start: 2001-12-14 10:06:25 --- INSERT INTO plant VALUES (5, 'rose', 'red') end: 2001-12-14 10:06:25 --- INSERT INTO plant VALUES (5, 'rose', 'red') 

Everything has been processed in one second. Let's see what will be displayed when the script is executed a second time:

 start: 2001-12-14 10:06:55 --- INSERT INTO plant VALUES (4, 'pines', 'green') end: 2001-12-14 10:06:55 --- INSERT INTO plant VALUES (4, 'pines', 'green') start: 2001-12-14 10:06:55 --- LOCK plant IN EXCLUSIVE MODE end: 2001-12-14 10:06:55 --- LOCK plant IN EXCLUSIVE MODE start: 2001-12-14 10:06:55 --- INSERT INTO plant VALUES (5, 'rose', 'red') end: 2001-12-14 10:06:55 --- INSERT INTO plant VALUES (5, 'rose', 'red') 

The lock does not affect the scripts started after the first one has been terminated. The reason for that is simple: When the connection to PostgreSQL is closed by PHP automatically, all locks made by this connection will be released automatically. However, PHP scripts that are accessing plant between the time the first PHP script has locked the table and the script has been terminated have to wait until the lock is released. If the script takes a long time to succeed, this will decrease the performance of your system significantly.