在同一个连接当中,执行了一次lock table之后,如果再次执行lock table,那么之前被锁住的表就自动解锁了。
下面这张图可以证明:
-----------------------------------------------------------------
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
-------------------------------------------------
在mysql中,如果使用了lock tables tb_name [,tb_name] read|write ,那么在unlock tables之前,都只能是用lock住的这几个表了。使用其他表的时候会提示没有被lock。
unlock tables可以用在这里解锁被lock住的表,也可以用在解锁flush tables with read lock;这个命令中来解锁。
-------------------------------------------------
在获得 锁的过程中,lock table 命令可能会锁定比你指定的更多的表。这是因为,如果你的表中有trigger,那么为了功能能正常进行,在trigger中涉及的表也会被lock。参考这里
-------------------------------------------------------------------------------------------------
获得锁的过程
LOCK TABLES acquires locks as follows:
Sort all tables to be locked in an internally defined order. From the user standpoint, this order is undefined.
If a table is to be locked with a read and a write lock, put the write lock request before the read lock request.
Lock one table at a time until the session gets all locks.
This policy ensures that table locking is