|NO.Z.00159|——————————|BigDataEnd|——|Java&MySQL.高级.V31|——|MySQL.v32|行锁测试|

一、行锁测试

### --- 更新时的行锁测试：数据准备

~~~     # 创建表
CREATE TABLE innodb_lock(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    age INT,
    INDEX idx_name(NAME)
);

~~~     # 插入数据

INSERT INTO innodb_lock VALUES(NULL,'a', 13);
INSERT INTO innodb_lock VALUES(NULL,'b', 23);
INSERT INTO innodb_lock VALUES(NULL,'c', 33);
INSERT INTO innodb_lock VALUES(NULL,'d', 43);

### --- 打开两个窗口, 都开启手动提交事务 ( 提交事务或回滚事务就会释放锁 )

~~~     # 开启MySQL数据库手动提交
SET autocommit=0;

### --- 执行不同会话修改操作, 窗口1读，窗口2 写
——>        窗口1 进行, 对id为1的数据 进行更新操作,但是不进行commit.
——>        执行之后,在当前窗口查看表数据,发现被修改了.

update innodb_lock set name = 'aaa' where id=1;
select * from innodb_lock;

mysql> update innodb_lock set name = 'aaa' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from innodb_lock;
+----+------+------+
| id | NAME | age  |
+----+------+------+
|  1 | aaa  |   13 |
|  2 | b    |   23 |
|  3 | c    |   33 |
|  4 | d    |   43 |
+----+------+------+
4 rows in set (0.00 sec)

### --- 在窗口2 查看表信息, 无法看到更新的内容

mysql> select * from innodb_lock;
+----+------+------+
| id | NAME | age  |
+----+------+------+
|  1 | a    |   13 |
|  2 | b    |   23 |
|  3 | c    |   33 |
|  4 | d    |   43 |
+----+------+------+

——>        总结: 行级锁中的写锁主要是为了解决在修改数据时，不允许其他事务对当前数据进行修改和读取操作，从而可以有效避免”脏读”问题的产生。
——>        窗口1 对innodb_lock表的 id=1 的这一行,进行写操作,但是不要commit

begin;
update innodb_lock set name = 'abc' where id=1;

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update innodb_lock set name = 'abc' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

### --- 接下来 窗口2 也对innodb_lock表的 id=1 的这一行,进行写操作,发现发生了阻塞

begin;
update innodb_lock set name = 'a123' where id=1;

### --- 等窗口1执行commit语句之后，窗口2的SQL就会执行了

——>        总结: 在有写锁的情况下，其他事务不能再对当前数据添加写锁，
——>        从而保证数据的一致性,从而避免了不可重复读的问题.

二、查询时的排他锁测试

### --- select语句加排他锁方式 : select * from table_name where ... for update;

~~~     # for update 的作用
——>        for update 是在数据库中上锁用的，可以为数据库中的行上一个排他锁。
~~~     # for update 的应用场景
——>        存在高并发并且对于数据的准确性很有要求的场景，是需要了解和使用for update的。
~~~     # for update 的注意点
——>        for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。

### --- 在窗口1中, 首先开启事务, 然后对 id为1 的数据进行排他查询

begin;
select * from innodb_lock where id = 1 for update;

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb_lock where id = 1 for update;
+----+------+------+
| id | NAME | age  |
+----+------+------+
|  1 | abc  |   13 |
+----+------+------+
1 row in set (44.51 sec)

### --- 在窗口2中，对同一数据分别使用 排他查和共享锁 两种方式查询
~~~     我们看到开了排他锁查询和共享锁查询都会处于阻塞状态，
~~~     因为id=1的数据已经被加上了排他锁，此处阻塞是等待排他锁释放。

~~~     # 排他锁查询
select * from innodb_lock where id = 1 for update;
~~~     # 共享锁查询
select * from innodb_lock where id = 1 lock in share mode;

### --- 如果只是使用普通查询,我们发现是可以的

select * from innodb_lock where id = 1;
mysql> select * from innodb_lock where id = 1;
+----+------+------+
| id | NAME | age  |
+----+------+------+
|  1 | abc  |   13 |
+----+------+------+
1 row in set (0.00 sec)

三、查询时的共享锁测试

### --- 查询时的共享锁测试
——>        添加共享锁: select * from table_name where ... lock in share mode;
——>        事务获取了共享锁，在其他查询中也只能加共享锁,但是不能加排它锁。

### --- 窗口1 开启事务, 使用共享锁查询 id = 2 的数据 ,但是不要提交事务

begin;
select * from innodb_lock where id = 2 lock in share mode;

mysql> 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb_lock where id = 2 lock in share mode;
+----+------+------+
| id | NAME | age  |
+----+------+------+
|  2 | b    |   23 |
+----+------+------+

### --- 窗口2 开启事务, 使用普通查询和共享锁查询 id = 2 的数据 ,是可以的

select * from innodb_lock where id = 2 lock in share mode;
select * from innodb_lock where id = 2;

mysql> select * from innodb_lock where id = 2 lock in share mode;
+----+------+------+
| id | NAME | age  |
+----+------+------+
|  2 | b    |   23 |
+----+------+------+

mysql> select * from innodb_lock where id = 2;
+----+------+------+
| id | NAME | age  |
+----+------+------+
|  2 | b    |   23 |
+----+------+------+

### --- 加排他锁就查不到，因为排他锁与共享锁不能存在同一数据上。

select * from innodb_lock where id = 2 for update;

---

 

 

 

 

 

 

 

 

 

---

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

---