Although threads are similar in principle to processes in UNIX systems, they are
not actually the same because the MySQL daemon is actually a single UNIX
process, but it can have many threads within it.
For example, consider a scenario in which you have a table containing stock levels. For each product,
the table contains a qty_in_stock field. If a customer orders an item, the server application would
check the quantity in stock for this item and, provided the stock level is greater than the customer's
order, you process the order. Finally, you reduce the stock level and update your table accordingly:
SELECT qty_in_stock FROM gadgets WHERE gadget_id=some_gadget_id;
.
#some code here to check qty_in_stock against customer's requirement;
#if okay...
.
UPDATE gadgets SET qty_in_stock=(qty_from_previous_statement-qty_purchased) WHERE
gadget_id=some_gadget_id;
While these lines of code are being executed, there's a slight chance that another customer is
simultaneously purchasing the same product from the store. The same piece of code would be
executed by a second thread, but the threads would confuse each other's calculations. The result would
be erroneous data being written back into the table (see Figure 11.1).Figure 11.1 Processing without locks.
Clearly, you're going to upset the second customer. You'll promise him the goods, but when you try to
fulfill, you'll find you've already emptied the store.The answer to this is to use a lock. Locking allows a thread to have exclusive access to a number of
tables, ensuring that it can go about its business without interference from other threads. When it's
finished, it releases its lock and other threads can get to the data again.Using locking, your code would look something like the following:
LOCK TABLES gadgets WRITE;
SELECT num_in_stock FROM gadgets WHERE gadget_id=some_gadget_id;
...
#some code here to check qty_in_stock against customer's requirement;
#if okay...
...
UPDATE gadgets SET num_in_stock=(num_from_previous_statement-num_purchased) WHERE
gadget_id=some_gadget_id;
UNLOCK TABLES;
Now your process is a little better managed, because the thread for the second customer has to wait
until the thread for the first customer has finished, as shown in Figure 11.2.