MySQL in a Nutshell, 2nd Edition

Name

LOCK TABLES

Synopsis

LOCK TABLES table [AS alias] 
   {READ [LOCAL]|[[LOW_PRIORITY] WRITE]} [, ...]

Use this statement to lock the given tables for exclusive use by the current connection thread. A READ lock allows the locked tables to be read by all threads, but it does not allow writes to the tables, even by the thread that locked them. A READ LOCAL lock allows all threads to read the tables that are locked while the locking connection can execute INSERT statements. Until the lock is released, though, direct data manipulation by command-line utilities should be avoided. A WRITE lock prohibits other threads from reading from or writing to locked tables, but it permits reads and writes by the locking thread. SQL statements for tables that are locked with the WRITE option have priority over statements involving tables with a READ lock. However, the LOW_PRIORITY keyword may be given before the WRITE to instruct the server to wait until there are no queries on the tables being locked.

Only locked tables may be accessed by a locking thread. Therefore, all tables to be used must be locked. To illustrate this, assume a new programmer has been hired. The programmer’s information must be added to the programmers table. The wk_schedule table that contains the records for scheduling work also needs to be adjusted to assign work to the new programmer and away from others. Here is how you might lock the relevant tables:

LOCK TABLES workreq READ, programmers READ LOCAL, wk_schedule AS work LOW_PRIORITY ...