做豆制品的网站,集团网站建设策划方案,业之峰,php装修公司网站源码【0】README#xff1a; 不论是加读锁还是写锁#xff0c;必须要等到其他数据库连接关闭后才可以进行#xff1b;
【1】添加表级读锁
1.1#xff09;多会话执行的时序操作分析#xff1a; 1.2#xff09;session1执行的具体时序操作#xff1b;
mysql select * …【0】README 不论是加读锁还是写锁必须要等到其他数据库连接关闭后才可以进行
【1】添加表级读锁
1.1多会话执行的时序操作分析 1.2session1执行的具体时序操作
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 isbn1003 where id2;
ERROR 1054 (42S22): Unknown column id in where clause
mysql update book_tbl set isbn1003 where rcrd_id2;
ERROR 1099 (HY000): Table book_tbl was locked with a READ lock and cant be updated
mysql
mysql
mysql update book_tbl set isbn1003 where rcrd_id2;
ERROR 1099 (HY000): Table book_tbl was locked with a READ lock and cant 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.3session2执行的具体时序操作
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 isbn1003 where rcrd_id2;
^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 isbn1003 where rcrd_id2;
Query OK, 1 row affected (7.68 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql 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 产生表级锁定的次数表示可以立即获取锁的查询次数每获取锁则值加1table_locks_waited出现表级锁定争用而发生等待的次数不能立即获取等待锁的次数而是每等待一次则加1此值高则说明存在较严重的表级锁争用问题
【补充】
表锁1 myisam存储引擎 的读写锁调度是写优先 这也是 myisam 不适合做写为主的引擎。因为写锁后其他线程不能对其做任何操作大量的更新会使得查询很难得到锁从而造成永远的阻塞
