【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 不适合做写为主的引擎。因为写锁后,其他线程不能对其做任何操作,大量的更新会使得查询很难得到锁,从而造成永远的阻塞;