a WRITE  lock, subsequent READ  lock requests wait until the session that requested the WRITE  lock has obtained the lock and released it. (An exception to this policy can occur for small values of the max_write_lock_count  system variable; see Section 8.11.4, “Metadata Locking” .) 

If the LOCK TABLES  statement must wait due to locks held by other sessions on any of the tables, it blocks until all locks can be acquired. 

A session that requires locks must acquire all the locks that it needs in a single LOCK TABLES  statement. While the locks thus obtained are held, the session can access only the locked tables. For example, in the following sequence of statements, an error occurs for the attempt to access t2  because it was not locked in the LOCK TABLES  statement: 

mysql> LOCK TABLES t1 READ; mysql> SELECT COUNT(*) FROM t1; +----------+ | COUNT(*) | +----------+ | 3 | +----------+ mysql> SELECT COUNT(*) FROM t2; ERROR 1100 (HY000): Table ’t2’ was not locked with LOCK TABLES 

Tables in the INFORMATION_SCHEMA  database are an exception. They can be accessed without being locked explicitly even while a session holds table locks obtained with LOCK TABLES . 

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: 

## Table Lock Release 

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; 

When the table locks held by a session are released, they are all released at the same time. A session can release its locks explicitly, or locks may be released implicitly under certain conditions. 

•A session can release its locks explicitly with UNLOCK TABLES . 

•If a session issues a LOCK TABLES  statement to acquire a lock while already holding locks, its existing locks are released implicitly before the new locks are granted. 

