|NO.Z.00156|——————————|BigDataEnd|——|Java&MySQL.高级.V28|——|MySQL.v29|加锁解锁|案例演示|表级锁读锁演示|

一、表级锁之读锁演示

### --- 表级锁(偏读)

——>        表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，
——>        它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。
——>        表级锁定分为: 表共享读锁（共享锁）与表独占写锁（排他锁）。
——>        特点: 开销小，加锁快；不会出现死锁；
——>        锁定粒度大，发出锁冲突的概率最高，并发度最低。

二、数据准备

### --- 创建数据库

CREATE DATABASE test_lock CHARACTER SET 'utf8';

### --- 创建表,选择 MYISAM存储引擎

CREATE TABLE mylock01(
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(20)
)ENGINE MYISAM;

### --- 创建表

CREATE TABLE mylock02(
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(20)
)ENGINE MYISAM;

### --- mylock01表中向插入数据

INSERT INTO mylock01(title) VALUES('a1');
INSERT INTO mylock01(title) VALUES('b1');
INSERT INTO mylock01(title) VALUES('c1');
INSERT INTO mylock01(title) VALUES('d1');
INSERT INTO mylock01(title) VALUES('e1');

### --- mylock02表中向插入数据

INSERT INTO mylock02(title) VALUES('a');
INSERT INTO mylock02(title) VALUES('b');
INSERT INTO mylock02(title) VALUES('c');
INSERT INTO mylock02(title) VALUES('d');
INSERT INTO mylock02(title) VALUES('e');

SELECT * FROM mylock01;

三、加锁语法

### --- 查看表中加过的锁
~~~     0表示没有加锁,当前的所有数据库表都没有加锁

SHOW OPEN TABLES;
mysql> SHOW OPEN TABLES;
+--------------------+------------------------------------------------------+--------+-------------+
| Database           | Table                                                | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| performance_schema | events_waits_summary_by_user_by_event_name           |      0 |           0 |
| performance_schema | events_waits_summary_global_by_event_name            |      0 |           0 |
| performance_schema | events_transactions_summary_global_by_event_name     |      0 |           0 |

~~~     # 查询加锁的表,条件In_use 大于0

SHOW OPEN TABLES WHERE In_use > 0;

四、手动增加表锁

~~~     # 语法格式: LOCK TABLE 表名 READ(WRITE), 表名2 READ(WRITE), 其他;
~~~     # 为mylock01加读锁(共享锁) , 给mylock02加写锁(排他锁)

lock table mylock01 read,mylock02 write;
SHOW OPEN TABLES WHERE In_use > 0;
mysql> lock table mylock01 read,mylock02 write;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW OPEN TABLES WHERE In_use > 0;
+--------------+----------+--------+-------------+
| Database     | Table    | In_use | Name_locked |
+--------------+----------+--------+-------------+
| test_explain | mylock01 |      1 |           0 |
| test_explain | mylock02 |      1 |           0 |
+--------------+----------+--------+-------------+

### --- 释放锁, 解除锁定

~~~     # 方式1
unlock tables;

~~~     # 方式2 找到锁进程,得到id

SHOW PROCESSLIST;
kill id

五、加读锁测试

### --- MySQL 的表级锁有两种模式:

——>        表共享读锁(Table Read Lock)
——>        表独占写锁(Table Write Lock)。

### --- 对mylock01表加读锁

lock table mylock01 read;

### --- 开启两个窗口,对mylock01进行读操作, 两个窗口都可以读

select * from mylock01;

### --- 在1窗口进行写操作 (update), 失败

update mylock01 set title='a123' where id = 1;
mysql> update mylock01 set title='a123' where id = 1;
ERROR 1099 (HY000): Table 'mylock01' was locked with a READ lock and can't be updated

### --- 错误提示: 表“mylock02” 未用锁表锁定

select * from mylock02;
mysql> select * from mylock02;
ERROR 1100 (HY000): Table 'mylock02' was not locked with LOCK TABLES5、在1窗口中 读取其他的表,比如读取 mylock 02表. 读取失败.
select * from mylock02;
mysql> select * from mylock02;
ERROR 1100 (HY000): Table 'mylock02' was not locked with LOCK TABLES

### --- 在2窗口中 对 mylock01表 进行写操作
~~~     执行后一直阻塞

update mylock01 set title='a123' where id = 1;

### --- 解除 mylock01 的锁定,窗口2 的修改执行.

unlock tables;

### --- 总结:

~~~     对MyISAM表的读操作 (加读锁) ,不会阻塞其他进程对同一表的读请求,
~~~     但是会阻塞对同一表的写请求. 只有当读锁释放后,才会执行其他进程的写操作.

---

 

 

 

 

 

 

 

 

 

---

Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart

                                                                                                                                                   ——W.S.Landor

---