Locking Tables in Web Database Applications

Example 6-9 shows a PHP function, updateDiscount( ), that requires locking to ensure that the value returned from the SELECT query can't change before the UPDATE operation. The script is designed to be run either by the winestore system administrator-it would then require a <form> for user input-or as the final module in the ordering process for users. Another example that requires locking for winestore ordering is included in Chapter 12.

The script in Example 6-9 is designed to reward loyal customers. If the customer has spent a significant amount on an order at the winestore, a percentage discount is applied to her order. The function updateDiscount( ) forms the body of the script. It takes as parameters a cust_id, an order_id for that customer, a discount to apply to that order, and a threshold total. If the total amount spent by the user exceeds the threshold total, the discount is applied to the order.

Example 6-9. The updateDiscount function in which locking is required
  function updateDiscount($custId, $orderId,
                          $discount, $minimum,
     $ok = false;
     // Lock all tables required in this transaction
     $query = "LOCK TABLES items READ,
               orders WRITE, customer READ";
     if (!mysql_query($query, $connection))
        showerror(  );
     // Run query to find out how much a user
     // has spent in this purchase
     $query = "SELECT SUM(price*qty)
               FROM items, orders, customer
               WHERE customer.cust_id =
               AND orders.order_id = items.order_id
               AND items.cust_id = orders.cust_id
               AND orders.order_id = $orderId
               AND customer.cust_id = $custId";
     if (!($result = mysql_query($query, $connection)))
        showerror(  );
     // Get the $row with the total spent
     $row = mysql_fetch_array($result);
     // Is the amount spent more than the threshold?
     if ($row["SUM(price*qty)"] > $minimum)
        // Yes, so give the customer a discount
        // for this order
        $query = "UPDATE orders
                  SET discount = $discount
                  WHERE cust_id = $custId
                  AND order_id = $orderId";
        if (!mysql_query($query, $connection))
            showerror(  );
        $ok = true;
     // Unlock the tables
     $query = "UNLOCK TABLES";
     if (!mysql_query($query, $connection))
        showerror(  );
     // Return whether the discount was given or not
     return $ok;

The locking of items, orders, and customer is performed before the query, and the UNLOCK TABLES statement is issued after the database update of the discount. As discussed in the last section, all tables and aliases that are used must be locked for either READ or WRITE. MySQL reports an error if, for example, items is accessed but not locked while orders and customer were locked. If an unlocked table needs to be accessed-or locking must be avoided for a particular table-a second DBMS connection can be opened and used.

by BrainBellupdated