LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE

UNLOCK TABLES

MySQL enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

LOCK TABLES acquires table locks for the current thread. As of MySQL 4.0.2, to use LOCK TABLES you must have the LOCK TABLES privilege, and the SELECT privilege for each table to be locked. In MySQL 3.23, you must have SELECT, INSERT, DELETE, and UPDATE privileges for the tables.

MySQL enables client sessions to acquire table locks explicitly Locks may be used to emulate transactions or to get more speed when updating tables. This is explained in more detail later in this section.

UNLOCK TABLES explicitly releases any table locks held by the current thread. Another use for UNLOCK TABLES is to release the global read lock acquired with FLUSH TABLES WITH READ LOCK. (You can lock all tables in all databases with a read lock with the FLUSH TABLES WITH READ LOCK statement. See Section 12.5.5.2, “FLUSH Syntax”. This is a very convenient way to get backups if you have a filesystem such as Veritas that can take snapshots in time.)

The following discussion applies only to non-TEMPORARY tables. LOCK TABLES is allowed (but ignored) for a TEMPORARY table. The table can be accessed freely by the session within which it was created, regardless of what other locking may be in effect. No lock is necessary because no other session can see the table.

The following general rules apply to acquisition and release of locks by a given thread:

A table lock protects only against inappropriate reads or writes by other clients. The client holding the lock, even a read lock, can perform table-level operations such as DROP TABLE. Truncate operations are not transaction-safe, so an error occurs if the client attempts one during an active transaction or while holding a table lock.

When you use LOCK TABLES, you must lock all tables that you are going to use in your statements. While the locks obtained with a LOCK TABLES statement are in effect, you cannot access any tables that were not locked by the statement. If you lock a view, LOCK TABLES adds all base tables used in the view to the set of tables to be locked and locks them automatically.

You cannot refer to a locked table multiple times in a single query using the same name. Use aliases instead, and obtain a separate lock for the table and each alias:

mysql> LOCK TABLE t WRITE, t AS t1 READ;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;

The error occurs for the first INSERT because there are two references to the same name for a locked table. The second INSERT succeeds because the references to the table use different names.

If your statements refer to a table by means of an alias, you must lock the table using that same alias. It does not work to lock the table without specifying the alias:

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

Conversely, if you lock a table using an alias, you must refer to it in your statements using that alias:

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;

If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, only the thread holding the lock can write to the table (that thread can also read from the table); other threads are blocked from reading or writing the table until the lock has been released.

The difference between READ and READ LOCAL is that READ LOCAL allows non-conflicting INSERT statements (concurrent inserts) to execute while the lock is held. However, READ LOCAL cannot be used if you are going to manipulate the database using processes external to the server while you hold the lock. For InnoDB tables, READ LOCAL is the same as READ as of MySQL 4.1.15. (Before that, READ LOCAL essentially does nothing: It does not lock the table at all, so for InnoDB tables, the use of READ LOCAL is deprecated because a plain consistent-read SELECT does the same thing, and no locks are needed.)

WRITE locks normally have higher priority than READ locks to ensure that updates are processed as soon as possible. This means that if one thread obtains a READ lock and then another thread requests a WRITE lock, subsequent READ lock requests wait until the thread that requested the WRITE lock has obtained the lock and released it. A request for a LOW_PRIORITY WRITE lock, by contrast, allows subsequent READ lock requests by other threads to be satisfied first if they occur while the LOW_PRIORITY WRITE request is waiting. You should use LOW_PRIORITY WRITE locks only if you are sure that eventually there will be a time when no threads have a READ lock. For InnoDB tables in transactional mode (autocommit = 0), a waiting LOW_PRIORITY WRITE lock acts like a regular WRITE lock and causes subsequent READ lock requests to wait.

LOCK TABLES works as follows:

This policy ensures that table locking is deadlock free. There are, however, other things you need to be aware of about this policy: If you are using a LOW_PRIORITY WRITE lock for a table, it means only that MySQL waits for this particular lock until there are no other threads that want a READ lock. When the thread has gotten the WRITE lock and is waiting to get the lock for the next table in the lock table list, all other threads wait for the WRITE lock to be released. If this becomes a serious problem with your application, you should consider converting some of your tables to transaction-safe tables.

LOCK TABLES and UNLOCK TABLES interact with the use of transactions as follows:

You can safely use KILL to terminate a thread that is waiting for a table lock. See Section 12.5.5.3, “KILL Syntax”.

You should not lock any tables that you are using with INSERT DELAYED because in that case the INSERT is performed by a separate thread.

Normally, you do not need to lock tables, because all single UPDATE statements are atomic; no other thread can interfere with any other currently executing SQL statement. However, there are a few cases when locking tables may provide an advantage:

You can avoid using LOCK TABLES in many cases by using relative updates (UPDATE customer SET value=value+new_value) or the LAST_INSERT_ID() function. See Section 1.8.5.3, “Transactions and Atomic Operations”.

You can also avoid locking tables in some cases by using the user-level advisory lock functions GET_LOCK() and RELEASE_LOCK(). These locks are saved in a hash table in the server and implemented with pthread_mutex_lock() and pthread_mutex_unlock() for high speed. See Section 11.10.4, “Miscellaneous Functions”.

See Section 7.3.1, “Internal Locking Methods”, for more information on locking policy.