mysql LOCK TABLES and UNLOCK TABLES

文档很详细，一些需要注意的地方：

lock table有两种模式

lock tables table_name  read  [or write];

test1:

session 1:

lock tables tmp_xf_lock;

1. 可以查询

2. dml 报：ERROR 1099 (HY000): Table 'tmp_xf_lock' was locked with a READ lock and can't be updated

session 2:

1. 可以查询

2. dml： 等待，直到session 1  unlock tables 或者超时

 test2:

session1:

lock tables tmp_xf_lock write;

1. 可以查询

2. 可以dml ：insert into tmp_xf_lock values(8,8);

Query OK, 1 row affected (0.00 sec)

session2:

1. 不可以查询

2，不可以dml ，都是等待状态

这些文档里描写的很清楚，所以当只是想停止对表加锁，不让表表数据再发生变更，那么用 read。如果只是想让自己可以更改数据，其他用户不能查询也不能变更数据，那么用 wirte（阻塞了其他线程的读写，有点狠，都不让读了）。 用处各不相同，注意好选择。 看字面意思的 lock write可能会产生误解。

 FLUSH TABLES WITH READ LOCK; 这样可以锁住所有表

READ [LOCAL] lock:

The session that holds the lock can read the table (but not write it).

Multiple sessions can acquire a READ lock for the table at the same time.

Other sessions can read the table without explicitly acquiring a READ lock.

The LOCAL modifier enables nonconflicting INSERT statements (concurrent inserts) by other sessions to execute while the lock is held.

[LOW_PRIORITY] WRITE lock:

The session that holds the lock can read and write the table.

Only the session that holds the lock can access the table. No other session can access it until the lock is released.

Lock requests for the table by other sessions block while the WRITE lock is held.

The LOW_PRIORITY modifier affects lock scheduling if the WRITE lock request must wait, as described later.