实验一 ： 事务表 查看

  • 建表语句 ``sql CREATE TABLEt2(idINT ( 11 ) NOT NULL,cINT ( 11 ) DEFAULT NULL,dINT ( 11 ) DEFAULT NULL, PRIMARY KEY (id), KEYc(c` ) ) ENGINE = INNODB;

insert into t2 values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25);

  2. - 开启三个窗口。
  3. - 窗口一 执行：select * from t2 where d=5 lock in share mode; 共享读锁
  4. - 窗口二执行：select * from t2 where d =5 for update;  排他读锁
  5. - 窗口三执行：select * from information_schema.INNODB_TRX\G;  查看 INNODB_TRX 表碓事务信息， 发现 有事务 在等待 锁 请求 lock wait；
  7. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/21613024/1630463565717-220fb3c9-01cc-4d18-8f92-520ef6ff668f.png#clientId=uff867bbb-3849-4&from=paste&height=836&id=u3fa64529&margin=%5Bobject%20Object%5D&name=image.png&originHeight=1672&originWidth=1944&originalType=binary&ratio=1&size=1217020&status=done&style=none&taskId=u1274ac50-2770-415b-bbbe-48674f46da9&width=972) 
  9. - 可以 观察到  trx_id 为 10143 的 事务 A 正在等待 锁请求，  并且 请求的锁 id ： trx_requeste_lock_id : 一大串id 如图。  也可以看到造成所等待的查询语句，锁住的行数等等， 在事务进行回滚时，会根据 这个行数 来选择 最小行数 进行回退。
  10. <a name="y5p1g"></a>
  11. ### 实验二 ： update的当前读
  13. - 关于 可重复读 的一些实验。MySQL 默认开启事务自动提交模式，即除非显式的开启事务(BEGIN 或 START TRANSACTION)，否则每条 SOL 语句都会被当做一个单独的事务自动执行
  14. - 开启事务A ， 查询语句 执行  select * from t2 where d=5;
  16. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/21613024/1630466036183-eb63686b-7003-4ace-9ed7-52360bfae8d4.png#clientId=uff867bbb-3849-4&from=paste&height=152&id=u61446567&margin=%5Bobject%20Object%5D&name=image.png&originHeight=304&originWidth=476&originalType=binary&ratio=1&size=40079&status=done&style=none&taskId=u03ab9b20-f5dc-4c74-b148-3b676d31099&width=238)
  18. - 开启 事务B ，执行 update t2 set c = 72 where id = 21; 并提交。
  20. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/21613024/1630466367642-8ad621df-b0c4-4f07-b0cc-4d67ccc7a5e5.png#clientId=uff867bbb-3849-4&from=paste&height=86&id=u82605f04&margin=%5Bobject%20Object%5D&name=image.png&originHeight=172&originWidth=652&originalType=binary&ratio=1&size=61004&status=done&style=none&taskId=u039807f9-837e-4055-b859-4d50bd5228b&width=326)
  22. - 事务 A 执行相同的语句 ： update t2 set c = 72 where id = 21;  可以观察到  changed：0。 虽然执行成功 但是 并没有发生修改。 因为 update是当前读， 会读到最新 数据， 事务B 执行过一次 uodate， 事务再次执行相同的是update语句时，会读到最新的数据， 因此 不会发生修改。  
  24. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/21613024/1630466417749-e4761194-fabd-4478-a238-b0519a8dcc3e.png#clientId=uff867bbb-3849-4&from=paste&height=59&id=u21518ed6&margin=%5Bobject%20Object%5D&name=image.png&originHeight=118&originWidth=612&originalType=binary&ratio=1&size=35089&status=done&style=none&taskId=ue0634d04-76bd-4b53-ad9e-275e1a85ac6&width=306)
  26. - 结论：  update 是当前读， 读的是最新快照，  与 innoDB的可重读 并不冲突， 因为可重复读 是针对 普通的select 语句碓。
  27. <a name="jO0rC"></a>
  28. ###  实验三 ： 锁释放时机
  30. - 开启事务A， 执行查询语句 ：select * from t2 where d = 5;
  32. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/21613024/1630466830820-e34b220f-81b0-48ae-a31e-f9df077ba69a.png#clientId=uff867bbb-3849-4&from=paste&height=179&id=u4c6b00d1&margin=%5Bobject%20Object%5D&name=image.png&originHeight=358&originWidth=382&originalType=binary&ratio=1&size=80722&status=done&style=none&taskId=u29ccfba9-2dc6-42b0-8855-89844a43afd&width=191)
  34. - 开启事务 B **不提交** ， 执行更新语句： update t2 set c = 8000  where id = 21;  执行成功。
  35. ```sql
  36.  update t2 set c = 8000  where id = 21;
  37. Query OK, 1 row affected (0.01 sec)
  38. Rows matched: 1  Changed: 1  Warnings: 0
  • 事务A 执行相同的 更新语句： update t2 set c = 8000 where id = 21; 执行失败， 事务会被阻塞，直到超时。 ```sql update t2 set c = 8000 where id = 21; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

加锁读 也是 当前读， 去读最新的快照。 但是由于事务B没有释放锁， 导致 阻塞等待超时。

mysql> select * from t2 where id = 21 lock in share mode; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

当前读， 读的是 id = 11 的行， 没有被锁住， 因此可以读

mysql> select * from t2 where id = 11 lock in share mode; +——+———+———+ | id | c | d | +——+———+———+ | 11 | 72 | 5 | +——+———+———+ 1 row in set (0.00 sec)

当前读 id = 21 的行 被锁住 因此 等待超时。

mysql> select * from t2 where c= 712 lock in share mode; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

快照读 读的是 事务自己的历史版本。MVCC

mysql> select * from t2 where c=712; +——+———+———+ | id | c | d | +——+———+———+ | 21 | 712 | 5 | +——+———+———+ 1 row in set (0.00 sec)

这里为什么被锁住 不是清楚， 估计是 间隙锁的原因。

mysql> select * from t2 where d = 4 lock in share mode; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> 

  2. - 原因： 由于事务B 执行了 update 操作， 并且没有提交事务， 而执行 update操作 实际上 是使用了排他锁， 锁需要等到事务提交时才会被释放， 事务A执行相同的 更新语句 会被阻塞。
  3. <a name="rbEN7"></a>
  4. ###  实验四 锁算法验证
  6. - 行锁： Record Lock：  锁记录 
  7. - 间隙锁：Gap Lock： 锁范围
  8. - Next-Key Lock： 锁记录与锁范围
  9. - 锁的验证实验。
  10. <a name="sBN0K"></a>
  11. #### 对于 锁 唯一索引
  13. - 建表语句： 创建 一个 主键索引， 主键索引的性质 ： 键唯一， 这里就会涉及到 锁 降级的一些问题。
  14. - 事务A：
  15. ```sql
  16. create table t 
  17. (a int primary key);
  18. # 插入 1   2   5  三条语句。
  19. # 开启事务A
  20. mysql> begin;
  21. Query OK, 0 rows affected (0.00 sec)
  22. # 一致性锁定读  加锁读。
  23. mysql> select * from t where a =5 for update;
  24. +---+
  25. | a |
  26. +---+
  27. | 5 |
  28. +---+
  29. 1 row in set (0.00 sec)
  • 事务B： ```sql mysql> begin; Query OK, 0 rows affected (0.00 sec)

mysql> select * from t; +—-+ | a | +—-+ | 1 | | 2 | | 5 | +—-+ 3 rows in set (0.00 sec)

插入数据 插入成功。

mysql> insert into t select 4; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> 

  2. - 结论： 事务A虽然使用了 加锁读，会触发 Next-Key Lock。 即锁定范围和本身， 但是 由于 a 是主键， 因此 Next-Key Lock 会降级为 Record Lock。 只会锁定 主键本身， 提高了并发度。对于这样的 锁降级 只会对 唯一索引有效。
  3. <a name="STDnw"></a>
  4. #### 对于锁辅助索引
  6. - 建表语句
  7. ```sql
  8. create table z (a int, b int, primary key(a),key(b));
  9. Query OK, 0 rows affected (0.03 sec)
  10. mysql> insert into z select 1,1;
  11. Query OK, 1 row affected (0.00 sec)
  12. Records: 1  Duplicates: 0  Warnings: 0
  14. mysql> insert into z select 3,1;
  15. Query OK, 1 row affected (0.00 sec)
  16. Records: 1  Duplicates: 0  Warnings: 0
  18. mysql> insert into z select 5,3;
  19. Query OK, 1 row affected (0.00 sec)
  20. Records: 1  Duplicates: 0  Warnings: 0
  22. mysql> insert into z select 7,6;
  23. Query OK, 1 row affected (0.00 sec)
  24. Records: 1  Duplicates: 0  Warnings: 0
  26. mysql> insert into z select 10,8;
  27. Query OK, 1 row affected (0.01 sec)
  28. Records: 1  Duplicates: 0  Warnings: 0
  • 事务A 执行 加锁读 ```sql begin; Query OK, 0 rows affected (0.00 sec)

mysql> select * from z where b = 3 for update; +—-+———+ | a | b | +—-+———+ | 5 | 3 | +—-+———+ 1 row in set (0.00 sec)

  2. -  由于有两个索引， 因此 mysql  会对 a，b分别加锁。  由于 a是唯一索引， 因此 只会 锁住 a=5 这个记录， 而由于 b是辅助索引， 会触发相应的Next-Key Lock 和Gap LOCK。
  3. - 对于 b索引，  next-key Lock  会锁住 范围(1,3), Gap Lock 会锁住(3,6);
  4. - 因此 对于 事务B 执行如下操作：
  5. ```sql
  6. mysql> begin;
  7. Query OK, 0 rows affected (0.00 sec)
  8. # a 被事务A 锁住  因此 该条语句会被阻塞
  9. mysql> select * from z where a = 5 lock in share mode;
  10. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  11. # 插入 （6，5） 对于主键 a=6 没有问题， 但是 b = 5 被在范围（3，6）中  会被锁住
  12. mysql> insert into z select 6,5;
  13. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  14. # 不在 事务A的加锁范围内 因此 直接插入成功。
  15. mysql> insert into z select 2,0;
  16. Query OK, 1 row affected (0.01 sec)
  17. Records: 1  Duplicates: 0  Warnings: 0
  18. mysql> insert into z select 8,6;
  19. Query OK, 1 row affected (0.00 sec)
  20. Records: 1  Duplicates: 0  Warnings: 0
  21. # 事务 B 执行 更新操作，  虽然 a =3 没有被锁住，  但是 b =2 在范围(1,3) 却被next-key lock 锁住了。 因此 更新失败。
  22. mysql> update z set b = 2 where a=3;
  23. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  24. # 执行成功 b = 8 不在被 锁住的范围内。
  25. mysql> update z set b = 8  where a=3;
  26. Query OK, 1 row affected (0.00 sec)
  27. Rows matched: 1  Changed: 1  Warnings: 0
  28. # a=5 , 被锁住了， 更新失败。
  29. mysql> update z set b = 8  where a=5;

  • 注意： 唯一索引的锁降级 ，只会针对 单列的 索引降级， 如果是多列构成的唯一索引，但是只操作一行， 那么依旧会是next - key lock。

    实验五 ： 幻读问题

  • 幻读： 在同一事务下，连续执行两次同样的SQL，可能导致不用的结果， 第二次的SQL语句 可能会返回不同的行。

    提交读

  • 设置 事务的隔离级别为 提交读 进行实验。 表中只有 1 2 5 这三条数据

  • 对于事务A ：select * from t where a>2 for update; ```sql

    将隔离级别设置 为 提交读

    mysql> set session transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from t where a>2 for update; +—-+ | a | +—-+ | 5 | +—-+ 1 row in set (0.00 sec)

  2. - 对于 事务B ， 插入 数据 4  并提交
  3. ```sql
  4. mysql> begin;
  5. Query OK, 0 rows affected (0.00 sec)
  7. mysql> insert into t select 4;
  8. Query OK, 1 row affected (0.01 sec)
  9. Records: 1  Duplicates: 0  Warnings: 0
  11. mysql> commit;
  12. Query OK, 0 rows affected (0.00 sec)

  • 事务A 再次 读取。 这里 不管是 提交读 还是 可重复读 由于使用的是 for update 加锁读，都会读到最新的数据。

    1. mysql> select * from t where a>2 for update;
    2. +---+
    3. | a |
    4. +---+
    5. | 5 |
    6. +---+
    7. 1 row in set (0.00 sec)
    8. # 在 事务B 提交 事务之后， A 读取到了 新插入的数据。
    9. mysql> select * from t where a>2 for update;
    10. +---+
    11. | a |
    12. +---+
    13. | 4 |
    14. | 5 |
    15. +---+
    16. 2 rows in set (0.00 sec)

  • 问题的关键在于： 事务B 在事务A 使用 for update 读的情况下 ， 依然能够 插入数据。 是因为 对于 提交读， 使用的锁不是next-key Lock 而是 record Lock。 并没有锁住 (2,5)这个范围， 而是 只是 锁住了 记录 a =5.