Figure 11.2 Safer processing with locks.
In this simple example, you have used only one table. More commonly, a query will involve more than
one table, and a sequence of related queries may involve several. In either situation, you would need to
lock all of the tables you're going to use.Imagine that your code gets a little more sophisticated, and you want to add this sale to a transactions
table together with the customer's name and address.Your code might look something like the following:
LOCK TABLES gadgets WRITE, transactions WRITE, customer READ;
SELECT num_in_stock, price 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;
SELECT cust_name, address, zip, from customer WHERE cust_id=this_customer_id;
INSERT INTO transactions VALUES (cust_name, address, zip, price, qty);
UNLOCK TABLES;
Notice here that you use WRITE locks on the gadgets and transactions tables, but you used a
READ lock on the Customers table. This is because you have no need to write anything to the
Customers table. In fact, other threads will still be able to read from the Customers table, even
though you've locked it.It is important to realize that all tables used need to be locked while you execute the code. This ensures
that a deadlock situation cannot arise. Deadlock might occur if a thread locked just one of a set of tables
it needed while another thread simultaneously locked another table in that set. Each thread would
commence its processing. Suppose that thread A locks table 1, and thread B locks table 2. But if thread
B also wanted to use table 1 (which it failed to lock), it would have to stop and wait until table 1 is
released by thread A. This might never happen, because thread A may be paused, waiting for thread B
to release table 2. This is a deadlock situation and would be the equivalent of gridlock on your
database.How MySQL Uses Locks
The syntax for LOCK TABLES is as follows:
LOCK TABLES table1_name (AS alias) {READ | [LOW_PRIORITY] WRITE} [, table2_name {READ |
[LOW_PRIORITY] WRITE} ...]
As you can see, the LOCK TABLES command can be given a list of tables, some to READ lock and
some to WRITE lock.
There are some simple rules about how MySQL handles READ and WRITE locks: