MySQL：关于RR的一些实验及拓展

最近收到了很多大佬的讨论，我大概研究了一下。

相关概念

锁相关问题

• 脏读：一个事务可以读到另一个事务中未提交的数据
• 不可重复读：在一个事务没有结束时，另外一个事务也访问该同一数据集合，并做了 DML 操作。因此，在第一个事务两次读数据之间，读到的数据可能是不一样的
• 幻读（丢失更新）：一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据的不一致。
• 虚读：在同一个事务中，连续执行两次查询，但是两次查询之间其他事务插入了新记录，导致后一次查询看到了之前未看到的“虚拟”记录。

事务的隔离级别

• READ UNCOMMITTED
  • 允许事务查看其他事务所进行的未提交更改
  • 允许“脏”读、 不可重复读和虚读。
• READ COMMITTED
  • 允许事务查看其他事务所进行的已提交更改
  • 不允许“脏”读。 但允许不可重复读和虚读。
  • 只支持二进制格式为 ROW 情况下
• REPEATABLE READ
  • 确保每个事务的 SELECT 输出一致
  • InnoDB 的默认级别
  • InnoDB 不允许“脏”读、不可重复读和虚读
• SERIALIZABLE
  • 将一个事务的结果与其他事务完全隔离， 即一个事务所选的行不能由其他事务更改， 直到该事务完成为止。
  • 分布式事务常用。

问题描述

以下图均来自朋友圈截图，无断章取义，亦无恶意搬运，均为完整的意思表达，有疑问可以贴出来全文进行补充说明哈

实验

准备

下面我做几个实验，从各个角度来分析一下 RR 和锁

1. 首先，先按照图中示例，创建一张表，并插入数据

   create table t17 (id int auto_increment,
a varchar(10),
b varchar(10),
c varchar(10),
primary key(id)
) engine=INNODB default charset=utf8;

   insert into t17(id, a, b, c)
values
('1','1','1','1'),
('2','2','2','2'),
('4','4','4','4'),
('6','6','6','4');


2. 然后，我们再确认隔离级别

   mysql> show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)


实验一

• 事务 1：

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

  mysql> select * from t17;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 4 | 4 | 4 | 4 |
| 6 | 6 | 6 | 4 |
+----+------+------+------+
4 rows in set (0.00 sec)


• 事务 2：

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

  mysql> select * from t17;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 4 | 4 | 4 | 4 |
| 6 | 6 | 6 | 4 |
+----+------+------+------+
4 rows in set (0.00 sec)


• 事务 1：

  mysql> update t17 set c=6 where a=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

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

  mysql> select * from t17;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 4 | 4 | 4 | 4 |
| 6 | 6 | 6 | 6 |
+----+------+------+------+
4 rows in set (0.00 sec)


• 事务 2：

  mysql> select * from t17;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 4 | 4 | 4 | 4 |
| 6 | 6 | 6 | 4 |
+----+------+------+------+
4 rows in set (0.00 sec)


结论 1：事务 1 的提交并不影响事务 2 的查询结果

疑问：事务 2 对数据做修改，会不会影响事务 1 的提交呢？我们继续实验

实验二

• 事务 2：

  mysql> select * from t17;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 4 | 4 | 4 | 4 |
| 6 | 6 | 6 | 4 |
+----+------+------+------+
4 rows in set (0.00 sec)

  mysql> update t17 set a=8 where c=4;
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1 Changed: 1 Warnings: 0

  mysql> select * from t17;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 4 | 8 | 4 | 4 |
| 6 | 6 | 6 | 4 |
+----+------+------+------+
4 rows in set (0.00 sec)


结论 2：事务 2 的提交并没有影响 id=6 的数据，所以 MySQL 中，数据的查询应该是依赖 UNDO 做到的，当查询数据时，查到的内容是事务开启时的事务 ID 之前的数据，提交后，事务结束，查到的内容是事务 ID 之后的数据。而修改影响的是小于事务 id，且符合条件的数据，所以此时查到的 id=6 的数据 a 值依然是 6，而非 8。

疑问：那查询的内容是这个动作第一次执行之前的数据，还是事务开启，即 begin 之前的数据呢？

实验三

• 重新初始化数据

  mysql> truncate table t17;
Query OK, 0 rows affected (7.38 sec)

  mysql> insert into t17(id, a, b, c)
  -> values
  -> ('1','1','1','1'),
  -> ('2','2','2','2'),
  -> ('4','4','4','4'),
  -> ('6','6','6','4');
  Query OK, 4 rows affected (0.00 sec)
  Records: 4 Duplicates: 0 Warnings: 0

  mysql> create table t1(id int);
  Query OK, 0 rows affected (0.02 sec)

  mysql> insert into t1 values (1),(2);
  Query OK, 2 rows affected (0.01 sec)
  Records: 2 Duplicates: 0 Warnings: 0

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


• 事务 2：

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

  mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)


• 事务 1：

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

  mysql> update t17 set c=6 where a=6;
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1 Changed: 1 Warnings: 0

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

  mysql> select * from t17;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 4 | 4 | 4 | 4 |
| 6 | 6 | 6 | 6 |
+----+------+------+------+
4 rows in set (0.00 sec)


• 事务 2：

  mysql> select * from t17;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 4 | 4 | 4 | 4 |
| 6 | 6 | 6 | 4 |
+----+------+------+------+
4 rows in set (0.00 sec)


总结：事务中的查询，查的是第一条与表相关的语句，这个动作触发之前的数据，而相同事务中，会记录此时的事务 ID，以此来保证所不允许的不可重复读和虚读

疑问：那图二中的 for update 是否可以操作呢？

实验四

• 重新初始化数据

  mysql> truncate table t17;
Query OK, 0 rows affected (7.38 sec)

  mysql> insert into t17(id, a, b, c)
-> values
-> ('1','1','1','1'),
-> ('2','2','2','2'),
-> ('4','4','4','4'),
-> ('6','6','6','4');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0


• 事务 1：

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

  mysql> select * from t17 where a=1 for update;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 4 | 4 | 4 | 4 |
| 6 | 6 | 6 | 4 |
+----+------+------+------+
4 rows in set (0.00 sec)


• 事务 2：

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

  mysql> update t17 set c=6 where a=6;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction


结论：for update 会造成锁表，不论 RR、还是 RC 环境

结语

好啦，蹭了一波热度，也进行了一些延伸，学到更多东西才是目的