Name

LOCK TABLES

Synopsis

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

Use this statement to lock given tables for exclusive use of the current connection thread. A READ lock will allow the locked tables to be read, but will not allow writes to them even by the thread which locked them. A READ LOCAL lock will allow 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 will not allow other threads to read or write to tables locked, but will permit 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 flag 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. So, all tables to be used must be locked. To illustrate this, assume a new programmer has been hired. The programmer’s information will need to be added to the programmers table. The wk_schedule table that contains the records for scheduling work will also need 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 ...

Get MySQL in a Nutshell now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.