LOCK TABLES

Use this MySQL statement to lock the given tables for exclusive use by the current connection thread. There are a few different types of locks that may be used: one allow users to continue to retrieve data, but not change data; another locks all other users out, totally.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

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

Explanation

Use this MySQL statement to lock the given MySQL tables for exclusive use by the current connection thread. A READ lock allows the locked tables to be read by all threads, but does not allow writes to the tables even by the thread which 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 tables locked, but 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 MySQL tables being locked.

Examples

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 previous 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.