多个会话对表加表级读锁和表级写锁后的操作权限分析（表级读锁+表级写锁）

【0】README： 不论是加读锁还是写锁，必须要等到其他数据库连接关闭后才可以进行； 

【1】添加表级读锁

1.1）多会话执行的时序操作分析：

 

1.2）session1执行的具体时序操作；

mysql> select * from book_tbl;
+---------+------+-----------+-------+
| rcrd_id | isbn | book_name | price |
+---------+------+-----------+-------+
|       1 | 1001 | 红楼梦    |    30 |
|       2 | 1002 | 西游记    |    60 |
+---------+------+-----------+-------+
2 rows in set (0.01 sec)

mysql>
mysql> lock table book_tbl read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from book_tbl;
+---------+------+-----------+-------+
| rcrd_id | isbn | book_name | price |
+---------+------+-----------+-------+
|       1 | 1001 | 红楼梦    |    30 |
|       2 | 1002 | 西游记    |    60 |
+---------+------+-----------+-------+
2 rows in set (0.00 sec)

mysql> update book_tbl set isbn='1003' where id=2;
ERROR 1054 (42S22): Unknown column 'id' in 'where clause'
mysql> update book_tbl set isbn='1003' where rcrd_id=2;
ERROR 1099 (HY000): Table 'book_tbl' was locked with a READ lock and can't be updated
mysql>
mysql>
mysql> update book_tbl set isbn='1003' where rcrd_id=2;
ERROR 1099 (HY000): Table 'book_tbl' was locked with a READ lock and can't be updated
mysql>
mysql> select * from mylock_tbl;
ERROR 1100 (HY000): Table 'mylock_tbl' was not locked with LOCK TABLES
mysql>
mysql>
mysql>
mysql>
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

1.3）session2执行的具体时序操作；

mysql> select * from book_tbl;
+---------+------+-----------+-------+
| rcrd_id | isbn | book_name | price |
+---------+------+-----------+-------+
|       1 | 1001 | 红楼梦    |    30 |
|       2 | 1002 | 西游记    |    60 |
+---------+------+-----------+-------+
2 rows in set (0.00 sec)

mysql> update book_tbl set isbn='1003' where rcrd_id=2;
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>
mysql> select * from mylock_tbl;
+---------+------+
| rcrd_id | name |
+---------+------+
|       1 | a    |
|       2 | b    |
|       3 | c    |
|       4 | d    |
|       5 | e    |
+---------+------+
5 rows in set (0.00 sec)

mysql>
mysql> update book_tbl set isbn='1003' where rcrd_id=2;
Query OK, 1 row affected (7.68 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from book_tbl;
+---------+------+-----------+-------+
| rcrd_id | isbn | book_name | price |
+---------+------+-----------+-------+
|       1 | 1001 | 红楼梦    |    30 |
|       2 | 1003 | 西游记    |    60 |
+---------+------+-----------+-------+
2 rows in set (0.00 sec)

==================================================================

【2】添加表级写锁

（session1）

 

（session2）

【总结】 

• 总结1：读锁不会阻塞读，但阻塞写； 写锁会把读和写都阻塞；
• 总结2： 看看哪些表被加锁 了？ show open tables;

【如何分析表锁定？】
可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定； 
show status like 'table%';

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 267   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 126   |
| Table_open_cache_misses    | 29    |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+

这里有两个状态变量记录 mysql内部表级锁定的情况，两个变量 table_locks_immediate 和  table_locks_waited 说明如下：

• table_locks_immediate： 产生表级锁定的次数，表示可以立即获取锁的查询次数，每获取锁，则值加1；
• table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取等待锁的次数，而是每等待一次则加1），此值高则说明存在较严重的表级锁争用问题；

【补充】

• 表锁1： myisam存储引擎 的读写锁调度是写优先， 这也是 myisam 不适合做写为主的引擎。因为写锁后，其他线程不能对其做任何操作，大量的更新会使得查询很难得到锁，从而造成永远的阻塞；