A PHP Application: The Wedding Gift Registry - Learning MySQL - page 505

When developing an application, you should carefully consider whether you can design
the process to minimize the duration that locks are held, or avoid locking altogether.
This is particularly important if you're using MyISAM tables, since MyISAM locks are
table-level. If you apply a write lock to a MyISAM table, nobody else can write to that
table or read from it until you release the lock.
In the approach we've just discussed, we lock the gifts table, ensure that the specified
gift is not reserved, update the gifts table to reserve the specified gift, and then unlock
the gifts table. Thinking through the operation, we realize that we're in fact worried
about trying to reserve a gift that is reserved between the time that our application lists
it as unreserved and the time that the application tries to reserve it for a particular user.
We can design a single SQL query that will only UPDATE the gifts table if the gift is not
reserved:
$query = "UPDATE gifts SET username = '{$_SESSION['username']}' ".
"WHERE gift_id = {$gift_id} AND username IS NULL";
A gift that is reserved by another user—or by the current user—will have a username
that is not NULL . If the MySQL server finds a row with the specified gift_id and with
no username , it will update it to set the username to that of the current user.
We can check whether the gift was reserved by examining the number of rows affected
by the update. Only one row, the one for the gift with the specified gift_id , should be
modified. If we identify that the number of affected rows is not 1 , then there are two
possibilities: the gift was already reserved by another user or by the current user. We
can run an additional query to see which of these it is:
// If we found the row and updated it, create a confirmation
// message to show the user
if (mysqli_affected_rows($connection) == 1)
{
$message = "Reserved the gift for you, {$_SESSION['username']}";
}
else // Couldn't reserve the gift because it wasn't free;
{
// Check whether it's already booked by someone other
// than the current user.
$query = "SELECT * FROM gifts ".
"WHERE gift_id = {$gift_id} ".
"AND username = '{$_SESSION['username']}'";
// Run the query through the connection
if (($result = @ mysqli_query($connection, $query))==FALSE)
showerror($connection);
// Create a message to show the user
if (mysqli_num_rows($result))
$message = "The gift is already reserved for you, ".
"{$_SESSION['username']}";
else
$message = "Oh dear... someone just beat you to that gift!";
}