lock tables和unlock tables

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) >

igoodful@a8-apple-iphone-db00.wh(glc) > select * from user;      ################# 表明：一个会话给表添加了读锁，那么不影响其他会话线程读取该表数据
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
|    2 | 1    |
+------+------+
2 rows in set (0.00 sec)

Fri Dec 20 17:54:11 2019
igoodful@a8-apple-iphone-db00.wh(glc) > insert into user values (3,‘3‘);       ################# 表明：一个会话给表添加了读锁，其他会话线程只能对该表进行读取，而不能对该表执行执行dml和ddl语句。
^CCtrl-C -- sending "KILL QUERY 233531" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
Fri Dec 20 17:55:59 2019
igoodful@a8-apple-iphone-db00.wh(glc) > select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|          233531 |
+-----------------+
1 row in set (0.00 sec)

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

ERROR:
No query specified

Fri Dec 20 17:57:25 2019
igoodful@a8-apple-iphone-db00.wh(glc) > lock tables user read;       ################# 表明：多个会话线程可以对同一张表添加读锁。
Query OK, 0 rows affected (0.00 sec)

Fri Dec 20 17:57:57 2019
igoodful@a8-apple-iphone-db00.wh(glc) >

igoodful@a8-apple-iphone-db00.wh(glc) > show open tables;
+--------------------+-------------------+--------+-------------+
| Database           | Table             | In_use | Name_locked |
+--------------------+-------------------+--------+-------------+
| glc                | mobile            |      0 |           0 |
| mysql              | db                |      0 |           0 |
| test               | host              |      0 |           0 |
| performance_schema | session_variables |      0 |           0 |
| glc                | user              |      2 |           0 |
| test               | user              |      0 |           0 |
| mysql              | user              |      0 |           0 |
+--------------------+-------------------+--------+-------------+
7 rows in set (0.00 sec)

Fri Dec 20 18:16:10 2019
igoodful@a8-apple-iphone-db00.wh(glc) >


##################################################################
1、出现在里面的表，表示的是：这些表都是打开的，且不是临时表。
2、如果In_use字段为0，则表示该表当前是被打开的状态，但是当前没有被使用。
3、如果In_use字段为2，则表示该表当前是被打开的状态，且正在被使用次数为2。
##################################################################


igoodful@a8-apple-iphone-db00.wh(glc) > show open tables;
+--------------------+-------------------+--------+-------------+
| Database           | Table             | In_use | Name_locked |
+--------------------+-------------------+--------+-------------+
| glc                | mobile            |      0 |           0 |
| mysql              | db                |      0 |           0 |
| test               | host              |      0 |           0 |
| performance_schema | session_variables |      0 |           0 |
| glc                | user              |      2 |           0 |
| test               | user              |      0 |           0 |
| mysql              | user              |      0 |           0 |
+--------------------+-------------------+--------+-------------+
7 rows in set (0.00 sec)

Fri Dec 20 18:16:10 2019
igoodful@a8-apple-iphone-db00.wh(glc) > flush tables;
Query OK, 0 rows affected (47.94 sec)

Fri Dec 20 18:22:19 2019
igoodful@a8-apple-iphone-db00.wh(glc) > show open tables;
Empty set (0.00 sec)

Fri Dec 20 18:23:00 2019

###########################################################################
1、flush tables会将缓存的所有表全部清空，即要读取这些表的数据就必须从磁盘加载到缓存。
2、当缓冲数目已经超过了table_open_cache设置的值，mysql开始使用LRU算法释放表对象。
3、当缓冲区已满，而连接想要打开一个不在缓冲中的表时。
##########################################################