1、lock tables table1 read,table2 read,table3 read

igoodful@a8-apple-iphone-db00.wh(glc) > show tables;
+---------------+
| Tables_in_glc |
+---------------+
| mobile        |
| user          |
+---------------+
2 rows in set (0.00 sec)

Fri Dec 20 17:42:35 2019
igoodful@a8-apple-iphone-db00.wh(glc) > show open tables where in_use >=1;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| glc      | user  |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

Fri Dec 20 17:42:47 2019
igoodful@a8-apple-iphone-db00.wh(glc) > lock tables user read;
Query OK, 0 rows affected (0.00 sec)

Fri Dec 20 17:43:03 2019
igoodful@a8-apple-iphone-db00.wh(glc) > show open tables where in_use >=1;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| glc      | user  |      2 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

Fri Dec 20 17:43:08 2019
igoodful@a8-apple-iphone-db00.wh(glc) > select * from user;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
|    2 | 1    |
+------+------+
2 rows in set (0.01 sec)

Fri Dec 20 17:43:37 2019
igoodful@a8-apple-iphone-db00.wh(glc) > select * from mobile;
ERROR 1100 (HY000): Table 'mobile' was not locked with LOCK TABLES
Fri Dec 20 17:44:11 2019
igoodful@a8-apple-iphone-db00.wh(glc) >

 

 

 

当一个会话线程执行：

use glc;

lock tables user read;

那么

1）、该会话线程只能查询锁定的这几个表的数据，没有被锁定的表，不能查询到数据

2）、同时阻止其他会话对锁住的表进行事务操作语句和添加write表锁，

3）、其他会话可以对同一个表添加read锁，

4）

执行