Build Your Own DatabaseDriven Website Using PHP amp;amp; MySQL [Electronic resources] نسخه متنی

LOCKing TABLES

Notice
how, in the UPDATE query given above, and repeated here
for convenience, we use the existing value of TimesViewed and
add one to it to set the new value.

$sql = "UPDATE Jokes SET TimesViewed=TimesViewed+1
WHERE ID='$id'";

If you hadn't known that you were allowed use this short cut, you might
have performed a separate SELECT to get the current value,
added one to it, and then performed an UPDATE using that
newly calculated value. Besides the fact that this would have required two
queries instead of one, and thus would take about twice as long, there is
a danger to using this method. What if, while that new value was being calculated,
someone else viewed the same joke? The PHP script would be run a second time
for that new request. When it performed the SELECT to get
the current value of TimesViewed, it would
retrieve the same value as the first script did, because the value had not
yet been updated. Both scripts would then add one to the same value, and write
the new value into the table. See what happens? Two users view the joke, but
the TimesViewed counter increments by just
one!

In some situations, this kind of fetch-calculate-update procedure cannot
be avoided, and the possibility of interference between simultaneous requests
of the nature described above must be dealt with. Other situations where this
procedure may be necessary include cases where you need to update several
tables in response to a single action (e.g. updating inventory and shipping
tables in response to a sale on an ecommerce Website).

By locking the table or tables with which you're working
in a multiple-query operation, you can obtain exclusive access for the duration
of that operation to prevent potentially damaging interference from concurrent
operations. The syntax for locking a table is fairly simple:

LOCK TABLES tblName READ/WRITE

As shown, when you lock a table, you must specify whether you want a read
lock or a write lock. The former prevents
other processes from making changes to the table, but allows others to read
the table. The latter stops all other access to the table.

When you're done with a table you have locked, you must release the
lock to give other processes access to the table again:

UNLOCK TABLES

A LOCK TABLES query implicitly releases whatever
locks you may already have. Therefore, to safely perform a multi-table operation,
you must lock all the tables you'll use with a single query. Here's what the
PHP code might look like for the ecommerce application we mentioned above:

mysql_query("LOCK TABLES inventory WRITE, shipping WRITE");
// Perform the operation...
mysql_query("UNLOCK TABLES");

For simple databases that require the occasional multi-table operation,
table locking, as described here, will do the trick. More demanding applications,
however, can benefit from the increased performance and crash-proof nature
of transactions.

Many high-end database
servers (e.g. Oracle, MS SQL Server, etc.) support a feature called transactions,
which lets you perform complex, multi-query operations in a single, uninterrupted
step. Consider what would happen if your server were struck by a power failure
halfway through a database update in response to a client order. For example,
the server might have crashed after it updated your shipping table, but before
it updated your inventory table, in response to a customer's order.

Transactions allow a group of table updates such as this to be defined
so that they all occur, or none of them will. You can also manually cancel
a transaction halfway through if the logic of your application requires it.

There are currently two versions of MySQL available: MySQL and MySQL-Max.
The MySQL-Max version includes built-in support for InnoDB tables, which support transactions.
The standard version of MySQL does not include this support by default, but
if you compile it yourself you have the option of enabling it.

A full discussion of transactions is outside the scope of this book;
please refer to the MySQL Reference Manual for a full description of MySQL-Max, InnoDB tables, and transaction
support.