Table of Contents for

MySQL in a Nutshell, 2nd Edition

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 WRITE;

In this example, the workreq table is locked with a READ keyword so that no new work requests may be added while the table for the programmers’ work schedules is being updated, but the work requests may still be viewed by other users. The programmers table is locked for writing with the READ LOCAL keyword, because one record needs to be inserted for the new programmer’s personal information. The wk_schedule table is locked for exclusive use by the current thread.

For convenience, you can give a table an alias with the AS keyword. In the example, the wk_schedule table is referred to as work for subsequent SQL statements until the tables are unlocked. During this time, the thread can refer to the table only by this name in all other SQL statements.

You can release locks with the UNLOCK TABLES statements. A START TRANSACTION statement also unlocks tables, as does the issuing of another TABLE LOCKS statement. Therefore, all tables to be locked should be named in one statement. Additional tables can be added to the end of the TABLE LOCKS statement in a comma-separated list.

You can lock all tables with a FLUSH TABLES WITH READ LOCK statement. You can use the GET_LOCK() and RELEASE_LOCK() functions as alternatives to the LOCK TABLES and UNLOCK TABLES covered in this chapter.