MySql中Lock命令和unlock命令

在同一个连接当中，执行了一次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 deadlock free.

 -------------------------------------------------------------------------------------------

释放锁的条件：

Rules for Lock Release

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 arereleased implicitly before the new locks are granted.

• If a session begins a transaction (for example, with START TRANSACTION), an implicit UNLOCK TABLES is performed, which causes existing locks to be released.

• If the connection for a client session terminates, whether normally or abnormally, the server implicitly releases all table locks held by the session (transactional and nontransactional). If the client reconnects, the locks will no longer be in effect.

•  In addition, if the client had an active transaction, the server rolls back the transaction upon disconnect, and if reconnect occurs, the new session begins with autocommit enabled. For this reason, clients may wish to disable auto-reconnect. With auto-reconnect in effect, the client is not notified if reconnect occurs but any table locks or current transaction will have been lost. With auto-reconnect disabled, if the connection drops, an error occurs for the next statement issued. The client can detect the error and take appropriate action such as reacquiring the locks or redoing the transaction.【最好禁用客户端的“自动重连”机制，因为如果自动重连功能打开，那么断开的连接自动重连上之后是不会通知客户端的，那么此时之前得到的lock已经不存在了。从而会造成一些未知错误。】

原文地址：http://blog.csdn.net/imzoer/article/details/8286726